You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Michael Smith <ri...@gmail.com> on 2006/07/31 18:01:21 UTC
Oracle CONNECT BY parameter issue (bug?)
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
RE: Oracle CONNECT BY parameter issue (bug?)
Posted by Chris Lamey <cl...@localmatters.com>.
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:rileympk@gmail.com]
Sent: Mon 7/31/2006 10:26 AM
To: user-java@ibatis.apache.org; lmeadors@apache.org
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 <lm...@apache.org> 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 <ri...@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
> >
>
Re: Oracle CONNECT BY parameter issue (bug?)
Posted by Michael Smith <ri...@gmail.com>.
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 <lm...@apache.org> 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 <ri...@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
> >
>
Re: Oracle CONNECT BY parameter issue (bug?)
Posted by Larry Meadors <lm...@apache.org>.
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 <ri...@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
>