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:andru..bjectstyle.org]
> Sent: Sunday, April 20, 2008 5:10 PM
> To: use..ayenne.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
>>
>
>
>
>
> ------------------------------------------------------------------
> CONFIDENTIALITY: This e-mail and any files transmitted with it are
> confidential and intended solely for the use of the recipient(s)
> only. Any review, retransmission, dissemination or other use of, or
> taking any action in reliance upon this information by persons or
> entities other than the intended recipient(s) is prohibited. If you
> have received this e-mail in error please notify the sender
> immediately and destroy the material whether stored on a computer or
> otherwise.
>
> DISCLAIMER: Any views or opinions presented within this e-mail are
> solely those of the author and do not necessarily represent those of
> SIDE International S.A. - EastNets Group, unless otherwise
> specifically stated.
> ------------------------------------------------------------------
> Please consider the environment before deciding to print this email.
>
>
This archive was generated by hypermail 2.0.0 : Mon Apr 21 2008 - 11:09:28 EDT