ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From M..@jri-america.com
Subject Subtle bug in Abator and iBatis
Date Tue, 17 Jun 2008 23:13:56 GMT

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

                                      &lt;dynamic&gt;

                                           &lt;isNotNull
property="TXN_CODE"&gt;
                                                 &lt;isNotNull
property="CONDITION_TXN_CODE_LIKE"&gt;

                                                            AND TC.TXN_CODE
LIKE #TXN_CODE#
                                                       &lt;/isNotNull&gt;

                                                 &lt;isNotNull
property="CONDITION_TXN_CODE_EQUAL"&gt;

                                                            AND TC.TXN_CODE
= #TXN_CODE#
                                                       &lt;/isNotNull&gt;
                                                 &lt;/isNotNull&gt;



                                           &lt;isNotNull
property="TC_DESCR"&gt;

                                                 &lt;isNotNull
property="CONDITION_TC_DESCR_LIKE"&gt;

                                                            AND
TT.DESCRIPTION     LIKE #TC_DESCR#
                                                       &lt;/isNotNull&gt;

                                                 &lt;isNotNull
property="CONDITION_TC_DESCR_EQUAL"&gt;

                                                            AND
TT.DESCRIPTION     = #TC_DESCR#
                                                       &lt;/isNotNull&gt;
                                                 &lt;/isNotNull&gt;

                                           &lt;isNotNull property="CR_DB"
&gt;
                                                            AND TC.CR_DB
= #CR_DB#
                                                 &lt;/isNotNull&gt;

                                           &lt;isNotNull
property="TXN_TYPE"&gt;
                                                            AND TT.TXN_TYPE
= #TXN_TYPE#
                                                 &lt;/isNotNull&gt;


                                          &lt;/dynamic&gt;


                                          ORDER BY TXN_CODE
                        ) B

                      WHERE ROWNUM &amp;lt; #ROW_TO_END# )
                          WHERE RNUM &amp;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

*******************************************************************


Mime
View raw message