cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Gabriel-Willem" <marc.gabriel-wil...@side-international.com>
Subject Stored proc returning result set (and output params) with MS SQL server
Date Thu, 10 Apr 2008 12:36:55 GMT
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


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message