[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

• 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
• Assignee: Daniel John Debrunner
• Reporter: Katherine Marsden
• Created: 14/Feb/06 03:56
• Updated: 25/Apr/06 03:54
• Resolved: 25/Apr/06 03:54

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

}

Issue Links

• is blocked by: DERBY-434 add jdbcapi/xaMorph.sql jdbcapi/xaJNDI jdbcapi/xaAnotherTest.sql to client xa testing.
• is blocked by: DERBY-435 jdbcapi/checkDataSource.java and jdbapi/checkDataSource30.java to tests should be changed or extended to test client DataSources
• relates to: DERBY-1005 getHoldability does not return CLOSE_CURSORS_AT_COMMIT in a global transaction