Hi,
Exellant.... i am able to get the resultsets back after passing IGNORE_DONE_IN_PROC parameter as jdbc url.
thanks for the reply...
Yogish
To |
[email protected] | |
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" <[EMAIL PROTECTED]> [mailto:"Jeff Butler" <[EMAIL PROTECTED]>]
Sent: Tuesday, May 23, 2006 10:14 AM
To: [email protected]
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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
end
******************************************************
thanks in advance
Yogish
|
To |
[email protected] |
cc |
| |
bcc |
| |
Subject |
Re: Stored procedure:Temporary select/update/insert sqls before actual select query | |
|
|
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, [EMAIL PROTECTED] < [EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
select * from tabley where [EMAIL PROTECTED]
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.translate (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.executeQueryWithCallback(GeneralStatement.java :188)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList (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.sqlExecuteQuery (ProcedureStatement.java:34)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback (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
