ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Oracle CONNECT BY parameter issue (bug?)
Date Mon, 31 Jul 2006 16:17:49 GMT
So, what happens if you plug in the parameters and run the query with
sql*plus or SQL Developer?

I do not see anything in that query that looks too far out.

Larry


On 7/31/06, Michael Smith <rileympk@gmail.com> wrote:
> Hello folks. I'm having an issue with getting parameters to work in an
> Oracle CONNECT BY call, and I was wondering if this is a bug (and if
> so, if anyone has come across simple work-arounds) or if my code is
> just incorrect.
>
> My Java data access object uses this class/function (I'm using the
> Spring Framework):
>
> public class SqlMapOrgDAO extends SqlMapClientDaoSupport implements OrgDAO {
>
> public List getSiteLevelsByGroupId(int ceId, int groupId) throws
> DataAccessException {
>    Map params = new HashMap();
>    params.put("ceId", ceId);
>    params.put("groupId", groupId);
>    List siteLevelList =
> getSqlMapClientTemplate().queryForList("getSiteLevelsByGroupId",
> params);
> }
> .....
>
> My iBATIS map:
> <select id="getSiteLevelsByGroupId" parameterClass="map" resultMap="siteLevel">
>         SELECT DISTINCT c.ce_depth_id, c.ce_depth_desc
>             FROM   table_1 a,
>                    table_2 c
>             WHERE  a.absolute_depth = c.absolute_depth
>             AND    a.group_id       = c.group_id
>             AND    a.absolute_depth >= 1
>             AND    a.group_id = #groupId#
>             START WITH a.ce_id = #ceId#
>             AND        a.group_id = #groupId#
>             CONNECT BY a.parent_ce_id = PRIOR a.ce_id
>             AND        a.group_id = #groupId#
> </select>
>
> Only the last #groupId# causes the problem - it returns an empty list.
> If I replace the last #groupId# with a hard-coded value that matches
> the other #groupId# values, the query works and I get a list with the
> correct rows. So basically, the value of groupId is not being passed
> correctly to the last #groupId# reference, and it is suspect because
> it is in a CONNECT BY statement, a situation that has resulted in the
> same problem for my development team before.
>
> I would greatly appreciate your assistance if you know anything about
> this issue!
>
> Thanks,
> Mike
>

Mime
View raw message