RE: Oracle CONNECT BY parameter issue (bug?)

[Chris Lamey]

Title: RE: Oracle CONNECT BY parameter issue (bug?)

Hello,

To see the SQL iBATIS issues, you can set the "java.sql" category in a log4j config to DEBUG.  For a log4j.properties with FILE appender, it'd look like this:

    log4j.category.java.sql=DEBUG, FILE

In a JBoss log4j.xml:

    <category name="java.sql">
       <priority value="DEBUG"/>
    </category>

Cheers,
Chris

-----Original Message-----
From: Michael Smith [mailto:[EMAIL PROTECTED]]
Sent: Mon 7/31/2006 10:26 AM
To: user-java@ibatis.apache.org; [EMAIL PROTECTED]
Subject: Re: Oracle CONNECT BY parameter issue (bug?)

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
> >
>