You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by km...@apache.org on 2005/10/20 23:01:14 UTC
svn commit: r327002 - in /db/derby/code/branches/10.1/java:
drda/org/apache/derby/impl/drda/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/
testing/org/apache/derbyTesting/func...
Author: kmarsden
Date: Thu Oct 20 14:01:01 2005
New Revision: 327002
URL: http://svn.apache.org/viewcvs?rev=327002&view=rev
Log:
DERBY-498 - Result set holdability defined inside stored procedures is ignored by server/client
merge from trunk
svn merge -r 326717:326718 https://svn.apache.org/repos/asf/db/derby/code/trunk
Contributed by Deepa Remesh
Note the test lang/holdCursorJava.java does not currently run with derbynetmats or derbynetclientmats. It requires port of DERBY-593 to run.
Added:
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/holdCursorJava.out (props changed)
- copied unchanged from r326718, db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/holdCursorJava.out
Modified:
db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAConnThread.java
db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAStatement.java
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/holdCursorJava.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java
Modified: db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAConnThread.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAConnThread.java?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAConnThread.java (original)
+++ db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAConnThread.java Thu Oct 20 14:01:01 2005
@@ -2340,8 +2340,11 @@
trace("sending QRYPRCTYP: " + prcType);
writer.writeScalar2Bytes(CodePoint.QRYPRCTYP, prcType);
- //pass the SQLCSRHLD codepoint only if statement has hold cursors over commit set
- if (stmt.withHoldCursor == JDBC30Translation.HOLD_CURSORS_OVER_COMMIT)
+ //pass the SQLCSRHLD codepoint only if statement producing the ResultSet has
+ //hold cursors over commit set. In case of stored procedures which use server-side
+ //JDBC, the holdability of the ResultSet will be the holdability of the statement
+ //in the stored procedure, not the holdability of the calling statement.
+ if (stmt.getCurrentDrdaResultSet().withHoldCursor == JDBC30Translation.HOLD_CURSORS_OVER_COMMIT)
writer.writeScalar1Byte(CodePoint.SQLCSRHLD, CodePoint.TRUE);
if (sqlamLevel >= MGRLVL_7)
{
Modified: db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAStatement.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAStatement.java?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAStatement.java (original)
+++ db/derby/code/branches/10.1/java/drda/org/apache/derby/impl/drda/DRDAStatement.java Thu Oct 20 14:01:01 2005
@@ -273,6 +273,38 @@
}
return holdValue;
}
+
+ /**
+ *
+ * get resultSetHoldability with reflection.
+ * We need to use reflection so we can use hold cursors with 1.3.1.
+ * And also since our statement might be a BrokeredStatement.
+ *
+ * @param rs ResultSet
+ * @return the resultSet holdability for the prepared statement
+ *
+ */
+ protected int getResultSetHoldability(ResultSet rs) throws SQLException
+ {
+ Statement rsstmt = null;
+ int holdValue = -1;
+
+ if (rs != null)
+ rsstmt = rs.getStatement();
+ else
+ rsstmt = getPreparedStatement();
+
+ Class[] getResultSetHoldabilityParam = {};
+ try {
+ Method sh =
+ rsstmt.getClass().getMethod("getResultSetHoldability", getResultSetHoldabilityParam);
+ holdValue = ((Integer) sh.invoke(rsstmt,null)).intValue();
+ }
+ catch (Exception e) {
+ handleReflectionException(e);
+ }
+ return holdValue;
+ }
/*
* Is lob object nullable
@@ -540,7 +572,11 @@
rs = ps.getResultSet();
if (rs !=null)
{
- addResultSet(rs);
+ //For callable statement, get holdability of statement generating the result set
+ if(isCallable)
+ addResultSet(rs,getResultSetHoldability(rs));
+ else
+ addResultSet(rs,withHoldCursor);
hasResultSet = true;
}
// For normal selects we are done, but procedures might
@@ -706,6 +742,16 @@
currentDrdaRs.setResultSet(value);
setRsDefaultOptions(currentDrdaRs);
}
+
+ /**
+ * Gets the current DRDA ResultSet
+ *
+ * @return DRDAResultSet
+ */
+ protected DRDAResultSet getCurrentDrdaResultSet()
+ {
+ return currentDrdaRs ;
+ }
/**
* Set currentDrdaResultSet
@@ -785,11 +831,12 @@
* Set as the current result set if there is not an
* existing current resultset.
* @param value - ResultSet to add
+ * @param holdValue - Holdability of the ResultSet
* @return Consistency token for this resultSet
* For a single resultSet that is the same as the statement's
* For multiple resultSets just the consistency token is changed
*/
- protected String addResultSet(ResultSet value) throws SQLException
+ protected String addResultSet(ResultSet value, int holdValue) throws SQLException
{
DRDAResultSet newDrdaRs = null;
@@ -822,6 +869,7 @@
newDrdaRs.setResultSet(value);
newDrdaRs.setPkgcnstknStr(newRsPkgcnstknStr);
+ newDrdaRs.withHoldCursor = holdValue;
setRsDefaultOptions(newDrdaRs);
newDrdaRs.suspend();
numResultSets++;
Propchange: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/holdCursorJava.out
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/xaSimplePositive.out Thu Oct 20 14:01:01 2005
@@ -192,4 +192,37 @@
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)>
\ No newline at end of file
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/holdCursorJava.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/holdCursorJava.out?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/holdCursorJava.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/holdCursorJava.out Thu Oct 20 14:01:01 2005
@@ -1,5 +1,43 @@
Creating table...
done creating table and inserting data.
+testHoldability with HOLD_CURSORS_OVER_COMMIT
+testStatements()
+checkResultSet before commit
+11, testtable1-one
+checkResultSet after commit
+12, testtable1-two
+checkResultSet before commit
+21, testtable2-one
+checkResultSet after commit
+EXPECTED EXCEPTION:ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
+testStatementsInProcedure()
+checkResultSet before commit
+11, testtable1-one
+checkResultSet before commit
+21, testtable2-one
+checkResultSet after commit
+11, testtable1-one
+checkResultSet after commit
+EXPECTED EXCEPTION:ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
+testHoldability with CLOSE_CURSORS_AT_COMMIT
+testStatements()
+checkResultSet before commit
+11, testtable1-one
+checkResultSet after commit
+12, testtable1-two
+checkResultSet before commit
+21, testtable2-one
+checkResultSet after commit
+EXPECTED EXCEPTION:ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
+testStatementsInProcedure()
+checkResultSet before commit
+11, testtable1-one
+checkResultSet before commit
+21, testtable2-one
+checkResultSet after commit
+11, testtable1-one
+checkResultSet after commit
+EXPECTED EXCEPTION:ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
Start multi table query with holdability true test
value of t2.c22 is 1
value of t2.c22 is 2
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/xaSimplePositive.out Thu Oct 20 14:01:01 2005
@@ -192,4 +192,39 @@
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);
+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)>
\ No newline at end of file
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/xaSimplePositive.sql Thu Oct 20 14:01:01 2005
@@ -138,3 +138,23 @@
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;
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java?rev=327002&r1=327001&r2=327002&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/holdCursorJava.java Thu Oct 20 14:01:01 2005
@@ -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);
@@ -73,6 +76,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();
@@ -189,4 +198,99 @@
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");
+
+ }
}