Derby
  1. Derby
  2. DERBY-4224

Commit after setAsciiStream() makes execution fail with the client driver

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Embedded/Client difference, Regression

      Description

      Got below error message when running a JDBC prolgram with derby. I use Apache Derby Network Server - 10.4.2.0 - (689064). The same program works fine with another version Apache Derby Network Server - 10.2.2.0 - (485682). It looks like there is a regression between the two versions.
      Exception in thread "main" 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.
      at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
      at org.apache.derby.client.am.Lob.checkValidity(Unknown Source)
      at org.apache.derby.client.am.Clob.length(Unknown Source)
      at org.apache.derby.client.net.NetStatementRequest.computeProtocolTypesAndLengths(Unknown Source)
      at org.apache.derby.client.net.NetStatementRequest.buildSQLDTAcommandData(Unknown Source)
      at org.apache.derby.client.net.NetStatementRequest.writeExecute(Unknown Source)
      at org.apache.derby.client.net.NetPreparedStatement.writeExecute_(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.writeExecute(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.execute(Unknown Source)
      at DerbyTest.test(DerbyTest.java:36)
      at DerbyTest.main(DerbyTest.java:12)
      Caused by: org.apache.derby.client.am.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.
      ... 12 more

      Below is the test program I used.

      import java.io.ByteArrayInputStream;
      import java.io.IOException;
      import java.io.InputStream;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.SQLException;
      import java.sql.Statement;

      public class DerbyTest {
      public static void main(String[] args) throws SQLException, IOException

      { new DerbyTest().test(); }

      private void test() throws SQLException, IOException {
      Connection conn = null;

      try

      { conn = getConnection(); Statement stmt = conn.createStatement(); dropTable("test1"); dropTable("test2"); String createStr1 = "CREATE TABLE test1 (col0 VARCHAR(8000))"; String createStr2 = "CREATE TABLE test2 (col0 VARCHAR(8000))"; stmt.executeUpdate(createStr1); stmt.executeUpdate(createStr2); stmt.close(); PreparedStatement pstmt1 = conn.prepareStatement("INSERT INTO test1 VALUES (?)"); PreparedStatement pstmt2 = conn.prepareStatement("INSERT INTO test1 VALUES (?)"); InputStream in1 = new ByteArrayInputStream("abcdefghijklmnopqrstuvwxyz0123456789".getBytes()); InputStream in2 = new ByteArrayInputStream("9876543210ZYXWVUTSRQPONMLKJIHGFEDCBA".getBytes()); pstmt1.setAsciiStream(1, in1, in1.available()); pstmt2.setAsciiStream(1, in2, in2.available()); pstmt1.execute(); pstmt2.execute(); pstmt1.close(); pstmt2.close(); System.out.println("Successful"); }

      finally

      { if (conn != null) conn.close(); }

      }

      private Connection getDriverConnection() throws SQLException

      { String connectionURL = "jdbc:derby://localhost:1527/testdb;user=app;password=derby;create=true"; return DriverManager.getConnection(connectionURL); }

      private Connection getConnection() throws SQLException

      { return getDriverConnection(); }

      public void dropTable(String tableName) throws SQLException {
      Connection conn = getConnection();
      Statement stmt = conn.createStatement();
      try

      { stmt.executeUpdate("DROP TABLE " + tableName); }

      catch (SQLException sqle)

      { System.out.println("Error occured when drop table: " + sqle.getMessage()); }

      finally {
      if (stmt != null)

      { stmt.close(); }

      if (conn != null)

      { conn.close(); }

      }
      }
      }

        Issue Links

          Activity

          Hide
          Kathey Marsden added a comment -

          This was fixed with the fix for DERBY-4312.

          Show
          Kathey Marsden added a comment - This was fixed with the fix for DERBY-4312 .
          Hide
          Kathey Marsden added a comment -

          This issue was introduced with DERBY-3574. Special handling will be needed when client side lobs are used for setXXXStream parameters.

          Show
          Kathey Marsden added a comment - This issue was introduced with DERBY-3574 . Special handling will be needed when client side lobs are used for setXXXStream parameters.
          Hide
          Dag H. Wanvik added a comment -

          Right, makes sense. Thanks, Knut.

          Show
          Dag H. Wanvik added a comment - Right, makes sense. Thanks, Knut.
          Hide
          Knut Anders Hatlen added a comment -

          Since this seems to be an unintended side-effect of the LOB changes in 10.3 (note that the data type is VARCHAR, so it shouldn't have been affected) and a regression, I'm leaning towards keeping the current classification as a bug.

          Show
          Knut Anders Hatlen added a comment - Since this seems to be an unintended side-effect of the LOB changes in 10.3 (note that the data type is VARCHAR, so it shouldn't have been affected) and a regression, I'm leaning towards keeping the current classification as a bug.
          Hide
          Dag H. Wanvik added a comment -

          Triaged for 10.5.2, "repro attached" and setting "normal" urgency.

          Should we change this issue to an "improvement", Knut?

          Show
          Dag H. Wanvik added a comment - Triaged for 10.5.2, "repro attached" and setting "normal" urgency. Should we change this issue to an "improvement", Knut?
          Hide
          Simon Meng added a comment -

          Your explanation is reasonable. Hope derby can provide an improvement for stream operation.

          Show
          Simon Meng added a comment - Your explanation is reasonable. Hope derby can provide an improvement for stream operation.
          Hide
          Knut Anders Hatlen added a comment -

          Updating the summary with a more accurate description of the problem.

          Also flagging this as an embedded/client difference, since the example program appears to work fine with the embedded driver.

          Show
          Knut Anders Hatlen added a comment - Updating the summary with a more accurate description of the problem. Also flagging this as an embedded/client difference, since the example program appears to work fine with the embedded driver.
          Hide
          Knut Anders Hatlen added a comment -

          The reason why the execution of the first statement makes the second
          statement fail, is that the two statements execute in the same
          connection and auto-commit is enabled.

          So I think this is what happens:

          1) pstmt1.setAsciiStream(1, in1, in1.available());

          • An internal CLOB object is created for pstmt1

          2) pstmt2.setAsciiStream(1, in2, in2.available());

          • An internal CLOB object is created for pstmt2

          3) pstmt1.execute();

          • pstmt1 reads data from its internal CLOB object
          • The connection is auto-committed
          • The commit frees all CLOBs associated with the transaction,
            including pstmt2's internal CLOB, since the two statements are
            created from the same connection object

          4) pstmt2.execute();

          • pstmt2 attempts to read its internal CLOB object, but it's not
            available since the transaction has been committed, and it fails

          However, your program doesn't actually use CLOBs (the column type is
          VARCHAR(9000)), so I'm not sure if the LOB changes in Derby 10.3
          actually intended to break your program. I think it would be a good
          improvement if we could make setAsciiStream (and the other set*Stream
          methods) detect that the parameter is a non-LOB type and materialize
          the stream. Then setAsciiStream() would work more like setString() in
          your program.

          In the example program, there are some simple workarounds:

          a) Change the order of the four lines mentioned above from 1,2,3,4 to
          1,3,2,4. Then there won't be a commit between pstmt2.setAsciiStream()
          and pstmt2.execute().

          or

          b) Turn off auto-commit and issue explicit calls to
          Connection.commit(). Make sure that you don't commit between calling
          pstmt2.setAsciiStream() and pstmt2.execute().

          Show
          Knut Anders Hatlen added a comment - The reason why the execution of the first statement makes the second statement fail, is that the two statements execute in the same connection and auto-commit is enabled. So I think this is what happens: 1) pstmt1.setAsciiStream(1, in1, in1.available()); An internal CLOB object is created for pstmt1 2) pstmt2.setAsciiStream(1, in2, in2.available()); An internal CLOB object is created for pstmt2 3) pstmt1.execute(); pstmt1 reads data from its internal CLOB object The connection is auto-committed The commit frees all CLOBs associated with the transaction, including pstmt2's internal CLOB, since the two statements are created from the same connection object 4) pstmt2.execute(); pstmt2 attempts to read its internal CLOB object, but it's not available since the transaction has been committed, and it fails However, your program doesn't actually use CLOBs (the column type is VARCHAR(9000)), so I'm not sure if the LOB changes in Derby 10.3 actually intended to break your program. I think it would be a good improvement if we could make setAsciiStream (and the other set*Stream methods) detect that the parameter is a non-LOB type and materialize the stream. Then setAsciiStream() would work more like setString() in your program. In the example program, there are some simple workarounds: a) Change the order of the four lines mentioned above from 1,2,3,4 to 1,3,2,4. Then there won't be a commit between pstmt2.setAsciiStream() and pstmt2.execute(). or b) Turn off auto-commit and issue explicit calls to Connection.commit(). Make sure that you don't commit between calling pstmt2.setAsciiStream() and pstmt2.execute().
          Hide
          Simon Meng added a comment -

          Thank you for quick response.
          I read the document you provided. But I don't think my test program is same as the case described in the release notes. In my test case, execute one PreparedStatement caused another PreparedStatement can't work. The two PreparedDtatements use different Streams. They do not reference same Clob/Blob object.

          Thanks,
          Simon

          Show
          Simon Meng added a comment - Thank you for quick response. I read the document you provided. But I don't think my test program is same as the case described in the release notes. In my test case, execute one PreparedStatement caused another PreparedStatement can't work. The two PreparedDtatements use different Streams. They do not reference same Clob/Blob object. Thanks, Simon
          Hide
          Knut Anders Hatlen added a comment -

          You may have come across one of the intentional changes in the large object support in Derby 10.3. For more details, see this section of the release notes for Derby 10.3.1.4: http://svn.apache.org/viewvc/db/derby/code/tags/10.3.1.4/RELEASE-NOTES.html?view=co#Note%20for%20DERBY-208

          Show
          Knut Anders Hatlen added a comment - You may have come across one of the intentional changes in the large object support in Derby 10.3. For more details, see this section of the release notes for Derby 10.3.1.4: http://svn.apache.org/viewvc/db/derby/code/tags/10.3.1.4/RELEASE-NOTES.html?view=co#Note%20for%20DERBY-208

            People

            • Assignee:
              Kathey Marsden
              Reporter:
              Simon Meng
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development