db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject 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...
Date Thu, 20 Oct 2005 21:01:14 GMT
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");
+
+	}
 }



Mime
View raw message