Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r631302 - in /db/derby/code/branches/10.3/java:
 engine/org/apache/derby/impl/sql/execute/
 testing/org/apache/derbyTesting/functionTests/tests/lang/
 testing/org/apache/derbyTesting/junit/
Date: Tue, 26 Feb 2008 17:16:00 -0000
To: derby-commits@db.apache.org
From: mamta@apache.org
Message-Id: <20080226171606.22ED01A9832@eris.apache.org>

Author: mamta
Date: Tue Feb 26 09:15:59 2008
New Revision: 631302

URL: http://svn.apache.org/viewvc?rev=631302&view=rev
Log:
Merging revision 631108 from trunk into 10.3 codeline. In addition, adding method
assertNoMoreResults in junit/JDBC.java since it is used by the test which is getting merged 
through 631108.

The commit comments for 631108
were as follows
DERBY-3304

When a SQL exception is thrown, make sure that rollback caused by it closes all the resultsets
irrespective of whether they return rows or not. This cleanup was not happening for
CallableStatementResultSet. To fix this, in CallableStatementResultSet class, I have changed
the no-op cleanup() method to call close(). Without this, the locks held by the resultsets
created inside the Java procedure method were not getting released.

I have added a test case to make sure that this code change is tested. I have created a
Java procedure which creates a dynamic resultset, a local resultset and then does an
insert which will cause duplicate key exception. As part of rollback for exception, Derby
closes the dynamic resultset and local resultset along with the CallableStatementResultset.
And the test case is able to drop the tables which were used by the dynamic and local
resultset without running into locking issues.


Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/junit/JDBC.java

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java?rev=631302&r1=631301&r2=631302&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java Tue Feb 26 09:15:59 2008
@@ -169,8 +169,8 @@
 	/**
 	 * @see org.apache.derby.iapi.sql.ResultSet#cleanUp
 	 */
-	public void	cleanUp() 
+	public void	cleanUp() throws StandardException
 	{
-		/* Nothing to do */
+			close();
 	}
 }

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java?rev=631302&r1=631301&r2=631302&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java Tue Feb 26 09:15:59 2008
@@ -801,8 +801,29 @@
 
             JDBC.assertClosed(rs1);
             JDBC.assertClosed(resultSet);
-            s.execute("drop table dellater1");
-            s.execute("drop table dellater2");
+            //Create a procedure which does an insert into a table. Then call 
+            //it with parameters such that insert will fail because of 
+            //duplicate key. The procedure also has couple select statements
+            //The exception thrown for duplicate key should close the
+            //resultsets associated with select statement and we should be
+            //able to drop the tables used in the select queries without
+            //running into locking issues.
+            s
+            .execute("create procedure insertCausingRollback"+
+            		"(p1 int, p2 CHAR(20))  MODIFIES SQL DATA "+
+            		"dynamic result sets 1 language java external "+
+            		"name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertCausingRollback' "+
+            		"parameter style java");
+            s.executeUpdate("CREATE TABLE DELLATER3(c31 int)");
+            s.executeUpdate("INSERT INTO DELLATER3 VALUES(1),(2),(3),(4)");
+            conn.commit();
+            drs1 = prepareCall("CALL insertCausingRollback(3,'3')");
+            assertStatementError("23505",drs1);
+            JDBC.assertNoMoreResults(drs1);
+            s.execute("DROP TABLE DELLATER1");
+            s.execute("DROP TABLE DELLATER2");
+            s.execute("DROP TABLE DELLATER3");
+
             conn.setAutoCommit(oldAutoCommit);
         }
 
@@ -1129,6 +1150,51 @@
         ps.setInt(1, p1);
         data[0] = ps.executeQuery();
         conn.rollback();
+        conn.close();
+    }
+
+    /**
+     * A test case for DERBY-3414. The procedure is attempting to insert a
+     * duplicate key into a table which causes an internal rollback (vs a
+     * user-initiated rollback). This internal rollback should close the
+     * internal resultset associated with Java procedure along with closing
+     * the resulsets for 2 SELECT queries.
+     * 
+     * @param p1
+     * @param p2
+     * @param data
+     * @throws SQLException
+     */
+    public static void insertCausingRollback(int p1, String p2, ResultSet[] data) throws SQLException {
+        Connection conn = DriverManager
+                .getConnection("jdbc:default:connection");
+        
+        //The resultset created here is a dynamic resultset and will be
+        //available to the caller of the java procedure (provided that there
+        //is no SQL exception thrown inside of this procedure. An exception 
+        //will cause Derby to close this resultset).
+        PreparedStatement ps = conn.prepareStatement(
+        		"select * from dellater2 where c11 = ?");
+        ps.setInt(1, p1);
+        data[0] = ps.executeQuery();
+        
+        //The resultset created here has the lifetime of this procedure
+        //and is not available to the caller of the procedure.
+        PreparedStatement ps1 = conn.prepareStatement(
+        		"select * from dellater3 where c31 = ?");
+        ps1.setInt(1, p1);
+        ps1.executeQuery();
+        
+        //Depending on the value of p1, following may throw duplicate key 
+        //exception. If that happens, both the dynamic resultset and local
+        //resultset created above will get closed and locks held by them
+        //and insert statement will be released
+        PreparedStatement ps2 = conn
+                .prepareStatement("insert into dellater1 values (?, ?)");
+        ps2.setInt(1, p1);
+        ps2.setString(2, p2);
+        ps2.executeUpdate();
+        ps2.close();
         conn.close();
     }
     

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=631302&r1=631301&r2=631302&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/junit/JDBC.java Tue Feb 26 09:15:59 2008
@@ -1099,6 +1099,20 @@
     }
 
     /**
+     * Assert that the statement has no more results(getMoreResults) and it
+     * indeed does not return any resultsets(by checking getResultSet).
+     * Also, ensure that update count is -1.
+     * @param s Statement holding no results.
+     * @throws SQLException Exception checking results.
+     */
+    public static void assertNoMoreResults(Statement s) throws SQLException
+    {
+	       Assert.assertFalse(s.getMoreResults());
+	        Assert.assertTrue(s.getUpdateCount() == -1);
+	        Assert.assertNull(s.getResultSet());
+    }
+
+    /**
      * Convert byte array to String.
      * Each byte is converted to a hexadecimal string representation.
      *