cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Gabriel-Willem" <marc.gabriel-wil...@side-international.com>
Subject RE: Stored proc returning result set (and output params) with MS SQL server
Date Mon, 21 Apr 2008 14:51:43 GMT
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