Hi Marc Gabriel,
For information, using JDBC directly the situation is handled
correctly.
Could you post the JDBC code you used to read the data?
Andrus
On Apr 10, 2008, at 3:36 PM, Marc Gabriel-Willem wrote:
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
