Dear all,
First of all, thank you for your great product. We are using it successfully since last year and we are very happy of the product quality.
Few days ago, we found a problem with our stored procedures access.
In order to illustrate you the problem in the best possible way, I'm going to use the following example.
This is an extract from our cayenne mapping file:
...
<procedure name="spXXX" catalog="spp" returningValue="true">
<procedure-parameter name=..ETURN_VALUE" type="INTEGER" length="4" direction="in"/>
<procedure-parameter name=..o_result_cursor" type="VARCHAR" length="8" direction="in_out"/>
<procedure-parameter name=..o_version" type="VARCHAR" length="8" direction="in_out"/>
<procedure-parameter name=..o_error_message" type="VARCHAR" length="4000" direction="in_out"/>
<procedure-parameter name=..o_error" type="NUMERIC" length="20" direction="in_out"/>
<procedure-parameter name=..o_nbrecord" type="NUMERIC" length="20" direction="in_out"/>
<procedure-parameter name=..i_operator" type="VARCHAR" length="100" direction="in"/>
<procedure-parameter name=..i_parameters" type="VARCHAR" length="4000" direction="in"/>
<procedure-parameter name=..i_maxrow" type="INTEGER" length="4" direction="in"/>
<procedure-parameter name=..i_fromrow" type="INTEGER" length="4" direction="in"/>
<procedure-parameter name=..i_idonly" type="INTEGER" length="4" direction="in"/>
</procedure>
...
Please note that stored procedure returns a result set ..ETURN_VALUE ... with the funny 'in' direction I know) and several output parameters.
We have the following problem.
Sometimes, due to internal error that can always occur, the stored procedure may not return a result set.
In fact, when the stored procedure catches an internal error, that error is returned to the caller using the output parameters ... but no result set is provided (it's a normal behaviour as data are may be not accessible!).
But it seems cayenne requires to receive from the stored proc this 'mapped' result set, otherwise the following exception is displayed:
INFO QueryLogger: {? = call spXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [bind: NULL, NULL, NULL, NULL, NULL, NULL, 'side', '<param><name>tTransaction.from_id</name><operator>=</operator><lvalue>400</lvalue><rvalue></rvalue><type>Number</type></param><param><name>tDateIds.day</name><operator>between</operator><lvalue>2008-04-10 00:00:00</lvalue><rvalue>2008-04-10 23:59:59</rvalue><type>DateTime</type></param>', 27, 1, 1]
INFO QueryLogger: === updated 1 row.
INFO QueryLogger: === updated 0 rows.
INFO QueryLogger: *** error.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.getMoreResults(Unknown Source)
at org.apache.cayenne.dba.sqlserver.SQLServerProcedureAction.performAction(SQLServerProcedureAction.java:105)
at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:59)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:273)
at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:301)
at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:60)
at org.apache.cayenne.access.DataDomainQueryAction$1.transform(DataDomainQueryAction.java:273)
at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:836)²
at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:270)
at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:110)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:217)
at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:54)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1395)
at org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:1350)
....
We did some test with the JTDS drivers too, but unfortunately with the same result (using cayenne 2.0.4).
For information, using JDBC directly the situation is handled correctly.
I do hope you will be able to give us a solution in order to fix that issue, which is blocking for us.
Thank you in advance.
Marc Gabriel
This archive was generated by hypermail 2.0.0 : Thu Apr 10 2008 - 08:37:35 EDT