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

[Kathey Marsden (JIRA)]

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