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:andru..bjectstyle.org]
Sent: Saturday, July 14, 2007 2:25 PM
To: use..ayenne.apache.org
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
This archive was generated by hypermail 2.0.0 : Mon Jul 16 2007 - 17:06:32 EDT