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 ma...@apache.org on 2008/02/26 08:26:27 UTC
svn commit: r631108 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java
testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java
Author: mamta
Date: Mon Feb 25 23:26:25 2008
New Revision: 631108
URL: http://svn.apache.org/viewvc?rev=631108&view=rev
Log:
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/trunk/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java?rev=631108&r1=631107&r2=631108&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java Mon Feb 25 23:26:25 2008
@@ -189,8 +189,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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java?rev=631108&r1=631107&r2=631108&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java Mon Feb 25 23:26:25 2008
@@ -828,8 +828,29 @@
JDBC.assertDrainResults(rs);
JDBC.assertNoMoreResults(drs1);
- 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);
}
@@ -1180,6 +1201,51 @@
"select * from dellater1 where i = ?");
ps.setInt(1, p1);
data2[0] = ps.executeQuery();
+ 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();
}