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 : Tue Apr 22 2008 - 04:06:25 EDT