Derby 10.3.2.1 throws the following SQLException:

 

SQLState XJ215, Error code 20000: java.sql.SQLException: You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after the Blob/Clob's transaction has been committed or rolled back.

 

When the application does something like this (pseudo code):

 

stmt = conn.prepareStatement(“SELECT anIntegerColumn, aBlobColumn FROM someTable”);

ResultSet rs = stmt.executeQuery();

int currentInt, bestInt = 0;

Blob bestBlob = null;

while (rs.next()) {

      currentInt = rs.getInt(1);

      if (currentInt > bestInt) {

               bestInt = currentInt;

               bestBlob = rs.getBlob(2);

      }

}

int blobLength = bestBlob.length();  // exception is thrown

 

 

org.apache.derby.impl.jdbc.EmbedResultSet reveals the following comment in movePosition():

 

                                  // The ResultSet may implicitly close when the ResultSet type

                                  // is TYPE_FORWARD_ONLY and the next method of ResultSet returns

                                  // false. This will cause a commit if autocommit = true.

 

The JDBC specification does not appear to request this behavior, and Sun’s JDBC tech guide (http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/resultset.html#1012149) says “A cursor remains valid until the ResultSet object or its parent Statement object is closed.”

 

Would you agree that closing the ResultSet when the next() method returns false is an undesirable side effect which conflicts with the expected behavior, or have I overlooked something?