cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Stored proc returning result set (and output params) with MS SQL server
Date Mon, 21 Apr 2008 15:08:54 GMT
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:andrus@objectstyle.org]
> 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
>>
>
>
>
>
> ------------------------------------------------------------------
> 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.
>
>


Mime
View raw message