[jira] Commented: (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

["Daniel John Debrunner (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-966?page=comments#action_12367407 ] 

Daniel John Debrunner commented on DERBY-966:
---------------------------------------------

The bug is that the holdability of the connection or statement is being ignored by Derby for client connections
when the connection is obtained from an XA data source.

Statements that are created with holdability  HOLD_CURSORS_OVER_COMMIT  while in the local transaction should throw errors if they are used in the global transaction as holdable cursors are not supported there.

> 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
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-966
>          URL: http://issues.apache.org/jira/browse/DERBY-966
>      Project: Derby
>         Type: Bug
>   Components: Network Client
>     Versions: 10.1.2.3, 10.2.0.0, 10.1.2.2, 10.1.3.0
>     Reporter: Kathey Marsden
>     Assignee: Daniel John Debrunner
>     Priority: Critical

>
> 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;
>     }      
> }

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira