Thanks Andrus. It appears that the stored procedure issue is resolved with
newer drivers from Oracle. (I've pasted the manifest information for the two
different jar files that were tested at the end of this email.) I tried
updating my drivers at the beginning of this process but I guess my classpath
was not updated appropriately.
The issue was due to an erroneous value being returned by the
CallableStatement.getUpdateCount() method. It should have been returning a -1
to indicate that there were no more results to be returned. Instead, it was
returning 1 which locked the code in an infinite loop.
The code block that was executing is from the ProcedureAction.performAction()
method:
// read the rest of the query
while (true) {
if (statement.getMoreResults()) {
ResultSet rs = statement.getResultSet();
try {
RowDescriptor descriptor = describeResultSet(
rs, processedResultSets++);
readResultSet(rs, descriptor, query, observer);
}
finally {
try {
rs.close();
}
catch (SQLException ex) {
}
}
}
else {
int updateCount = statement.getUpdateCount(); // ß
erroneously returned 1 instead of -1
if (updateCount == -1) {
break;
}
QueryLogger.logUpdateCount(updateCount);
observer.nextCount(query, updateCount);
}
}
Bad Jar Manifest info:
Manifest-Version: 1.0
Implementation-Version: "Oracle JDBC Driver version - 10.1.0.2.0"
Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
Specification-Version: "Oracle JDBC Driver version - 10.1.0.2.0"
Implementation-Title: "ojdbc14.jar"
Created-By: 1.2.2 (Sun Microsystems Inc.)
Implementation-Time: "Wed Jan 21 00:48:12 2004"
Implementation-Vendor: "Oracle Corporation"
Specification-Vendor: "Oracle Corporation" .
Successful Manifest info:
Manifest-Version: 1.0
Implementation-Version: "Oracle JDBC Driver version - 10.1.0.5.0"
Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
Specification-Version: "Oracle JDBC Driver version - 10.1.0.5.0"
Implementation-Title: "ojdbc14.jar"
Created-By: 1.2.2 (Sun Microsystems Inc.)
Implementation-Time: "Wed Dec 28 05:06:13 2005"
Implementation-Vendor: "Oracle Corporation"
Specification-Vendor: "Oracle Corporation" .
Greg
-----Original Message-----
From: Andrus Adamchik [mailto:[EMAIL PROTECTED]
Sent: Saturday, July 14, 2007 2:25 PM
To: [email protected]
Subject: Re: problems with Oracle stored procedure
On Jul 13, 2007, at 5:58 PM, Greg Boshart wrote:
> Thanks Andrus, I appreciate your help. What version of Cayenne are
> you using?
I tested both 2.0.3 and 3.0M1 (essentially trunk code).
First I ran an unchanged unit test suite against 2.0.3 code. Namely this test:
https://svn.apache.org/repos/asf/cayenne/main/branches/STABLE-2.0/
cayenne/cayenne-java/src/tests/java/org/apache/cayenne/access/
DataContextProcedureQueryTst.java
against this procedure definition:
https://svn.apache.org/repos/asf/cayenne/main/branches/STABLE-2.0/
cayenne/cayenne-java/src/tests/resources/ddl/oracle/create-update-sp.sql
It passed. But since it does manual transaction management, I did another test
against the trunk, removing transaction code from the procedure, and from the
unit test. I.e. it was running under default Cayenne transaction management. It
passed again.
> What about your Oracle JDBC drivers?
From the driver jar MANIFEST.MF:
Specification-Version: Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Version: Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Vendor: Oracle Corporation
Implementation-Time: Wed Jun 22 18:55:48 2005
> Does your stored procedure mapping look like:
>
> <procedure name="cayenne_tst_upd_proc">
> <procedure-parameter name="painting_price"
> type="INTEGER" direction="in"/>
> </procedure>
Yes.
Andrus
