Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <239006815.1140647860487.JavaMail.jira@ajax.apache.org>
Date: Wed, 22 Feb 2006 23:37:40 +0100 (CET)
From: "Daniel John Debrunner (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (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
In-Reply-To: <731344299.1139857016696.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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

Daniel John Debrunner updated DERBY-966:
----------------------------------------

    Attachment: derby966_diff.txt
                derby966_status.txt

1) Change DRDAStatement to use the EngineConnection.prepareStatement() method that has a holdability parameter. This ensures prepares on a connection that was obtained from a XADataSource (or a ConnectionPoolDataSource) do not lose the holdability requested by the application.

2) Change the client's state of holdability to match the embedded in that a Connection's holdability is set to close cursors on commit when it has an active global XA transaction.

Passes derbyall, gives the correct behaviour when running the derby966 tests in XATest against the client. This test needs a little more work
and a couple of client XA fixes before running in derbynetclientmats can be enabled.

Patch addresses this issue completely.

> 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
>  Attachments: derby966_diff.txt, derby966_status.txt
>
> 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