Derby
  1. Derby
  2. DERBY-6053

Client should use a prepared statement rather than regular statement for Connection.setTransactionIsolation

    Details

    • Issue & fix info:
      High Value Fix, Newcomer, Repro attached

      Description

      o.a.d.client.am.Connection setTransactionIsolation() uses a Statement which it builds up each time for setTransactionIsolation() is called.

      private Statement setTransactionIsolationStmt = null;
      ...
      setTransactionIsolationStmt =
      createStatementX(java.sql.ResultSet.TYPE_FORWARD_ONLY,
      java.sql.ResultSet.CONCUR_READ_ONLY,
      holdability());
      ....
      private void setTransactionIsolationX(int level)
      ...
      setTransactionIsolationStmt.executeUpdate(
      "SET CURRENT ISOLATION = " + levelString);

      It would be better for performance and also for avoid possible garbage collection issues, to have a single prepared statement with a parameter marker.

      The program below shows repeated calls to setTransactionIsolation.

      import java.sql.*;
      import java.net.*;
      import java.io.*;
      import org.apache.derby.drda.NetworkServerControl;

      /**

      • Client template starts its own NetworkServer and runs some SQL against it.
      • The SQL or JDBC API calls can be modified to reproduce issues
      • */public class SetTransactionIsolation {
        public static Statement s;

      public static void main(String[] args) throws Exception {
      try {
      // Load the driver. Not needed for network server.

      Class.forName("org.apache.derby.jdbc.ClientDriver");
      // Start Network Server
      startNetworkServer();
      // If connecting to a customer database. Change the URL
      Connection conn = DriverManager
      .getConnection("jdbc:derby://localhost:1527/wombat;create=true");
      // clean up from a previous run
      s = conn.createStatement();
      try

      { s.executeUpdate("DROP TABLE T"); }

      catch (SQLException se)

      { if (!se.getSQLState().equals("42Y55")) throw se; }

      for (int i = 0; i < 50000; i++)

      { conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); }

      // rs.close();
      // ps.close();
      runtimeInfo();
      conn.close();
      // Shutdown the server
      shutdownServer();
      } catch (SQLException se) {
      while (se != null)

      { System.out.println("SQLState=" + se.getSQLState() + se.getMessage()); se.printStackTrace(); se = se.getNextException(); }

      }
      }

      /**

      • starts the Network server
      • */
        public static void startNetworkServer() throws SQLException {
        Exception failException = null;
        try {

      NetworkServerControl networkServer = new NetworkServerControl(
      InetAddress.getByName("localhost"), 1527);

      networkServer.start(new PrintWriter(System.out));

      // Wait for the network server to start
      boolean started = false;
      int retries = 10; // Max retries = max seconds to wait

      while (!started && retries > 0) {
      try

      { // Sleep 1 second and then ping the network server Thread.sleep(1000); networkServer.ping(); // If ping does not throw an exception the server has // started started = true; }

      catch (Exception e)

      { retries--; failException = e; }

      }

      // Check if we got a reply on ping
      if (!started)

      { throw failException; }

      } catch (Exception e)

      { SQLException se = new SQLException("Error starting network server"); se.initCause(failException); throw se; }

      }

      public static void shutdownServer() throws Exception

      { NetworkServerControl networkServer = new NetworkServerControl( InetAddress.getByName("localhost"), 1527); networkServer.shutdown(); }

      public static void runtimeInfo() throws Exception

      { NetworkServerControl networkServer = new NetworkServerControl( InetAddress.getByName("localhost"), 1527); System.out.println(networkServer.getRuntimeInfo()); }

      }

      1. derby-6053-01-aa-lintCheck.diff
        0.4 kB
        Rick Hillegas
      2. DERBY6053_patch1_diff.txt
        5 kB
        Mamta A. Satoor

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Mamta A. Satoor added a comment -

          Backported changes to 10.8 codeline with revision 1451397.

          Show
          Mamta A. Satoor added a comment - Backported changes to 10.8 codeline with revision 1451397.
          Hide
          Mamta A. Satoor added a comment -

          reopening the issue for backporting

          Show
          Mamta A. Satoor added a comment - reopening the issue for backporting
          Hide
          Mamta A. Satoor added a comment -

          Backported changes to 10.9 codeline with revision 1451023.

          Show
          Mamta A. Satoor added a comment - Backported changes to 10.9 codeline with revision 1451023.
          Hide
          Knut Anders Hatlen added a comment -

          10.9 is supposed to work on Java 1.4, so I don't think we should change the compiler level on that branch. (FailedProperties40 is only used on Java 6 and higher, so it's OK for that class, though.) Declaring the HashMap without the type parameters should be fine.

          Show
          Knut Anders Hatlen added a comment - 10.9 is supposed to work on Java 1.4, so I don't think we should change the compiler level on that branch. (FailedProperties40 is only used on Java 6 and higher, so it's OK for that class, though.) Declaring the HashMap without the type parameters should be fine.
          Hide
          Mamta A. Satoor added a comment -

          I am working on backporting the 2 commits that went in for this jira into 10.9 codelines. But the declaration of isolationLevelPreparedStmts includes type parameters as shown below
          final private HashMap<String, PreparedStatement>
          isolationLevelPreparedStmts =
          new HashMap<String, PreparedStatement>();

          This code when compiled in 10.9 gives following compile time error
          [javac] C:\p4clients\svn10.9\client1\10.9\java\client\org\apache\derby\client\am\Connection.java:91: generics are not supported in -source 1.4
          [javac] (use -source 5 or higher to enable generics)
          [javac] final private HashMap<String, PreparedStatement>
          [javac] ^

          I know there are other clases in 10.9 that include type parameters during HashMap declaration eg org.apache.derby.iapi.jdbc.FailedProperties40 but build.xml has been modified to compile them with compilerLevel16 rather than 1.4. Is it ok to solve the compile time issue with Connection.java by having it compile with compilerLevel16 or should I use the old style HashMap declaration(without the type parameters) which is found scattered around in 10.9 codeline? Thanks for the help.

          Show
          Mamta A. Satoor added a comment - I am working on backporting the 2 commits that went in for this jira into 10.9 codelines. But the declaration of isolationLevelPreparedStmts includes type parameters as shown below final private HashMap<String, PreparedStatement> isolationLevelPreparedStmts = new HashMap<String, PreparedStatement>(); This code when compiled in 10.9 gives following compile time error [javac] C:\p4clients\svn10.9\client1\10.9\java\client\org\apache\derby\client\am\Connection.java:91: generics are not supported in -source 1.4 [javac] (use -source 5 or higher to enable generics) [javac] final private HashMap<String, PreparedStatement> [javac] ^ I know there are other clases in 10.9 that include type parameters during HashMap declaration eg org.apache.derby.iapi.jdbc.FailedProperties40 but build.xml has been modified to compile them with compilerLevel16 rather than 1.4. Is it ok to solve the compile time issue with Connection.java by having it compile with compilerLevel16 or should I use the old style HashMap declaration(without the type parameters) which is found scattered around in 10.9 codeline? Thanks for the help.
          Hide
          Mamta A. Satoor added a comment -

          Committed changes recommended by Knut with revision 1443599.

          Show
          Mamta A. Satoor added a comment - Committed changes recommended by Knut with revision 1443599.
          Hide
          Mamta A. Satoor added a comment -

          Knut and Rick, thanks for your feedbacks. I will take care of the comments posted by Knut. Rick, thanks for adding -lint directive, it will be good to see the error from the compiler.

          Show
          Mamta A. Satoor added a comment - Knut and Rick, thanks for your feedbacks. I will take care of the comments posted by Knut. Rick, thanks for adding -lint directive, it will be good to see the error from the compiler.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-6053-01-aa-lintCheck.diff. This patch adds a -lint directive to the compilation target which compiles Connection.java. Committed at subversion revision 1442732.

          After applying this patch, when you do a clean build, you will see more detail about the compilation warning introduced by revision 1442474. I agree with Knut's analysis of the warning: the coding bug here will give rise to ClassCastExceptions in production.

          Touches the following file:

          M java/client/build.xml

          Show
          Rick Hillegas added a comment - Attaching derby-6053-01-aa-lintCheck.diff. This patch adds a -lint directive to the compilation target which compiles Connection.java. Committed at subversion revision 1442732. After applying this patch, when you do a clean build, you will see more detail about the compilation warning introduced by revision 1442474. I agree with Knut's analysis of the warning: the coding bug here will give rise to ClassCastExceptions in production. Touches the following file: M java/client/build.xml
          Hide
          Knut Anders Hatlen added a comment -

          > The subsequent compile doesn't show this error anymore. I have a
          > feeling this has to do with using Hashtable and Iterator objects,
          > but not 100% yet. Am I correct about that assumption? If so, can
          > this compile time error be ignored since the subsequent compile
          > doesn't give that error again?

          You probably didn't see it again because the second time the class
          file was already built, so it didn't get recompiled (incremental
          build). I suppose you'd see it again if you ran "ant clean" or "ant
          clobber" first and then built the code.

          Show
          Knut Anders Hatlen added a comment - > The subsequent compile doesn't show this error anymore. I have a > feeling this has to do with using Hashtable and Iterator objects, > but not 100% yet. Am I correct about that assumption? If so, can > this compile time error be ignored since the subsequent compile > doesn't give that error again? You probably didn't see it again because the second time the class file was already built, so it didn't get recompiled (incremental build). I suppose you'd see it again if you ran "ant clean" or "ant clobber" first and then built the code.
          Hide
          Knut Anders Hatlen added a comment -

          Some post-commit comments:

          • As far as I can see, all accesses to isolationLevelPreparedStmts are synchronized on the Connection instance, so maybe it could be an unsynchronized HashMap instead of a synchronized Hashtable?
          • isolationLevelPreparedStmts is initialized to a non-null value and never changed, so checking that it's not null before accessing it shouldn't be necessary. (Maybe also declare the field as final to make it explicit that it is never changed from the initial non-null value.)
          • I think the declaration of isolationLevelPreparedStmts should include type parameters, so that it's easier to access the values stored in it:

          HashMap<String,PreparedStatement> isolationLevelPreparedStmts = new HashMap<String,PreparedStatement>();

          • There's a compiler warning when I build the code. This line looks wrong, and I suppose that's what the compiler is warning us about:

          for (Iterator<PreparedStatement> it = isolationLevelPreparedStmts.keySet().iterator();

          The keys in the hash table are strings, not prepared statements. So it should say Iterator<String>. (If the type parameters had been included in the declaration of the field, the compiler would have known it was wrong, and it would have produced an error instead of a warning.)

          However, it's much simpler to use a for-each loop, so I suggest that the loop is changed to something like this:

          for (PreparedStatement ps : isolationLevelPreparedStmts.values()) {
          try

          { ps.close(); }

          catch (SQLException se)

          { // ... }

          }

          • When the declaration of isolationLevelPreparedStmts is changed to include the type parameters <String,PreparedStatement>, it is no longer necessary to cast the value returned from isolationLevelPreparedStmts.get(levelString), as the compiler already knows it returns a PreparedStatement.
          Show
          Knut Anders Hatlen added a comment - Some post-commit comments: As far as I can see, all accesses to isolationLevelPreparedStmts are synchronized on the Connection instance, so maybe it could be an unsynchronized HashMap instead of a synchronized Hashtable? isolationLevelPreparedStmts is initialized to a non-null value and never changed, so checking that it's not null before accessing it shouldn't be necessary. (Maybe also declare the field as final to make it explicit that it is never changed from the initial non-null value.) I think the declaration of isolationLevelPreparedStmts should include type parameters, so that it's easier to access the values stored in it: HashMap<String,PreparedStatement> isolationLevelPreparedStmts = new HashMap<String,PreparedStatement>(); There's a compiler warning when I build the code. This line looks wrong, and I suppose that's what the compiler is warning us about: for (Iterator<PreparedStatement> it = isolationLevelPreparedStmts.keySet().iterator(); The keys in the hash table are strings, not prepared statements. So it should say Iterator<String>. (If the type parameters had been included in the declaration of the field, the compiler would have known it was wrong, and it would have produced an error instead of a warning.) However, it's much simpler to use a for-each loop, so I suggest that the loop is changed to something like this: for (PreparedStatement ps : isolationLevelPreparedStmts.values()) { try { ps.close(); } catch (SQLException se) { // ... } } When the declaration of isolationLevelPreparedStmts is changed to include the type parameters <String,PreparedStatement>, it is no longer necessary to cast the value returned from isolationLevelPreparedStmts.get(levelString), as the compiler already knows it returns a PreparedStatement.
          Hide
          Mamta A. Satoor added a comment -

          I think the backport of the changes should be fine(although will it be an issue to have different client versions to a server using different ways of changing the isolation level? ie clients without the changes from this jira will use regular statements whereas clients with the changes from this jira will use prepared statements)

          Show
          Mamta A. Satoor added a comment - I think the backport of the changes should be fine(although will it be an issue to have different client versions to a server using different ways of changing the isolation level? ie clients without the changes from this jira will use regular statements whereas clients with the changes from this jira will use prepared statements)
          Hide
          Mamta A. Satoor added a comment -

          Committed changes into trunk(10.10.0.0 alpha) with revision 1442474.

          Show
          Mamta A. Satoor added a comment - Committed changes into trunk(10.10.0.0 alpha) with revision 1442474.
          Hide
          Mamta A. Satoor added a comment -

          Hi Kathey, thanks for reviewing the patch. I will take care of both your comments. As there are no further comments from anyone else, I will commit the fix for this jira after making the changes suggested by you.

          Show
          Mamta A. Satoor added a comment - Hi Kathey, thanks for reviewing the patch. I will take care of both your comments. As there are no further comments from anyone else, I will commit the fix for this jira after making the changes suggested by you.
          Hide
          Kathey Marsden added a comment -

          Thanks Mamta for the patch.

          Do you get the same warning with tis patch and the more strongly typed iterator?

          The patch looks good to me. A couple minor points.
          Since this catch block might be for more than one prepared statement close, it should probably only set accumulateExceptions to se if accumulatedExceptions is null, otherwise do accumulatedExceptions.setNextException(se)

          } catch (SQLException se)

          { accumulatedExceptions = se; }

          There are tabs mixed in with the spaces even on lines that have otherwise not changed. The client code should be all space indentation.

          Show
          Kathey Marsden added a comment - Thanks Mamta for the patch. Do you get the same warning with tis patch and the more strongly typed iterator? The patch looks good to me. A couple minor points. Since this catch block might be for more than one prepared statement close, it should probably only set accumulateExceptions to se if accumulatedExceptions is null, otherwise do accumulatedExceptions.setNextException(se) } catch (SQLException se) { accumulatedExceptions = se; } There are tabs mixed in with the spaces even on lines that have otherwise not changed. The client code should be all space indentation.
          Hide
          Mamta A. Satoor added a comment -

          I have fired the derbyall and junit tests with the attached patch.

          Show
          Mamta A. Satoor added a comment - I have fired the derbyall and junit tests with the attached patch.
          Hide
          Mamta A. Satoor added a comment -

          Here is the correct attachement

          Show
          Mamta A. Satoor added a comment - Here is the correct attachement
          Hide
          Mamta A. Satoor added a comment - - edited

          I have made the changes to use PreparedStatement vs Statement(attached a patch DERBY6053_patch1_diff.txt for it) so that we do not compile the statements for isolation level change every single time. When I compile this code though, I get following error from ant all
          [javac] Note: C:\p4clients\svnmain\client3\trunk\java\client\org\apache\derby\client\am\Connection.java uses unchecked or unsafe operations.
          [javac] Note: Recompile with -Xlint:unchecked for details.
          The subsequent compile doesn't show this error anymore. I have a feeling this has to do with using Hashtable and Iterator objects, but not 100% yet. Am I correct about that assumption? If so, can this compile time error be ignored since the subsequent compile doesn't give that error again? Thanks

          Show
          Mamta A. Satoor added a comment - - edited I have made the changes to use PreparedStatement vs Statement(attached a patch DERBY6053_patch1_diff.txt for it) so that we do not compile the statements for isolation level change every single time. When I compile this code though, I get following error from ant all [javac] Note: C:\p4clients\svnmain\client3\trunk\java\client\org\apache\derby\client\am\Connection.java uses unchecked or unsafe operations. [javac] Note: Recompile with -Xlint:unchecked for details. The subsequent compile doesn't show this error anymore. I have a feeling this has to do with using Hashtable and Iterator objects, but not 100% yet. Am I correct about that assumption? If so, can this compile time error be ignored since the subsequent compile doesn't give that error again? Thanks
          Hide
          Kathey Marsden added a comment -

          Thank you Mamta for looking at this,

          I do not think that comment is true about statements not staying prepared across commits.
          If I prepare a statement and execute it twice with a commit in between from client, I do not see a reprepare in the derby.log . I think maybe the openOnServer_ property may be a relic of some place or time in the original code base when that was true.

          Thu Jan 31 10:52:46 PST 2013 Thread[DRDAConnThread_2,5,derby.daemons] (XID = 172), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921

          {2}), End compiling prepared statement: INSERT INTO T VALUES(?,?) :End prepared statement
          Thu Jan 31 10:52:46 PST 2013 Thread[DRDAConnThread_2,5,derby.daemons] (XID = 172), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921{2}

          ), Executing prepared statement: INSERT INTO T VALUES(?,?) :End prepared statement with 2 parameters begin parameter #1: 1 :end parameter begin parameter #2: CLOB(5) :end parameter
          Thu Jan 31 10:52:46 PST 2013 Thread[DRDAConnThread_2,5,derby.daemons] (XID = 172), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921

          {2}), Committing
          Thu Jan 31 10:52:46 PST 2013 Thread[DRDAConnThread_2,5,derby.daemons] (XID = 174), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921{2}

          ), Executing prepared statement: INSERT INTO T VALUES(?,?) :End prepared statement with 2 parameters begin parameter #1: 2 :end parameter begin parameter #2: CLOB(7) :end parameter <=== Just execute again after commit.

          At least in this case, I think it can be removed from the condition. It would be good to file an issue to investigate the openOnServer field in general.

          One aside comment on this change is that for our prepared statements it might be nice to have a HashTable keyed on the DERBY_TRANSACTION_... values instead of separate fields for each one and repeat code.

          Show
          Kathey Marsden added a comment - Thank you Mamta for looking at this, I do not think that comment is true about statements not staying prepared across commits. If I prepare a statement and execute it twice with a commit in between from client, I do not see a reprepare in the derby.log . I think maybe the openOnServer_ property may be a relic of some place or time in the original code base when that was true. Thu Jan 31 10:52:46 PST 2013 Thread [DRDAConnThread_2,5,derby.daemons] (XID = 172), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921 {2}), End compiling prepared statement: INSERT INTO T VALUES(?,?) :End prepared statement Thu Jan 31 10:52:46 PST 2013 Thread [DRDAConnThread_2,5,derby.daemons] (XID = 172), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921{2} ), Executing prepared statement: INSERT INTO T VALUES(?,?) :End prepared statement with 2 parameters begin parameter #1: 1 :end parameter begin parameter #2: CLOB(5) :end parameter Thu Jan 31 10:52:46 PST 2013 Thread [DRDAConnThread_2,5,derby.daemons] (XID = 172), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921 {2}), Committing Thu Jan 31 10:52:46 PST 2013 Thread [DRDAConnThread_2,5,derby.daemons] (XID = 174), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = .-4398045151445635921{2} ), Executing prepared statement: INSERT INTO T VALUES(?,?) :End prepared statement with 2 parameters begin parameter #1: 2 :end parameter begin parameter #2: CLOB(7) :end parameter <=== Just execute again after commit. At least in this case, I think it can be removed from the condition. It would be good to file an issue to investigate the openOnServer field in general. One aside comment on this change is that for our prepared statements it might be nice to have a HashTable keyed on the DERBY_TRANSACTION_... values instead of separate fields for each one and repeat code.
          Hide
          Mamta A. Satoor added a comment -

          I worked on the changes to use PreparedStatement rather than Statement to avoid compiling before every execution of change of isolation level. Following shows part of the code changes in o.a.d.client.am.Connection.setTransactionIsolationX
          if (setRepeatableReadTransactionIsolationStmt == null ||
          !(setRepeatableReadTransactionIsolationStmt.openOnClient_ &&
          setRepeatableReadTransactionIsolationStmt.openOnServer_))

          { System.out.println("in new changes"); setRepeatableReadTransactionIsolationStmt = prepareStatementX( "SET CURRENT ISOLATION = " + DERBY_TRANSACTION_REPEATABLE_READ, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY, holdability(), java.sql.Statement.NO_GENERATED_KEYS, null, null); }

          But I am finding that setRepeatableReadTransactionIsolationStmt.openOnServer_ check always returns false thus making the if statement always true after the first time set isolation level is changed to repeatable read. I had thought that after it has been prepared the first time around, the subsequent times will make the if statement return false. I looked at o.a.d.client.am.Statement:openOnServer_ and see following comment for it
          // This means a DERBY server-side section for this statement is in the prepared state.
          // A client-side jdbc statement may remain open across commits (openOnClient=true),
          // but the server-side DERBY section moves to an unprepared state (openOnServer=false) across commits,
          // requiring an implicit re-prepare "under the covers" by the driver.
          // Unprepared jdbc query statements still have prepared sections on the server.
          // This openOnServer_ only has implications for preparedstatement

          Based on the comment above, I changed the sample program provided above by Kathey to have commit false on the connection but I still see us preparing the statement every time isolation level change is requested, ie openOnServer_ is still false. I will look further into this but wanted to give a heads up and see if anyone had any insight in this area of the code. Thanks

          Show
          Mamta A. Satoor added a comment - I worked on the changes to use PreparedStatement rather than Statement to avoid compiling before every execution of change of isolation level. Following shows part of the code changes in o.a.d.client.am.Connection.setTransactionIsolationX if (setRepeatableReadTransactionIsolationStmt == null || !(setRepeatableReadTransactionIsolationStmt.openOnClient_ && setRepeatableReadTransactionIsolationStmt.openOnServer_)) { System.out.println("in new changes"); setRepeatableReadTransactionIsolationStmt = prepareStatementX( "SET CURRENT ISOLATION = " + DERBY_TRANSACTION_REPEATABLE_READ, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY, holdability(), java.sql.Statement.NO_GENERATED_KEYS, null, null); } But I am finding that setRepeatableReadTransactionIsolationStmt.openOnServer_ check always returns false thus making the if statement always true after the first time set isolation level is changed to repeatable read. I had thought that after it has been prepared the first time around, the subsequent times will make the if statement return false. I looked at o.a.d.client.am.Statement:openOnServer_ and see following comment for it // This means a DERBY server-side section for this statement is in the prepared state. // A client-side jdbc statement may remain open across commits (openOnClient=true), // but the server-side DERBY section moves to an unprepared state (openOnServer=false) across commits, // requiring an implicit re-prepare "under the covers" by the driver. // Unprepared jdbc query statements still have prepared sections on the server. // This openOnServer_ only has implications for preparedstatement Based on the comment above, I changed the sample program provided above by Kathey to have commit false on the connection but I still see us preparing the statement every time isolation level change is requested, ie openOnServer_ is still false. I will look further into this but wanted to give a heads up and see if anyone had any insight in this area of the code. Thanks
          Hide
          Mamta A. Satoor added a comment -

          I wrote a simple JDBC program outside to see if parameter can be used to set transaction isolation level and it appears that use of ? inside a prepared statement is not allowed by SET CURRENT ISOLATION sql. I got following syntax error
          $ java org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC
          SQLState=42X01Syntax error: Encountered "?" at line 1, column 25.
          java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 25.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:92)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2400)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:153)
          at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:102)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1725)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1553)
          at org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.main(MamtaJDBC.java:29)
          Caused by: java.sql.SQLException: Syntax error: Encountered "?" at line 1, column 25.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71)
          ... 10 more
          Caused by: ERROR 42X01: Syntax error: Encountered "?" at line 1, column 25.
          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278)
          at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:153)
          at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:357)
          at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:134)
          ... 4 more

          The JDBC program is as follows
          package org.apache.derbyTesting.functionTests.tests.lang;
          import java.sql.*;

          /**

          • Sample JDBC program
          • */public class MamtaJDBC {

          public static void main(String[] args) throws Exception {
          Statement s;
          PreparedStatement ps;
          try {
          // Load the driver.
          Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
          Connection conn = DriverManager
          .getConnection("jdbc:derby:c:/dellater/wombat;create=true");
          // clean up from a previous run
          s = conn.createStatement();
          try

          { s.executeUpdate("DROP TABLE T1"); }

          catch (SQLException se)

          { if (!se.getSQLState().equals("42Y55")) throw se; }


          //Just a sanity check that ? worked for following select statement
          ps = conn.prepareStatement("select * from sys.systables where tablename=?");
          ps.setString(1, "rr");
          ps.execute();
          ps = conn.prepareStatement("SET CURRENT ISOLATION = ?");
          ps.setString(1, "rr");
          ps.execute();
          ps.close();
          s.close();
          conn.close();
          } catch (SQLException se) {
          while (se != null)

          { System.out.println("SQLState=" + se.getSQLState() + se.getMessage()); se.printStackTrace(); se = se.getNextException(); }


          }
          }
          }

          In order to address the performance issues and possible garbage collection issues mentioned by Kathey above, since there are only 4 isolation levels, I was thinking we could create 4 prepared statements in Client side, one for each of the isolation levels and execute one of those PreparedStatement when the user asks for change of isolation levels. This way, we will not be preparing and executing for every isolation level change, instead just execute already prepared PreparedStatemtn.

          Show
          Mamta A. Satoor added a comment - I wrote a simple JDBC program outside to see if parameter can be used to set transaction isolation level and it appears that use of ? inside a prepared statement is not allowed by SET CURRENT ISOLATION sql. I got following syntax error $ java org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC SQLState=42X01Syntax error: Encountered "?" at line 1, column 25. java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 25. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:92) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2400) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:153) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Driver40.java:102) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1725) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1553) at org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.main(MamtaJDBC.java:29) Caused by: java.sql.SQLException: Syntax error: Encountered "?" at line 1, column 25. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71) ... 10 more Caused by: ERROR 42X01: Syntax error: Encountered "?" at line 1, column 25. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:153) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:357) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:134) ... 4 more The JDBC program is as follows package org.apache.derbyTesting.functionTests.tests.lang; import java.sql.*; /** Sample JDBC program */public class MamtaJDBC { public static void main(String[] args) throws Exception { Statement s; PreparedStatement ps; try { // Load the driver. Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); Connection conn = DriverManager .getConnection("jdbc:derby:c:/dellater/wombat;create=true"); // clean up from a previous run s = conn.createStatement(); try { s.executeUpdate("DROP TABLE T1"); } catch (SQLException se) { if (!se.getSQLState().equals("42Y55")) throw se; } //Just a sanity check that ? worked for following select statement ps = conn.prepareStatement("select * from sys.systables where tablename=?"); ps.setString(1, "rr"); ps.execute(); ps = conn.prepareStatement("SET CURRENT ISOLATION = ?"); ps.setString(1, "rr"); ps.execute(); ps.close(); s.close(); conn.close(); } catch (SQLException se) { while (se != null) { System.out.println("SQLState=" + se.getSQLState() + se.getMessage()); se.printStackTrace(); se = se.getNextException(); } } } } In order to address the performance issues and possible garbage collection issues mentioned by Kathey above, since there are only 4 isolation levels, I was thinking we could create 4 prepared statements in Client side, one for each of the isolation levels and execute one of those PreparedStatement when the user asks for change of isolation levels. This way, we will not be preparing and executing for every isolation level change, instead just execute already prepared PreparedStatemtn.

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Kathey Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development