Re: problems with Oracle stored procedure

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Mon Jul 16 2007 - 17:12:07 EDT

  • Next message: Andrus Adamchik: "Cayenne Query trivia"

    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: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:12:34 EDT