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 MY...@jri-america.com on 2008/06/18 01:13:56 UTC
Subtle bug in Abator and iBatis
Dear sir(s):
Currently I am working with iBatis with Oracle 10g as the backend. I
have
a number of jsps or Web pages which take various input parameters in the
format of
#xxxxxxxx# where xxxxxxxx represents the dataitem from the Javascript. In
my
sqlmap, there is a complex join involving nested SELECT statements. The
nesting works fine as one of them is used for grouping a result set of no
more than
500 rows at a time. This works fine.
The main problem with the sqlmap is that part of the SELECT statment
involves
a composite join between three Oracle tables/views. By using the backend
utility
TOAD, I can see all the individual columns and data, so there is no issue
with the
underlying data.
The problem I am facing is a subtle one. Throughout my
application, there
are various queries involving wildcard searches with the input parameters.
When
there is a query involving a single table, the wildcard parameter like qu*
will
return the result set with the values quit, queer, question quintessence,
etc.
When I use the wildcard with the multiple joins and there are logical
asisgnments
to the joined tbales, the query returns back an empty set. What gives ??
Here is the code snippet.
<SQLstatement>
SELECT * FROM ( SELECT /*+ FIRST_ROWS(500) */
B.*, ROWNUM RNUM
FROM(
SELECT
TC.TXN_CODE,
TC.DESCRIPTION TC_DESCR,
TC.CR_DB,
TM.TXN_TYPE_NAME TXN_TYPE,
TT.DESCRIPTION TT_DESCR,
TC.REVERSAL,
TC.SIGN,
TC.CHANNEL
FROM
LKP_TXN_CODES_VW TC,
LKP_TXN_TYPES_VW TT,
MAP_AML_TXN_CODES TM
WHERE
TC.TXN_CODE =
TM.POSTED_TXN_CODE (+)
AND TT.TXN_TYPE =
TM.TXN_TYPE_NAME
<dynamic>
<isNotNull
property="TXN_CODE">
<isNotNull
property="CONDITION_TXN_CODE_LIKE">
AND TC.TXN_CODE
LIKE #TXN_CODE#
</isNotNull>
<isNotNull
property="CONDITION_TXN_CODE_EQUAL">
AND TC.TXN_CODE
= #TXN_CODE#
</isNotNull>
</isNotNull>
<isNotNull
property="TC_DESCR">
<isNotNull
property="CONDITION_TC_DESCR_LIKE">
AND
TT.DESCRIPTION LIKE #TC_DESCR#
</isNotNull>
<isNotNull
property="CONDITION_TC_DESCR_EQUAL">
AND
TT.DESCRIPTION = #TC_DESCR#
</isNotNull>
</isNotNull>
<isNotNull property="CR_DB"
>
AND TC.CR_DB
= #CR_DB#
</isNotNull>
<isNotNull
property="TXN_TYPE">
AND TT.TXN_TYPE
= #TXN_TYPE#
</isNotNull>
</dynamic>
ORDER BY TXN_CODE
) B
WHERE ROWNUM &lt; #ROW_TO_END# )
WHERE RNUM &gt; #ROW_TO_START#
</SQLstatement>
I look forward to any input from the iBatis user community.
regards,
Mason Yu Jr.
*******************************************************************
This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity
to whom they are addressed. If you have received this
email in error please notify the sender by replying to this
email and then delete it from your system.
No reliance may be placed upon this email without written
confirmation of its contents and any liability arising from
such reliance without written confirmation is hereby
excluded.
JRI America
*******************************************************************
Re: Subtle bug in Abator and iBatis
Posted by Nicholoz Koka Kiknadze <ki...@gmail.com>.
> wildcard parameter like qu*
Should not it be Like qu% ?
Turn on logging for connection and iBatis and check what SQL is actually
executed and whether it returns anything when executed from TOAD.
On Wed, Jun 18, 2008 at 3:13 AM, <MY...@jri-america.com> wrote:
>
> Dear sir(s):
>
> Currently I am working with iBatis with Oracle 10g as the backend. I
> have
> a number of jsps or Web pages which take various input parameters in the
> format of
> #xxxxxxxx# where xxxxxxxx represents the dataitem from the Javascript. In
> my
> sqlmap, there is a complex join involving nested SELECT statements. The
> nesting works fine as one of them is used for grouping a result set of no
> more than
> 500 rows at a time. This works fine.
> The main problem with the sqlmap is that part of the SELECT statment
> involves
> a composite join between three Oracle tables/views. By using the backend
> utility
> TOAD, I can see all the individual columns and data, so there is no issue
> with the
> underlying data.
> The problem I am facing is a subtle one. Throughout my
> application, there
> are various queries involving wildcard searches with the input parameters.
> When
> there is a query involving a single table, the wildcard parameter like qu*
> will
> return the result set with the values quit, queer, question quintessence,
> etc.
> When I use the wildcard with the multiple joins and there are logical
> asisgnments
> to the joined tbales, the query returns back an empty set. What gives ??
>
> Here is the code snippet.
>
>
>
> <SQLstatement>
>
> SELECT * FROM ( SELECT /*+ FIRST_ROWS(500) */
> B.*, ROWNUM RNUM
> FROM(
> SELECT
> TC.TXN_CODE,
> TC.DESCRIPTION TC_DESCR,
> TC.CR_DB,
> TM.TXN_TYPE_NAME TXN_TYPE,
> TT.DESCRIPTION TT_DESCR,
> TC.REVERSAL,
> TC.SIGN,
> TC.CHANNEL
>
> FROM
> LKP_TXN_CODES_VW TC,
> LKP_TXN_TYPES_VW TT,
> MAP_AML_TXN_CODES TM
> WHERE
> TC.TXN_CODE =
> TM.POSTED_TXN_CODE (+)
> AND TT.TXN_TYPE =
> TM.TXN_TYPE_NAME
>
> <dynamic>
>
> <isNotNull
> property="TXN_CODE">
> <isNotNull
> property="CONDITION_TXN_CODE_LIKE">
>
> AND TC.TXN_CODE
> LIKE #TXN_CODE#
> </isNotNull>
>
> <isNotNull
> property="CONDITION_TXN_CODE_EQUAL">
>
> AND TC.TXN_CODE
> = #TXN_CODE#
> </isNotNull>
> </isNotNull>
>
>
>
> <isNotNull
> property="TC_DESCR">
>
> <isNotNull
> property="CONDITION_TC_DESCR_LIKE">
>
> AND
> TT.DESCRIPTION LIKE #TC_DESCR#
> </isNotNull>
>
> <isNotNull
> property="CONDITION_TC_DESCR_EQUAL">
>
> AND
> TT.DESCRIPTION = #TC_DESCR#
> </isNotNull>
> </isNotNull>
>
> <isNotNull property="CR_DB"
> >
> AND TC.CR_DB
> = #CR_DB#
> </isNotNull>
>
> <isNotNull
> property="TXN_TYPE">
> AND TT.TXN_TYPE
> = #TXN_TYPE#
> </isNotNull>
>
>
> </dynamic>
>
>
> ORDER BY TXN_CODE
> ) B
>
> WHERE ROWNUM &lt; #ROW_TO_END# )
> WHERE RNUM &gt; #ROW_TO_START#
>
> </SQLstatement>
>
> I look forward to any input from the iBatis user community.
>
> regards,
>
> Mason Yu Jr.
>
>
>
> *******************************************************************
>
> This email and any files transmitted with it are confidential
> and intended solely for the use of the individual or entity
> to whom they are addressed. If you have received this
> email in error please notify the sender by replying to this
> email and then delete it from your system.
>
> No reliance may be placed upon this email without written
> confirmation of its contents and any liability arising from
> such reliance without written confirmation is hereby
> excluded.
>
> JRI America
>
> *******************************************************************
>
>