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:andru..bjectstyle.org]
> Sent: Monday, April 21, 2008 5:09 PM
> To: use..ayenne.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: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
This archive was generated by hypermail 2.0.0 : Mon Apr 21 2008 - 11:32:16 EDT