Sorry I missed to attach the patch with my previous mail. Just want to add that I had run the new xaSimplePositive.sql with embedded and client driver. In both cases, I did not get error when the holdability of a statement inside a procedure was set to HOLD_CURSORS_OVER_COMMIT in a xa transaction. I tried this before and after applying my patch for derby-498. The behaviour is same.
Thanks, Deepa
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java (revision 320871)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java (working copy)
@@ -22,6 +22,7 @@
import java.sql.CallableStatement;
import java.sql.Connection;
+import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
@@ -50,6 +51,8 @@
//set autocommit to off after creating table and inserting data
conn.setAutoCommit(false);
+ testHoldability(conn,ResultSet.HOLD_CURSORS_OVER_COMMIT);
+ testHoldability(conn,ResultSet.CLOSE_CURSORS_AT_COMMIT);
testHoldCursorOnMultiTableQuery(conn);
testIsolationLevelChange(conn);
@@ -74,6 +77,12 @@
stmt.executeUpdate("INSERT INTO T2 VALUES(1,1)");
stmt.executeUpdate("INSERT INTO T2 VALUES(1,2)");
stmt.executeUpdate("INSERT INTO T2 VALUES(1,3)");
+ stmt.execute("create table testtable1 (id integer, vc varchar(100))");
+ stmt.execute("insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two')");
+ stmt.execute("create table testtable2 (id integer, vc varchar(100))");
+ stmt.execute("insert into testtable2 values (21, 'testtable2-one'), (22, 'testtable2-two')");
+ stmt.execute("create procedure MYPROC() language java parameter style java external name " +
+ "'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testProc' result sets 2");
System.out.println("done creating table and inserting data.");
stmt.close();
@@ -190,4 +199,121 @@
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
}
+ //set connection holdability and test holdability of statements inside and outside procedures
+ //test that holdability of statements always overrides holdability of connection
+ private static void testHoldability(Connection conn,int holdability) throws SQLException{
+
+ conn.setHoldability(holdability);
+
+ switch(holdability){
+ case ResultSet.HOLD_CURSORS_OVER_COMMIT:
+ System.out.println("\ntestHoldability with HOLD_CURSORS_OVER_COMMIT\n");
+ break;
+ case ResultSet.CLOSE_CURSORS_AT_COMMIT:
+ System.out.println("\ntestHoldability with CLOSE_CURSORS_AT_COMMIT\n");
+ break;
+ }
+
+ testStatements(conn);
+ testStatementsInProcedure(conn);
+ }
+
+ //test holdability of statements outside procedures
+ private static void testStatements(Connection conn) throws SQLException{
+ System.out.println("\ntestStatements()\n");
+
+ //HOLD_CURSORS_OVER_COMMIT
+ Statement st1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
+ ResultSet rs1 = st1.executeQuery("select * from testtable1");
+ checkResultSet(rs1, "before");
+ conn.commit();
+ checkResultSet(rs1, "after");
+ st1.close();
+
+ //CLOSE_CURSORS_AT_COMMIT
+ Statement st2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
+ ResultSet rs2 = st2.executeQuery("select * from testtable2");
+ checkResultSet(rs2, "before");
+ conn.commit();
+ checkResultSet(rs2, "after");
+ st2.close();
+ }
+
+ //test holdability of statements in procedures
+ private static void testStatementsInProcedure(Connection conn) throws SQLException{
+ System.out.println("\ntestStatementsInProcedure()\n");
+
+ CallableStatement cs1 = conn.prepareCall("call MYPROC()");
+ cs1.execute();
+ do{
+ checkResultSet(cs1.getResultSet(), "before");
+ }while(cs1.getMoreResults());
+
+ CallableStatement cs2 = conn.prepareCall("call MYPROC()");
+ cs2.execute();
+ conn.commit();
+ do{
+ checkResultSet(cs2.getResultSet(),"after");
+ }while(cs2.getMoreResults());
+
+ cs1.close();
+ cs2.close();
+ }
+
+ //check if resultset is accessible
+ private static void checkResultSet(ResultSet rs, String beforeOrAfter) throws SQLException{
+ System.out.println("checkResultSet "+ beforeOrAfter + " commit");
+ try{
+ if(rs != null){
+ rs.next();
+ System.out.println(rs.getString(1) + ", " + rs.getString(2));
+ }
+ else{
+ System.out.println("EXPECTED:ResultSet is null");
+ }
+ } catch(SQLException se){
+ System.out.println("EXPECTED EXCEPTION:"+se.getMessage());
+ }
+ }
+
+ //Java method for stored procedure
+ public static void testProc(ResultSet[] rs1, ResultSet[] rs2) throws Exception
+ {
+ Connection conn = DriverManager.getConnection("jdbc:default:connection");
+
+ //HOLD_CURSORS_OVER_COMMIT
+ Statement st1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
+ rs1[0] = st1.executeQuery("select * from testtable1");
+
+ //CLOSE_CURSORS_AT_COMMIT
+ Statement st2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
+ rs2[0] = st2.executeQuery("select * from testtable2");
+
+ }
+
+ //Java method for stored procedure with only HOLD_CURSORS_OVER_COMMIT
+ public static void testHoldCursorsProc(ResultSet[] rs) throws Exception
+ {
+ Connection conn = DriverManager.getConnection("jdbc:default:connection");
+
+ //HOLD_CURSORS_OVER_COMMIT
+ Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
+ rs[0] = st.executeQuery("select * from testtable1");
+ }
+
+ //Java method for stored procedure with only CLOSE_CURSORS_OVER_COMMIT
+ public static void testCloseCursorsProc(ResultSet[] rs) throws Exception
+ {
+ Connection conn = DriverManager.getConnection("jdbc:default:connection");
+
+ //CLOSE_CURSORS_AT_COMMIT
+ Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
+ ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
+ rs[0] = st.executeQuery("select * from testtable1");
+ }
}
Index: java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql (revision 320871)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql (working copy)
@@ -138,3 +138,48 @@
xa_end xa_success 5;
xa_prepare 5;
xa_commit xa_2Phase 5;
+
+---------------------------------------------
+-- Test procedure with server-side JDBC
+---------------------------------------------
+--- local transaction
+create table t1(i int not null primary key, b char(15));
+insert into t1 values (1,'one'), (2, 'two'), (3,'three');
+create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
+call DRS(1);
+drop table t1;
+drop procedure DRS;
+commit;
+--- global transaction
+xa_start xa_noflags 6;
+create table t1(i int not null primary key, b char(15));
+insert into t1 values (1,'one'), (2, 'two'), (3,'three');
+create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
+call DRS(1);
+xa_end xa_success 6;
+xa_commit xa_1Phase 6;
+
+---------------------------------------------
+-- Test procedure with server-side JDBC with statement holdability set in procedure
+---------------------------------------------
+--- local transaction
+create table testtable1 (id integer, vc varchar(100));
+insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two');
+create procedure HOLDCURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testHoldCursorsProc' result sets 1;
+call HOLDCURSORPROC();
+create procedure CLOSECURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testCloseCursorsProc' result sets 1;
+call CLOSECURSORPROC();
+drop table testtable1;
+drop procedure HOLDCURSORPROC;
+drop procedure CLOSECURSORPROC;
+commit;
+--- global transaction
+xa_start xa_noflags 7;
+create table testtable1 (id integer, vc varchar(100));
+insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two');
+create procedure HOLDCURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testHoldCursorsProc' result sets 1;
+call HOLDCURSORPROC();
+create procedure CLOSECURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testCloseCursorsProc' result sets 1;
+call CLOSECURSORPROC();
+xa_end xa_success 7;
+xa_commit xa_1Phase 7;
\ No newline at end of file
Index: java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out (revision 320871)
+++ java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out (working copy)
@@ -192,4 +192,90 @@
ij(XA)> xa_end xa_success 5;
ij(XA)> xa_prepare 5;
ij(XA)> xa_commit xa_2Phase 5;
+ij(XA)> ---------------------------------------------
+-- Test procedure with server-side JDBC
+---------------------------------------------
+--- local transaction
+create table t1(i int not null primary key, b char(15));
+0 rows inserted/updated/deleted
+ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three');
+3 rows inserted/updated/deleted
+ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
+0 rows inserted/updated/deleted
+ij(XA)> call DRS(1);
+selectRows - 1 arg - 1 rs
+I |B
+---------------------------
+1 |one
+ij(XA)> drop table t1;
+0 rows inserted/updated/deleted
+ij(XA)> drop procedure DRS;
+0 rows inserted/updated/deleted
+ij(XA)> commit;
+ij(XA)> --- global transaction
+xa_start xa_noflags 6;
+ij(XA)> create table t1(i int not null primary key, b char(15));
+0 rows inserted/updated/deleted
+ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three');
+3 rows inserted/updated/deleted
+ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
+0 rows inserted/updated/deleted
+ij(XA)> call DRS(1);
+selectRows - 1 arg - 1 rs
+I |B
+---------------------------
+1 |one
+ij(XA)> xa_end xa_success 6;
+ij(XA)> xa_commit xa_1Phase 6;
+ij(XA)> ---------------------------------------------
+-- Test procedure with server-side JDBC with statement holdability set in procedure
+---------------------------------------------
+--- local transaction
+create table testtable1 (id integer, vc varchar(100));
+0 rows inserted/updated/deleted
+ij(XA)> insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two');
+2 rows inserted/updated/deleted
+ij(XA)> create procedure HOLDCURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testHoldCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call HOLDCURSORPROC();
+ID |VC
+----------------------------------------------------------------------------------------------------------------
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> create procedure CLOSECURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testCloseCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call CLOSECURSORPROC();
+ID |VC
+----------------------------------------------------------------------------------------------------------------
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> drop table testtable1;
+0 rows inserted/updated/deleted
+ij(XA)> drop procedure HOLDCURSORPROC;
+0 rows inserted/updated/deleted
+ij(XA)> drop procedure CLOSECURSORPROC;
+0 rows inserted/updated/deleted
+ij(XA)> commit;
+ij(XA)> --- global transaction
+xa_start xa_noflags 7;
+ij(XA)> create table testtable1 (id integer, vc varchar(100));
+0 rows inserted/updated/deleted
+ij(XA)> insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two');
+2 rows inserted/updated/deleted
+ij(XA)> create procedure HOLDCURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testHoldCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call HOLDCURSORPROC();
+ID |VC
+----------------------------------------------------------------------------------------------------------------
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> create procedure CLOSECURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testCloseCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call CLOSECURSORPROC();
+ID |VC
+----------------------------------------------------------------------------------------------------------------
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> xa_end xa_success 7;
+ij(XA)> xa_commit xa_1Phase 7;
ij(XA)>
Index: java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out (revision 320871)
+++ java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out (working copy)
@@ -192,4 +192,88 @@
ij(XA)> xa_end xa_success 5;
ij(XA)> xa_prepare 5;
ij(XA)> xa_commit xa_2Phase 5;
-ij(XA)>
+ij(XA)> ---------------------------------------------
+----- Test procedure with server-side JDBC
+-----
+----- local transaction
+create table t1(i int not null primary key, b char(15));
+0 rows inserted/updated/deleted
+ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three');
+3 rows inserted/updated/deleted
+ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
+0 rows inserted/updated/deleted
+ij(XA)> call DRS(1);
+I |B
+-----
+1 |one
+ij(XA)> drop table t1;
+0 rows inserted/updated/deleted
+ij(XA)> drop procedure DRS;
+0 rows inserted/updated/deleted
+ij(XA)> commit;
+ij(XA)> --- global transaction
+xa_start xa_noflags 6;
+ij(XA)> create table t1(i int not null primary key, b char(15));
+0 rows inserted/updated/deleted
+ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three');
+3 rows inserted/updated/deleted
+ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
+0 rows inserted/updated/deleted
+ij(XA)> call DRS(1);
+I |B
+-----
+1 |one
+ij(XA)> xa_end xa_success 6;
+ij(XA)> xa_commit xa_1Phase 6;
+ij(XA)> ---------------------------------------------
+----- Test procedure with server-side JDBC with statement holdability set in procedure
+-----
+----- local transaction
+create table testtable1 (id integer, vc varchar(100));
+0 rows inserted/updated/deleted
+ij(XA)> insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two');
+2 rows inserted/updated/deleted
+ij(XA)> create procedure HOLDCURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testHoldCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call HOLDCURSORPROC();
+ID |VC
+-----
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> create procedure CLOSECURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testCloseCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call CLOSECURSORPROC();
+ID |VC
+-----
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> drop table testtable1;
+0 rows inserted/updated/deleted
+ij(XA)> drop procedure HOLDCURSORPROC;
+0 rows inserted/updated/deleted
+ij(XA)> drop procedure CLOSECURSORPROC;
+0 rows inserted/updated/deleted
+ij(XA)> commit;
+ij(XA)> --- global transaction
+xa_start xa_noflags 7;
+ij(XA)> create table testtable1 (id integer, vc varchar(100));
+0 rows inserted/updated/deleted
+ij(XA)> insert into testtable1 values (11, 'testtable1-one'), (12, 'testtable1-two');
+2 rows inserted/updated/deleted
+ij(XA)> create procedure HOLDCURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testHoldCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call HOLDCURSORPROC();
+ID |VC
+-----
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> create procedure CLOSECURSORPROC() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.holdCursorJava.testCloseCursorsProc' result sets 1;
+0 rows inserted/updated/deleted
+ij(XA)> call CLOSECURSORPROC();
+ID |VC
+-----
+11 |testtable1-one
+12 |testtable1-two
+ij(XA)> xa_end xa_success 7;
+ij(XA)> xa_commit xa_1Phase 7;
+ij(XA)>
\ No newline at end of file
xa_proc_test.status
Description: Binary data
