Hello Andrus,
The JDBC code used to call the stored procedure is really trivial.
Please find below our test case.
Connection connection = null;
CallableStatement statement = null;
try
{
connection = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433;databasename=mydb;", "guest", "guest");
statement = connection.prepareCall ("{ call spXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
statement.registerOutParameter(1, java.sql.Types.VARCHAR);
statement.registerOutParameter(2, java.sql.Types.VARCHAR);
statement.registerOutParameter(3, java.sql.Types.VARCHAR);
statement.registerOutParameter(4, java.sql.Types.NUMERIC);
statement.registerOutParameter(5, java.sql.Types.NUMERIC);
statement.setString(6, "guest");
statement.setString(7, "myData");
statement.setInt(8, 60);
statement.setInt(9, 1);
statement.setInt(10, 1);
statement.executeUpdate();
if (statement.getMoreResults())
{
ResultSet rs = statement.getResultSet();
while (rs.next())
{
System.out.println(rs.getInt("ID"));
}
}
System.out.println("Proc status: " + statement.getString(1) + " " + statement.getString(2) + " " + statement.getString(3) + " " + statement.getInt(4) + " " + statement.getInt(5));
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
close(connection, statement);
}
Again, we would like to insist on the fact that the crash occurs when the called stored procedure does not return any result set.
Thank you again for your help. We appreciate very much.
Marc Gabriel
-----Original Message-----
From: Andrus Adamchik [mailto:andru..bjectstyle.org]
Sent: Friday, April 11, 2008 7:09 PM
To: use..ayenne.apache.org
Subject: Re: Stored proc returning result set (and output params) with MS SQL server
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=..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 : Tue Apr 15 2008 - 04:38:30 EDT