You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by yo...@tcs.com on 2006/05/23 16:39:32 UTC

Stored procedure:Temporary select/update/insert sqls before actual select query

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.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

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

Posted by Jeff Butler <je...@gmail.com>.
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, yogisha.b@tcs.com <yo...@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.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
>
>
>