Hard-coding the values in iBATIS, or doing the query through a
different client, brings the expected results. The only parameter that
doesn't work is the final #groupId#. For example, if #groupId# = 1500
and #ceId# = 1000, and I put the following query into iBATIS:
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 = 1500
It works. But using #groupId# in place of that final 1500 causes the
incorrect result. So as you can see, the first two instances of
#groupId# are correctly assigned the value of 1500. It is only the
#groupId# inside the CONNECT BY statement that doesn't get the value
(I don't know what value it's getting instead... how would I check
what query is actually being sent by iBATIS?)
On 7/31/06, Larry Meadors <[EMAIL PROTECTED]> wrote:
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 <[EMAIL PROTECTED]> 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
>
