Then I didn't understand your question... In my test cases, using a
manual transaction would prevent an exception and I could read the out
parameters without a problem, even on error conditions.
Andrus
On Apr 21, 2008, at 6:20 PM, Marc Gabriel-Willem wrote:
Hi Andrus,
We are using intensively the QueryResponse ;)
Here is the problem; the call of the 'performGenericQuery' method
throws
an exception. So we don't have any chance to use the QueryResponse!
Even if the stored proc does not return any 'result set' in that error
situation, we need to be able to handle the output parameters.
Marc Gabriel
-----Original Message-----
From: Andrus Adamchik [mailto:[EMAIL PROTECTED]
Sent: Monday, April 21, 2008 5:09 PM
To: user@cayenne.apache.org
Subject: Re: Stored proc returning result set (and output params) with
MS SQL server
QueryResponse is the most generic response that you can get out of
Cayenne, designed to incorporate multiple results sets, update counts,
and surely - stored procedure out values.
The OUT values are returned as the first "resultList". So you can do
something like this:
if(result.next()) {
List outParams = result.currentList();
}
if(result.next()) {
List actualResult = result.currentList();
}
Andrus
On Apr 21, 2008, at 5:51 PM, Marc Gabriel-Willem wrote:
Hello,
Thank you for your investigation.
Your workaround solves partially the problem. It is a good think
that we
can 'commit' or 'rollback' the transaction ourself.
But it remains a major problem regarding the QueryResponse returned
by
the 'performGenericQuery' method. Our low level layer requires the
'output parameters' returned by the stored procedure.
Do you have another idea for us?
Thank you again.
Marc Gabriel
-----Original Message-----
From: Andrus Adamchik [mailto:[EMAIL PROTECTED]
Sent: Sunday, April 20, 2008 5:10 PM
To: user@cayenne.apache.org
Subject: Re: Stored proc returning result set (and output params)
with
MS SQL server
I think I found the problem. It was not a stored procedure call per
se, but a transaction surrounding it. When wrapping a JDBC failing SP
call with "connection.setAutoCommit(false)" and "conneciton.commit()"
I get the same error as with Cayenne. Here is how to handle this in
Cayenne workaround - use manual transaction handling, doing rollback
instead of commit when the SP return values indicate a failure:
Transaction tx = context.getParentDataDomain().createTransaction();
Transaction.bindThreadTransaction(tx);
QueryResponse result;
try {
result = context.performGenericQuery(query);
} finally {
try {
// here you can be smarter and do a commit/rollback
// based on the SP output if you care
tx.rollback();
} catch (Exception e) {
throw new RuntimeException(e);
}
Transaction.bindThreadTransaction(null);
}
Andrus
On Apr 17, 2008, at 3:52 PM, Andrus Adamchik wrote:
So Cayenne calls the sp in the following format:
{? = call spXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
In your JDBC test you do it like this:
{ call spXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
I.e. no return value is specified. So what if you uncheck "returning
value" checkbox for this stored procedure? Would you still get the
same error? (Sorry if my questions sound a bit random, I personally
haven't used stored procedures with Cayenne or JDBC for a couple of
years already, so my memory of all the quirks is a bit blurry).
If this doesn't work, could you send me a stored procedure
definition so that I could test that locally. If you don't want to
send it to the public mailing list, you can email it to me directly.
Andrus
