Dear Andrus,
Have you got a chance to work on the problem?
For a summary, our investigations show that using JDBC, the 'auto
commit' attribute set to 'true' solve the problem. Is it possible to
execute the stored procedure using that behavior? We already looked to
set that flag to a particular connection within cayenne, but without any
success...
Thanks!
Marc Gabriel
-----Original Message-----
From: Andrus Adamchik [mailto:andru..bjectstyle.org]
Sent: Tuesday, April 22, 2008 10:06 AM
To: use..ayenne.apache.org
Subject: Re: Stored proc returning result set (and output params) with
MS SQL server
Ah, I used the MS driver. Let me also try it with jTDS when I have a
bit more time.
Andrus
On Apr 22, 2008, at 10:59 AM, Marc Gabriel-Willem wrote:
> Hello Andrus,
>
> In our test cases, the manual transaction did not avoid the
> exception in
> error condition! So it is impossible to use the QueryResponse.
>
> Please find below the stack trace of the error:
>
> INFO QueryLogger: --- will run 1 query.
> INFO QueryLogger: --- transaction started.
> INFO QueryLogger: {? = call spGetXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
> [bind: NULL, NULL, NULL, NULL, NULL, NULL, NULL,
> '<param><name>tDetections.date_created</name><operator>between</
> operator
>> <lvalue>2008-04-22 00:00:00</lvalue><rvalue>2008-04-22
> 23:59:59</rvalue><type>DateTime</type></param>', NULL, NULL, NULL]
> INFO QueryLogger: *** error.
> java.sql.SQLException: Uncommittable transaction is detected at the
> end
> of the batch. The transaction is rolled back.
> at
> net
> .sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java
> :368)
> at
> net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
> at
> net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
> at
> net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:636)
> at
> net
> .sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.jav
> a:584)
> at
> net
> .sourceforge.jtds.jdbc.JtdsStatement.cacheResults(JtdsStatement.java:
> 632)
> at
> net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:594)
> at
> org
> .apache.cayenne.access.jdbc.JDBCResultIterator.checkNextRow(JDBCResul
> tIterator.java:268)
> at
> org
> .apache.cayenne.access.jdbc.JDBCResultIterator.<init>(JDBCResultItera
> tor.java:91)
> at
> org
> .apache.cayenne.access.jdbc.BaseSQLAction.readResultSet(BaseSQLAction
> .java:68)
> at
> org
> .apache.cayenne.dba.sqlserver.SQLServerProcedureAction.performAction(
> SQLServerProcedureAction.java:86)
> at
> org
> .apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryActi
> on.java:59)
> at
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:273)
> at
> org
> .apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQuery
> Action.java:301)
> at
> org.apache.cayenne.access.DataDomainQueryAction.access
> $000(DataDomainQue
> ryAction.java:60)
> at
> org.apache.cayenne.access.DataDomainQueryAction
> $1.transform(DataDomainQu
> eryAction.java:273)
> at
> org
> .apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:82
> 6)
> at
> org
> .apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(Da
> taDomainQueryAction.java:270)
> at
> org
> .apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryA
> ction.java:110)
> at
> org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:746)
> at
> org
> .apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQ
> ueryAction.java:217)
> at
> org
> .apache.cayenne.access.DataContextQueryAction.execute(DataContextQuer
> yAction.java:54)
> at
> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1395)
> at
> org
> .apache.cayenne.access.DataContext.performGenericQuery(DataContext.ja
> va:1350)
> at
> com
> .sideinternational.sas.database.AbstractSecureStatement.exec(Abstract
> SecureStatement.java:83)
> at
> com
> .sideinternational.sas.database.SecureQuery.execute(SecureQuery.java:
> 69)
> at
> com
> .sideinternational.sas.database.SecureQuery.executeQuery(SecureQuery.
> java:203)
> at
> com
> .sideinternational.sas.database.SecureQuery.execute(SecureQuery.java:
> 130)
> at
> test.jdbc.MSSQLProcedureTest.testGetXXX(MSSQLProcedureTest.java:104)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
> Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at junit.framework.TestCase.runTest(TestCase.java:154)
> at junit.framework.TestCase.runBare(TestCase.java:127)
> at junit.framework.TestResult$1.protect(TestResult.java:106)
> at junit.framework.TestResult.runProtected(TestResult.java:124)
> at junit.framework.TestResult.run(TestResult.java:109)
> at junit.framework.TestCase.run(TestCase.java:118)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at junit.framework.TestSuite.runTest(TestSuite.java:208)
> at junit.framework.TestSuite.run(TestSuite.java:203)
> at
> org
> .eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUn
> it3TestReference.java:128)
> at
> org
> .eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.ja
> va:38)
> at
> org
> .eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTe
> stRunner.java:460)
> at
> org
> .eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTe
> stRunner.java:673)
> at
> org
> .eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRun
> ner.java:386)
> at
> org
> .eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRu
> nner.java:196)
>
>
> Here is an extract of our source code:
>
> Transaction tx = pm_context.getParentDataDomain().createTransaction();
> Transaction.bindThreadTransaction(tx);
>
> QueryResponse result;
> try {
> System.out.println("Before performGenericQuery");
> result = pm_context.performGenericQuery(m_procedureQuery);
> System.out.println("After performGenericQuery");
> } 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);
> }
>
> Obviously, the display "After performGenericQuery" is never executed
> when error situation occurred (the date conversion problem).
>
> Thank you again for your help.
>
> Marc Gabriel
>
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andru..bjectstyle.org]
> Sent: Monday, April 21, 2008 5:32 PM
> To: use..ayenne.apache.org
> Subject: Re: Stored proc returning result set (and output params) with
> MS SQL server
>
> 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
>
>
>
> ------------------------------------------------------------------
> 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 28 2008 - 08:42:01 EDT