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="@RETURN_VALUE" type="INTEGER" length="4"
direction="in"/>
<procedure-parameter name="@po_result_cursor" type="VARCHAR"
length="8" direction="in_out"/>
<procedure-parameter name="@po_version" type="VARCHAR" length="8"
direction="in_out"/>
<procedure-parameter name="@po_error_message" type="VARCHAR"
length="4000" direction="in_out"/>
<procedure-parameter name="@po_error" type="NUMERIC" length="20"
direction="in_out"/>
<procedure-parameter name="@po_nbrecord" type="NUMERIC" length="20"
direction="in_out"/>
<procedure-parameter name="@pi_operator" type="VARCHAR"
length="100" direction="in"/>
<procedure-parameter name="@pi_parameters" type="VARCHAR"
length="4000" direction="in"/>
<procedure-parameter name="@pi_maxrow" type="INTEGER" length="4"
direction="in"/>
<procedure-parameter name="@pi_fromrow" type="INTEGER" length="4"
direction="in"/>
<procedure-parameter name="@pi_idonly" type="INTEGER" length="4"
direction="in"/>
</procedure>
...
Please note that stored procedure returns a result set (@RETURN_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
