Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 81718 invoked from network); 23 May 2006 17:14:16 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 23 May 2006 17:14:16 -0000 Received: (qmail 30426 invoked by uid 500); 23 May 2006 17:14:13 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 30402 invoked by uid 500); 23 May 2006 17:14:13 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 30391 invoked by uid 99); 23 May 2006 17:14:13 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 23 May 2006 10:14:13 -0700 X-ASF-Spam-Status: No, hits=1.6 required=10.0 tests=HTML_MESSAGE,HTML_TAG_EXIST_TBODY,HTML_TINY_FONT,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of jeffgbutler@gmail.com designates 66.249.82.202 as permitted sender) Received: from [66.249.82.202] (HELO wx-out-0102.google.com) (66.249.82.202) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 23 May 2006 10:14:11 -0700 Received: by wx-out-0102.google.com with SMTP id t13so654446wxc for ; Tue, 23 May 2006 10:13:51 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=DUwJ/d8n1QWUYkIQ//TFhxQPP37xXM3cuOguIk2uAbGC5b2WOijio/+52OBY0rKmBzDsviVAqLpfFX6+K4XO4zhB1sT4F7pYNMZC7V2uz5PPefxGfH6gdRaHP2EJGPcrVvH1s7Z/MqOqhnS5kv308lyPSBaHZEBodYb6Rm+z9fM= Received: by 10.70.31.19 with SMTP id e19mr6632973wxe; Tue, 23 May 2006 10:13:51 -0700 (PDT) Received: by 10.70.72.14 with HTTP; Tue, 23 May 2006 10:13:51 -0700 (PDT) Message-ID: Date: Tue, 23 May 2006 12:13:51 -0500 From: "Jeff Butler" To: user-java@ibatis.apache.org Subject: Re: Stored procedure:Temporary select/update/insert sqls before actual select query In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_122753_27853140.1148404431119" References: X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_122753_27853140.1148404431119 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline iBATIS does not read your SP source code so it has no expectations. It wil= l respond to the first results set that is returned and ignore all others. Since you are only returning one result set, there must be some other problem. Sending your SqlMap.xml file would probably be useful for diagnosis. Jeff Butler On 5/23/06, yogisha.b@tcs.com wrote: > > hi , > > thanks for the reply... > > i am using Sybase database but when i execute this with sql-advantage it > will return only one resultset but still i am getting this error. > > My question is in the following example, as per Ibatis how many resultset= s > it is expecting..? > is it assumes first 2 select sqls returning resultsets..? > > //procedure > ****************************************************** > create procedure testinsertTrade @id_trd_ver int > as > begin > declare @id float,@dt_trd int > select @id=3D max(id_trd)+1 from TEST_TRADE > select @dt_trd=3D convert(int, convert(char(12), getdate(), 112)) > insert into TEST_TRADE (id_trd ,id_trd_ver,dt_trd) > VALUES(@id,@id_trd_ver ,@dt_trd) > select * from TEST_TRADE where id_trd=3D@id > end > > ****************************************************** > > > thanks in advance > > Yogish > > > > *"Jeff Butler" * > > 05/23/2006 10:10 EST > ------------------------------ > Please respond to user-java@ibatis.apache.org > ------------------------------ > > > To > > user-java@ibatis.apache.org > cc > > > bcc > > > Subject > > Re: Stored procedure:Temporary select/update/insert sqls before actual > select query > > You need to make sure that these "temporary" selects are not returning > results sets because - as you've already read - iBATIS does not support > multiple result sets. > > The best way to debug would be to run the SP from something like Squirrel > SQL where you can see the results - and make sure that only one result se= t > is coming back. > > If the DB is SQL Server, then the "temporary" selects can be avoided by > doing something like "select ... into #temp ...". With DB2 you have more > explicit control over which queries generate returned result sets. > > Jeff Butler > > > On 5/23/06, *yogisha.b@tcs.com* <*yogisha.b@tcs.com*<= yogisha.b@tcs.com>> > wrote:Hi all, > > I have a problem in getting the resultsets from procedure: > > basically a few sqls are run before executing main select sql with in the > procedure. > when i execute i will get uncategorized exception. if i comment first sq= l > and give hard coded value in second sql it works > > I suspect it will try to map resultsets of first sql to resultMap object. > > And i have read at this point Ibatis doesnt support multiple resultsets, > > In my procedure there are lot of temporary select/update/insert sqls are > executed before main sql will be executed.. in that case > how does IBATIS treat those sql and how do i hide those... and how do i > get resultsets from the last select sql? > > > Following is the sample code where few temporary queries are executed > before the main select sql. > > Pls help in this regard: > > Thanks > Yogish > > > > /********** sample code > > //procedure > > create procedure testGetList @idParam int > > as > > begin > > /*** lot of temporary select ,update insert will goes here before > executing > final select sql../ > > declare @id int > > select @id=3Did_trd from tablex where id_trd=3D@idParam > > select * from tabley where id=3D@id > > end > > //sql maps > > resultMap=3D"resultAll"> > { call testGetList(#idParam#) } > > > > > jdbcType=3D"INTEGER" /> > > > //java call > getSqlMapClientTemplate().queryForList("executeInsert" , trade); > > > exeption.DataBaseException: > org.springframework.jdbc.UncategorizedSQLException: SqlMapClient > operation; > uncategorized SQLException for SQL > > []; SQL state [null]; error code [0]; > --- The error occurred in ibatis/Trade.xml. > --- The error occurred while applying a parameter map. > --- Check the executeInsert-InlineParameterMap. > --- Check the results (failed to retrieve results). > --- Cause: java.lang.NullPointerException; nested exception is > com.ibatis.common.jdbc.exception.NestedSQLException: > --- The error occurred in ibatis/Trade.xml. > --- The error occurred while applying a parameter map. > --- Check the executeInsert-InlineParameterMap. > --- Check the results (failed to retrieve results). > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java > :39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke( > DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:585) > at junit.framework.TestCase.runTest(TestCase.java:154) > at junit.framework.TestCase.runBare(TestCase.java:127) > at junit.framework.TestResult$1.protect(TestResult.java:106) > at junit.framework.TestResult.runProtected(TestResult.java:124) > at junit.framework.TestResult.run(TestResult.java:109) > at junit.framework.TestCase.run (TestCase.java:118) > at junit.framework.TestSuite.runTest(TestSuite.java:208) > at junit.framework.TestSuite.run(TestSuite.java:203) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests ( > RemoteTestRunner.java:478) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run( > RemoteTestRunner.java:344) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main( > RemoteTestRunner.java :196) > Caused by: org.springframework.jdbc.UncategorizedSQLException: > SqlMapClient > operation; uncategorized SQLException for SQL []; SQL state [null]; error > code [0]; > --- The error occurred in ibatis/Trade.xml. > --- The error occurred while applying a parameter map. > --- Check the executeInsert-InlineParameterMap. > --- Check the results (failed to retrieve results). > --- Cause: java.lang.NullPointerException; nested exception is > com.ibatis.common.jdbc.exception.NestedSQLException: > --- The error occurred in ibatis/Trade.xml. > --- The error occurred while applying a parameter map. > --- Check the executeInsert-InlineParameterMap. > --- Check the results (failed to retrieve results). > --- Cause: java.lang.NullPointerException > at > org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate= ( > SQLStateSQLExceptionTranslator.java:96) > at > > org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.trans= late( > SQLErrorCodeSQLExceptionTranslator.java:257) > at > org.springframework.orm.ibatis.SqlMapClientTemplate.execute( > SqlMapClientTemplate.java:168) > at > org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult= ( > SqlMapClientTemplate.java:204) > at > org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList( > SqlMapClientTemplate.java:243) > > Caused by: com.ibatis.common.jdbc.exception.NestedSQLException: > --- The error occurred in ibatis/Trade.xml. > --- The error occurred while applying a parameter map. > --- Check the executeInsert-InlineParameterMap. > --- Check the results (failed to retrieve results). > --- Cause: java.lang.NullPointerException > Caused by: java.lang.NullPointerException > at > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryW= ithCallback > (GeneralStatement.java:188) > at > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryF= orList( > GeneralStatement.java:123) > at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( > SqlMapExecutorDelegate.java:610) > at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( > SqlMapExecutorDelegate.java:584) > at > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList( > SqlMapSessionImpl.java:101) > at > com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList( > SqlMapClientImpl.java :78) > at > org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient( > SqlMapClientTemplate.java:245) > at > org.springframework.orm.ibatis.SqlMapClientTemplate.execute( > SqlMapClientTemplate.java:165) > ... 19 more > Caused by: java.lang.NullPointerException > at > com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults( > SqlExecutor.java:355) > at > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure ( > SqlExecutor.java:291) > at > > com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQ= uery > (ProcedureStatement.java:34) > at > > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryW= ithCallback( > GeneralStatement.java:173) > ... 26 more > > > **********/ > =3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D > Notice: The information contained in this e-mail > message and/or attachments to it may contain > confidential or privileged information. If you are > not the intended recipient, any dissemination, use, > review, distribution, printing or copying of the > information contained in this e-mail message > and/or attachments to it are strictly prohibited. If > you have received this communication in error, > please notify us by reply e-mail or telephone and > immediately and permanently delete the message > and any attachments. Thank you > > > > > ForwardSourceID:NT000021AA > ------=_Part_122753_27853140.1148404431119 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
iBATIS does not read your SP source code so it has no expectation= s.  It will respond to the first results set that is returned and igno= re all others.
 
Since you are only returning one result set, there must be some other = problem.  Sending your SqlMap.xml file would probably be useful for di= agnosis.
 
Jeff Butler

 
On 5/23/06, = yogisha.b@tcs.com <yogisha.b@tcs.com> wrote:

hi ,

thanks for the reply...

i am using Sybase database but when i execute this with sql-advantage it= will return only one resultset but still i am getting this error.

My question is in the following example, as per Ibatis how many resultse= ts it is expecting..?
is it assumes first 2 select sqls returning result= sets..?

//procedure
******************************************************create procedure testinsertTrade   @id_trd_ver int
 as begin
  declare @id float,@dt_trd int
 select = @id=3D max(id_trd)+1 from TEST_TRADE
 select @dt_trd=3D convert(int, convert(char(12), getdate(), 112))=
   insert into TEST_TRADE (id_trd ,id_trd_ver,dt_trd) VALUES(= @id,@id_trd_ver ,@dt_trd)
 select * from TEST_TRADE  where id_trd=3D@id
 end

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


thanks in advance

Yogish


=3D""
 3D""
To  
 3D""
user-java@ibatis.apache.org
3D""
cc  
 3D""
3D""
bcc  <= /div>
 3D""
3D""
Subject &nb= sp;
 3D""
Re: Stored procedure:Temporary select/update/insert sqls before act= ual select query
3D"" 3D""

You need to make sure that these "temporary" selects = are not returning results sets because - as you've already read - iBATIS do= es not support multiple result sets.
 
The best way to debug would be to run the SP from som= ething like Squirrel SQL where you can see the results - and make sure that= only one result set is coming back.
 
If the DB is SQL Server, then the "temporary" se= lects can be avoided by doing something like "select ... into #temp ..= .".  With DB2 you have more explicit control over which queries g= enerate returned result sets.=20
 
Jeff Butler

=  
On 5/23/06, yogisha.b@tcs.com < yogisha.b@tcs.com> wrote:Hi all,

I ha= ve a problem in getting the resultsets from procedure:

basically a few sqls are run= before executing main select sql with in the
procedure.
when i e= xecute  i will get uncategorized exception. if i comment first sq= l
and give hard coded value in sec= ond sql it works

I suspect it= will try to map resultsets of first sql to resultMap object.
And i have read at this point Ibatis doesnt= support multiple resultsets,

In my procedure there are lot of temporary select/update/insert sqls are
executed before main sql will be execut= ed.. in that case
how does IBATIS= treat those sql  and how do i hide those... and how do i<= br> get resultsets from the last select sql?


Following is the  sample code where few temporary= queries are executed
before the = main select sql.=20

Pls help in this regard:

Thanks
Yogish



/********** sample code

//procedure

create procedure testGetList @idParam int

as

begin

/*** lot of temporary s= elect ,update insert will goes here before executing
final select sql../

declare @id int

select @id=3D= id_trd from tablex where id_trd=3D@idParam

select * from tabley where id=3D@id

end

//sql maps

= <procedure id=3D"executeInsert"= ; parameterClass=3D" test.Trade"
resultMap=3D"resultAll">
     { call testGetList(#idParam#) }
</procedure>

     <resultMap id=3D"resu= ltAll" class=3D" test.Trade">
  =          <result column=3D"= id_amt" property=3D"id_trd" jdbcType=3D"DOUBLE" /&= gt;
    &nbs= p;      <result column=3D"id_qnty" pr= operty=3D"id_trd_ver"
     &n= bsp;           jdbcType= =3D"INTEGER" />
&nbs= p;    </resultMap>

//java call
getSqlMapClientTemplate().queryForList(&quo= t;executeInsert" , trade);


exeption.DataBaseException:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation;=
uncategorized SQLException for S= QL

[]; SQL state [null]; erro= r code [0];
--- The error occurred in ibatis= /Trade.xml.
--- The error occurr= ed while applying a parameter map.
--- Check the results (failed to retrieve results).--- Cause: java.lang.NullPointerException ; nested exception is
com.ibatis= .common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis/Trade.xml.
--- The error occurred while applying a parameter map.
--- Check the executeInsert-InlineParameterMap.
--- Check the results (failed to retri= eve results).=20
at sun.reflect.NativeMethodAcces= sorImpl.invoke0(Native Method)
&n= bsp;    at
sun.ref= lect.NativeMethodAccessorImpl.invoke (NativeMethodAccessorImpl.java:39)
sun= .reflect.DelegatingMethodAccessorImpl.invoke( DelegatingMethodAccessorImpl.= java:25)
     at java.lang.reflect.Method.invoke(Method.java:585= )
     at jun= it.framework.TestCase.runTest(TestCase.java:154)
     at junit.framework.TestCase.runBar= e (TestCase.java:127)
  =    at junit.framework.TestResult$1.protect(TestResult.java:106)
     at junit.= framework.TestResult.runProtected(TestResult.java :124)
     at= junit.framework.TestResult.run(TestResult.java:109)
     at junit.framework.TestCase.r= un (TestCase.java:118)
     at junit.framework.TestSuite.runTest(TestSuite.jav= a:208)
     a= t junit.framework.TestSuite.run(TestSuite.java:203)
     at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests (RemoteTest= Runner.java:478)
  &nbs= p;  at
org.eclipse.jdt.inter= nal.junit.runner.RemoteTestRunner.run (RemoteTestRunner.java:344)
 = ;    at
org.eclips= e.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java :19= 6)
Caused by: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient=
operation; uncategorized SQLExce= ption for SQL []; SQL state [null]; error
code [0];
--- The error occurred = in ibatis/Trade.xml.
--- The err= or occurred while applying a parameter map.
--- Check the executeInsert-InlineParameterMap.
--- Check the results (failed to retrieve results).--- Cause: java.lang.NullPointerException ; nested exception is
com.ibatis= .common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis/Trade.xml.
--- The error occurred while applying a parameter map.
--- Check the executeInsert-InlineParameterMap.
--- Check the results (failed to retri= eve results).=20
--- Cause: java.lang.NullPointer= Exception
    = ; at
org.springframework.jdbc.sup= port.SQLStateSQLExceptionTranslator.translate (SQLStateSQLExceptionTranslator.java:96)
     at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.tran= slate (SQLErrorCodeSQLExceptionTranslator.java :257)
     at=
org.springframework.orm.ibatis.S= qlMapClientTemplate.execute(SqlMapClientTemplate.java:168)
     at
org.s= pringframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult (SqlMa= pClientTemplate.java:204)
 &= nbsp;   at
org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClie= ntTemplate.java:243)

Caused b= y: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis/Trade.xml.
--- The error occurred while applying a parameter map.--- Check the executeInsert-InlineParamet= erMap.
--- Check the results (failed to= retrieve results).
--- Cause: ja= va.lang.NullPointerException
Caus= ed by:=20 java.lang.NullPointerException
&n= bsp;    at
com.iba= tis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallba= ck(GeneralStatement.java :188)
     at=
com.ibatis.sqlmap.engine.mapping= .statement.GeneralStatement.executeQueryForList (GeneralStatement.java:123)=
     at
com.i= batis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutor= Delegate.java:610)
  &n= bsp;  at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList( SqlMapEx= ecutorDelegate.java:584)
 &n= bsp;   at
com.ibatis.sq= lmap.engine.impl.SqlMapSessionImpl.queryForList (SqlMapSessionImpl.java:101)
&nbs= p;    at
com.ibati= s.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java :7= 8)
     at
org.s= pringframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapCli= entTemplate.java:245)
  = ;   at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTem= plate.java:165)
  &nbs= p;  ... 19 more
Caused by: j= ava.lang.NullPointerException
     at
com.ibatis.sqlmap.engine.execution.Sq= lExecutor.handleResults(SqlExecutor.java:355)
     at
com.ibatis.sqlmap.engine.execution.SqlE= xecutor.executeQueryProcedure (SqlExecutor.java:291)
     at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStateme= nt.sqlExecuteQuery (ProcedureStatement.java:34)
&nbs= p;    at
com.ibati= s.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback= (GeneralStatement.java :173)
     ..= . 26 more


**********/
=3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D-= ----=3D=3D=3D=3D=3D
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you = are=20
not the intended recipient, any = dissemination, use,
review, distr= ibution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
= you have received this communication in err= or,=20
please notify us by reply e-mail= or telephone and
immediately and= permanently delete the message
a= nd any attachments. Thank you




ForwardSourceID:NT000= 021AA

------=_Part_122753_27853140.1148404431119--