ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Smith" <riley...@gmail.com>
Subject Oracle CONNECT BY parameter issue (bug?)
Date Mon, 31 Jul 2006 16:01:21 GMT
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