ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shannon, Bryan" <BShan...@Tribune.com>
Subject RE: Stored procedure:Temporary select/update/insert sqls before a ctual select query
Date Thu, 01 Jun 2006 18:51:57 GMT
I noticed that some peculiar things with Sybase changed when 2.0.9b became
2.1.0.  SqlExecutor.java was updated.
 
(looks like it was the change made for Oracle REFCURSOR support, but I'm not
too familiar with Oracle.
 
With me, after the upgrade to any release after 2.1.0, I would get strange
results, much as if Sybase were internally doing a "rollback" on the
connection instead of a commit.  It was going through all the motions of
inserting, gettting @@identity, etc.... But by the time commit was called
after many update/inserts, the transaction would just not take effect.  No
exceptions thrown, ... Just.... *nothing* !!!
 
This problem is described in some of my earlier (more naive) emails. :-)
 
 
So, until this thread happened, I was stuck.  I thought "oh man, I can't
EVER upgrade iBatis without manually re-patching and recompiling this
SqlExecutor.java".  Turned out that adding the "IGNORE_DONE_IN_PROC=true" to
my jdbc url worked like a charm.  I can say that changing this flag to true
doesn't really hurt me (since the multiple resultsets are being generated by
multiple sybase triggers+stored procedure combinations) but it might hurt
other people.  It changes the way the "number of rows affected" is returned
back through the JDBC level.
 
 
Can anyone else comment on this?   My problem is that I can't figure out
exactly why this could have been causing a transaction to roll back.  Did a
fix for Oracle break Sybase (in the 2.0.9b release, maybe I got weird
"number of rows affected" in rare instances that I didn't care about, but at
least the transaction was never silently rolled back!)  
 
I can only narrow it down so far to "something fishy with the change to
SqlExecutor.java when a bunch of statements are in a single transaction and
one of them calls a stored procedure that happens to call a trigger and one
of those statements returns more than one result!"   (Note that this might
also be Yogish's problem:  I found that if a stored procedure calls a
trigger, it might actually be generating multiple resultsets even though in
most SQL programs you only get one of them.)
 
 
Any comment would be greatly appreciated!  
 
Thanks guys!
 
-Bryan Shannon
 
 
The diffs between the 2.0.9b release and the 2.1.0 release in the file
SqlExecutor.java follow...  executeUpdate() and executeQuery() calls within
were changed to plain "execute()".
 
83,84c83,87
<       rows = ps.executeUpdate();
<     } finally {
---
>
>       ps.execute();
>       rows = ps.getUpdateCount();
>     }
>     finally {
176c179,181
<       rs = ps.executeQuery();
---
>
>       ps.execute();
>       rs = ps.getResultSet();
230c235,237
<       rows = cs.executeUpdate();
---
>
>       cs.execute();
>       rows = cs.getUpdateCount();
279c286,288
<       rs = cs.executeQuery();
---
>
>       cs.execute();
>       rs = cs.getResultSet();
354,355c363,364
<           Object[] columnValues = resultMap.getResults(request, rs);
<           callback.handleResultObject(request, columnValues);
---
>           Object[] columnValues = resultMap.resolveSubMap(request,
rs).getResults(request, rs);
>           callback.handleResultObject(request, columnValues, rs);


-----Original Message-----
From: yogisha.b@tcs.com [mailto:yogisha.b@tcs.com]
Sent: Wednesday, May 24, 2006 4:53 AM
To: user-java@ibatis.apache.org
Subject: RE: Stored procedure:Temporary select/update/insert sqls before
actual select query



Hi,

 

Excellent.... i am able to get the resultsets back after passing
IGNORE_DONE_IN_PROC parameter as jdbc url.

 

 

thanks for the reply...

 

Yogish






Christopher.Mathrusse@sybase.com 

05/23/2006 10:23 MST 

  _____  

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	
	 
	

Sybase JDBC driver complies with the JDBC specification, so multiple
ResultSets can be returned. As Jeff has stated iBatis does not support
multiple result sets. I've come across this issue myself and the solution is
to add the following parameter to the JDBC URL:
 
IGNORE_DONE_IN_PROC=true
 
Please refer to the Sybase JConnect documentation pertaining to this
parameter. Basically what it does is tells the JDBC driver to not return
multiple result sets.

  _____  


From: "Jeff Butler" <jeffgbutler@gmail.com> [mailto:"Jeff Butler"
<jeffgbutler@gmail.com>] 
Sent: Tuesday, May 23, 2006 10:14 AM
To: user-java@ibatis.apache.org
Subject: Re: Stored procedure:Temporary select/update/insert sqls before
actual select query

iBATIS does not read your SP source code so it has no expectations.  It will
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,  <mailto:yogisha.b@tcs.com> yogisha.b@tcs.com <
<mailto: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 resultsets
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= max(id_trd)+1 from TEST_TRADE 
 select @dt_trd= 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  <mailto:id_trd=@id> id_trd=@id
 end

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


thanks in advance

Yogish






	[IMAGE] 
	"Jeff Butler" <  <mailto:jeffgbutler@gmail.com>
jeffgbutler@gmail.com> 

05/23/2006 10:10 EST 

  _____  

	Please respond to  <mailto:user-java@ibatis.apache.org>
user-java@ibatis.apache.org 

  _____  


	[IMAGE]

To  

	[IMAGE]
 <mailto:user-java@ibatis.apache.org> user-java@ibatis.apache.org


	[IMAGE]

cc  

	[IMAGE]


	[IMAGE]

bcc  

	[IMAGE]


	[IMAGE]

Subject  

	[IMAGE]
Re: Stored procedure:Temporary select/update/insert sqls before actual
select query 


	[IMAGE]

	[IMAGE]

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 set
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,  <mailto:yogisha.b@tcs.com> yogisha.b@tcs.com <
<mailto: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 sql 
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=id_trd from tablex where id_trd=@idParam

select * from tabley where id=@id

end

//sql maps

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

     <resultMap id="resultAll" class=" test.Trade">
           <result column="id_amt" property="id_trd" jdbcType="DOUBLE" /> 
           <result column="id_qnty" property="id_trd_ver" 
                 jdbcType="INTEGER" />
     </resultMap>

//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.translat
e (SQLErrorCodeSQLExceptionTranslator.java :257)
     at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemp
late.java:168)
     at
org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult
(SqlMapClientTemplate.java:204)
     at
org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClien
tTemplate.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.executeQueryWith
Callback(GeneralStatement.java :188)
     at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForL
ist (GeneralStatement.java:123)
     at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExec
utorDelegate.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(SqlMa
pClientTemplate.java:245)
     at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemp
late.java:165) 
     ... 19 more
Caused by: java.lang.NullPointerException 
     at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.jav
a:355)
     at 
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure
(SqlExecutor.java:291)
     at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuer
y (ProcedureStatement.java:34)
     at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
Callback (GeneralStatement.java :173)
     ... 26 more


**********/
=====-----=====-----=====
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 


ForwardSourceID:NT000021B2 


Mime
View raw message