Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-966

creating a preparedStatement outside of a Global tran using a ClientXADatasource will result in an "SqlException: Cannot set holdability" if the statement is used in a Global transaction

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.1.6
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: Network Client
    • Labels:
      None

      Description

      creating a preparedStatement outside of a Global tran using
      an xa datasource will result in an exception if the statement
      is used in a Global (i.e. xa transaction).

      DERBY-346 and DERBY-8 may be relevant to this issue.

      User noted
      1) setting the holdability on the connection to
      CLOSE_CURSORS_AT_COMMIT doesn't seem to be taken affect, since

      the problem is observed to happen even if I set the the
      holdability to CLOSE_CURSORS_AT_COMMIT before creating the
      statement. (maybe another bug)

      2) setting the holdability to close_cursor_at_commit on the PS
      when creating it, doesn't seem to be affecting the outcome,
      this, not sure its even honored (maybe another bug)

      Test case is below:

      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.Statement;

      import javax.sql.XAConnection;
      import javax.transaction.xa.XAException;
      import javax.transaction.xa.XAResource;
      import javax.transaction.xa.Xid;

      import com.ibm.db2.jcc.DB2Xid;

      class CursorHoldProblem
      {

      public static PreparedStatement pstmt = null;
      public static void main (String args [])throws Exception {
      org.apache.derby.jdbc.ClientXADataSource ds = new
      org.apache.derby.jdbc.ClientXADataSource();

      System.out.println("getting connection");
      ds.setDatabaseName("sample");
      //ds.setTraceFile("trace.out");
      ds.setConnectionAttributes("create=true");
      conn1 = ds.getConnection();

      System.out.println(conn1.getMetaData().getDatabaseProductVersion
      ());

      PreparedStatement ps1 = null;
      try

      { System.out.println("creating table"); ps1 = conn1.prepareStatement("CREATE TABLE TAB1(COL1 INT NOT NULL)"); ps1.executeUpdate(); System.out.println("done creating table"); conn1.commit (); }

      catch (SQLException x)

      { System.out.println ("table already exists"); conn1.commit(); }

      XAConnection pc1 = ds.getXAConnection();
      XAResource xar1 = pc1.getXAResource();
      Xid xid1 = createXid(11);
      Connection conn = pc1.getConnection();

      System.out.println("get Holidability returning: " +
      conn.getHoldability());
      conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
      //==> setting this has no affect

      doSelect(conn, 23);

      xar1.start(xid1, XAResource.TMNOFLAGS);

      doSomeWork1(conn, 66);
      doSelect(conn, 50);

      xar1.end(xid1, XAResource.TMSUCCESS);

      int prp1 = xar1.prepare(xid1);
      System.out.println("prp1 is: " + prp1);

      if (prp1 == XAResource.XA_OK)
      xar1.commit(xid1, false);
      }

      private static void doSomeWork1(Connection conn, int
      deptno) throws SQLException

      { Statement stmt = conn.createStatement(); int cnt = stmt.executeUpdate("INSERT INTO tab1 VALUES (" + deptno + ")"); System.out.println("No of rows Affected " + cnt); stmt.close(); stmt = null; }

      private static void doSelect(Connection conn, int deptno)
      throws SQLException
      {

      if (pstmt == null)
      pstmt = conn.prepareStatement("select * from tab1");
      ResultSet rset1 = pstmt.executeQuery();
      while (rset1.next())

      { System.out.println("==>: " + rset1.getString(1)); break; }

      }

      static Xid createXid(int bids) throws XAException

      { byte[] gid = new byte[1]; gid[0] = (byte) 9; byte[] bid = new byte[1]; bid[0] = (byte) bids; byte[] gtrid = new byte[64]; byte[] bqual = new byte[64]; System.arraycopy(gid, 0, gtrid, 0, 1); System.arraycopy(bid, 0, bqual, 0, 1); Xid xid = new DB2Xid(0x1234, gtrid, bqual); return xid; }


      }

        Attachments

        1. derby966_diff.txt
          21 kB
          Daniel John Debrunner
        2. derby966_status.txt
          0.5 kB
          Daniel John Debrunner

          Issue Links

            Activity

              People

              • Assignee:
                djd Daniel John Debrunner
                Reporter:
                kmarsden Katherine Marsden
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: