ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lucy.fos...@uk.abnamro.com
Subject RE: iBatis gives no error but no results!
Date Wed, 14 Nov 2007 14:56:03 GMT
Hi Niels,

Thanks for the suggestion but that is exactly what I did - not too clear
I'm afraid in the last part of my email:

Executing Statement:       select T1.M_ORIG_REF as
contractId, T2.M_TRN_TYPE as contractType,   T1.M_VERSION as
contractVersion  from CONTRACT_DBF T1, TRN_HDR_DBF T2     where
T2.M_TRN_FMLY = 'CURR' and T2.M_TRN_GRP = 'FXD' and T2.M_TRN_TYPE =
'FXD'
and T1.M_ORIG_REF between 12377813 and 12378073    and T1.M_REFERENCE =
T2.M_CONTRACT  and T1.M_VERSION = 1        and T2.M_TRN_STATUS = 'LIVE'

This worked giving me back the rows I expected - this was to test that the
driver accepted the between statement.  When I went back to using the
params it did not work.

Regards,
Lucy



                                                                           
             "Niels Beekman"                                               
             <n.beekman@wis.nl                                             
             >                                                          To 
                                       <user-java@ibatis.apache.org>       
             14/11/2007 14:52                                           cc 
                                                                           
                                                                   Subject 
             Please respond to         RE: iBatis gives no error but no    
             user-java@ibatis.         results!                            
                apache.org                                                 
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




Try replacing parameters with hardcoded values for the individual
pieces, maybe BETWEEN does not like to be used in combination with
parameters. Which dbms are you using?

Niels

-----Original Message-----
From: lucy.foster@uk.abnamro.com [mailto:lucy.foster@uk.abnamro.com]
Sent: woensdag 14 november 2007 15:38
To: user-java@ibatis.apache.org
Subject: iBatis gives no error but no results!


I hope someone can help as I will be bald shortly from tearing my hair
out!

I am using the following select:

  <sql id="selectFragment">
      select T1.M_ORIG_REF as contractId, T2.M_TRN_TYPE as contractType,
      T1.M_VERSION as contractVersion
      from CONTRACT_DBF T1, TRN_HDR_DBF T2
  </sql>

  <sql id="selectVersion">
      and T1.M_REFERENCE = T2.M_CONTRACT
      and T1.M_VERSION = #version#
  </sql>

  <select id="selectByTypeAndContract" parameterClass="java.util.Map"
resultClass="Contract">
      <include refid="selectFragment"/>
      where T2.M_TRN_FMLY = #family# and T2.M_TRN_GRP = #group# and
T2.M_TRN_TYPE = #type#
      and T1.M_ORIG_REF between #firstContractId# and #secondContractId#
      <include refid="selectVersion"/>
      <include refid="selectStatus"/>
  </select>

And I set the params thus:

params.put(new String("family"), new String("CURR"));
params.put(new String("group"), new String("FXD"));
params.put(new String("type"), new String("FXD"));
params.put(new String("firstContractId"), new Long(argv[2]));
params.put(new String("secondContractId"), new Long(argv[3]));
params.put(new String("version"), new Integer(argv[4]));

When I run this substituting the values in my code I get the following
result:

14 Nov 2007 13:25:08,908 sql.PreparedStatement (Log4jImpl.java:26)     -
{pstm-100001} Executing Statement:       select T1.M_ORIG_REF as
contractId, T2.M_TRN_TYPE as contractType,   T1.M_VERSION as
contractVersion  from CONTRACT_DBF T1, TRN_HDR_DBF T2     where
T2.M_TRN_FMLY = ? and T2.M_TRN_GRP = ? and T2.M_TRN_TYPE = ?  and
T1.M_ORIG_REF between ? and ?        and T1.M_REFERENCE = T2.M_CONTRACT
and T1.M_VERSION = ?           and T2.M_TRN_STATUS = 'LIVE'
14 Nov 2007 13:25:08,916 sql.PreparedStatement (Log4jImpl.java:26)     -
{pstm-100001} Parameters: [CURR, FXD, FXD, 12377813, 12378073, 1]
14 Nov 2007 13:25:08,917 sql.PreparedStatement (Log4jImpl.java:26)     -
{pstm-100001} Types: [java.lang.String, java.lang.String,
java.lang.String,
java.lang.Long, java.lang.Long, java.lang.Integer]
14 Nov 2007 13:25:08,929 sql.ResultSet     (Log4jImpl.java:26)     -
{rset-100002} ResultSet
14 Nov 2007 13:25:09,021 jdbc.SimpleDataSource (Log4jImpl.java:26)     -
Returned connection 18471958 to pool.
14 Nov 2007 13:25:09,024 extraction.ContractExtraction
(ContractExtraction.java:54)     - No. of results : 0

However if I substitutue the above values (CURR, FXD, FXD, 12377813,
12378073, 1) in the select statement ie. I replace #family# with 'CURR'
etc. I get the nine rows back that I was expecting:

14 Nov 2007 12:24:57,932 sql.PreparedStatement (Log4jImpl.java:26)     -
{pstm-100001} Executing Statement:       select T1.M_ORIG_REF as
contractId, T2.M_TRN_TYPE as contractType,   T1.M_VERSION as
contractVersion  from CONTRACT_DBF T1, TRN_HDR_DBF T2     where
T2.M_TRN_FMLY = 'CURR' and T2.M_TRN_GRP = 'FXD' and T2.M_TRN_TYPE =
'FXD'
and T1.M_ORIG_REF between 12377813 and 12378073    and T1.M_REFERENCE =
T2.M_CONTRACT  and T1.M_VERSION = 1        and T2.M_TRN_STATUS = 'LIVE'
14 Nov 2007 12:24:57,938 sql.PreparedStatement (Log4jImpl.java:26)     -
{pstm-100001} Parameters: []
14 Nov 2007 12:24:57,939 sql.PreparedStatement (Log4jImpl.java:26)     -
{pstm-100001} Types: []
14 Nov 2007 12:24:57,958 sql.ResultSet     (Log4jImpl.java:26)     -
{rset-100002} ResultSet
14 Nov 2007 12:24:58,098 sql.ResultSet     (Log4jImpl.java:26)     -
{rset-100002} Header: [CONTRACTID, CONTRACTTYPE, CONTRACTVERSION]
14 Nov 2007 12:24:58,100 sql.ResultSet     (Log4jImpl.java:26)     -
{rset-100002} Result: [12377966, FXD  , 1] ...

Any ideas would be greatly appreciated.  Thanks in advance.

Regards,
Lucy


------------------------------------------------------------------------
---
This message (including any attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender
by
return e-mail and delete this message from your system. Any unauthorised
use or dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change. ABN AMRO
Bank N.V, which has its seat at Amsterdam, the Netherlands, and is
registered in the Commercial Register under number 33002587, including
its
group companies, shall not be liable for the improper or incomplete
transmission of the information contained in this communication nor for
any
delay in its receipt or damage to your system. ABN AMRO Bank N.V. (or
its
group companies) does not guarantee that the integrity of this
communication has been maintained nor that this communication is free of
viruses, interceptions or interference.
------------------------------------------------------------------------
---




---------------------------------------------------------------------------
This message (including any attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender by
return e-mail and delete this message from your system. Any unauthorised
use or dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change. ABN AMRO
Bank N.V, which has its seat at Amsterdam, the Netherlands, and is
registered in the Commercial Register under number 33002587, including its
group companies, shall not be liable for the improper or incomplete
transmission of the information contained in this communication nor for any
delay in its receipt or damage to your system. ABN AMRO Bank N.V. (or its
group companies) does not guarantee that the integrity of this
communication has been maintained nor that this communication is free of
viruses, interceptions or interference.
---------------------------------------------------------------------------


Mime
View raw message