Hi Greg,
Glad you resolved it, and good to know the cause. I remember Oracle
driver quality issues plagued us since the early days of Cayenne, but
since I rarely use Oracle myself these days, I've been out of the
loop on the latest set of issues :-)
Andrus
On Jul 17, 2007, at 12:06 AM, Greg Boshart wrote:
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
