db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r757253 - in /db/derby/code/branches/10.4/java: engine/org/apache/derby/catalog/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Sun, 22 Mar 2009 20:35:42 GMT
Author: kmarsden
Date: Sun Mar 22 20:35:41 2009
New Revision: 757253

URL: http://svn.apache.org/viewvc?rev=757253&view=rev
Log:
DERBY-4095 Trigger in fails with ERROR 38000: The exception 'java.sql.SQLException: ResultSet
not open during VTIResultSet.getNextRowCore()


Modified:
    db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerNewTransitionRows.java
    db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerOldTransitionRows.java
    db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerNewTransitionRows.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerNewTransitionRows.java?rev=757253&r1=757252&r2=757253&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerNewTransitionRows.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerNewTransitionRows.java
Sun Mar 22 20:35:41 2009
@@ -62,6 +62,13 @@
 	 */
 	public TriggerNewTransitionRows() throws SQLException
 	{
+		initializeResultSet();
+	}
+
+	private ResultSet initializeResultSet() throws SQLException {
+		if (resultSet != null)
+			resultSet.close();
+		
 		TriggerExecutionContext tec = Factory.getTriggerExecutionContext();
 		if (tec == null)
 		{
@@ -73,15 +80,18 @@
 		{
 			throw new SQLException("There is no new transition rows result set for this trigger",
"38000");
 		}
-    }
+		return resultSet;
+	}
     
     public ResultSetMetaData getMetaData() throws SQLException
     {
         return resultSet.getMetaData();
     }
     
-   public ResultSet executeQuery() {
-       return resultSet;
+   public ResultSet executeQuery() throws SQLException {
+	   //DERBY-4095. Need to reinititialize ResultSet on 
+       //executeQuery, in case it was closed in a NESTEDLOOP join.
+       return initializeResultSet();
    }
     
    public int getResultSetConcurrency() {

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerOldTransitionRows.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerOldTransitionRows.java?rev=757253&r1=757252&r2=757253&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerOldTransitionRows.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/catalog/TriggerOldTransitionRows.java
Sun Mar 22 20:35:41 2009
@@ -63,6 +63,13 @@
 	 */
 	public TriggerOldTransitionRows() throws SQLException
 	{
+		initializeResultSet();
+	}
+
+	private ResultSet initializeResultSet() throws SQLException {
+		if (resultSet != null)
+			resultSet.close();
+		
 		TriggerExecutionContext tec = Factory.getTriggerExecutionContext();
 		if (tec == null)
 		{
@@ -74,10 +81,13 @@
 		{
 			throw new SQLException("There is no old transition rows result set for this trigger",
"38000");
 		}
-    }  
+		return resultSet;
+	}  
 
-       public ResultSet executeQuery() {
-           return resultSet;
+       public ResultSet executeQuery() throws SQLException {
+    	   //DERBY-4095. Need to reinititialize ResultSet on 
+           //executeQuery, in case it was closed in a NESTEDLOOP join.
+           return initializeResultSet();
        }
         
        public int getResultSetConcurrency() {

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=757253&r1=757252&r2=757253&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Sun Mar 22 20:35:41 2009
@@ -1347,4 +1347,77 @@
     }
 
 
+    /**
+     * Test that a nested loop join that accesses the 
+     * TriggerOldTransitionRowsVTI can reopen the ResultSet properly 
+     * when it re-executes.
+     * @throws SQLException
+     */
+    public void testDerby4095OldTriggerRows() throws SQLException {
+        Statement s = createStatement();
+        
+        s.executeUpdate("CREATE TABLE APP.TAB (I INT)");
+        s.executeUpdate("CREATE TABLE APP.LOG (I INT, NAME VARCHAR(30), DELTIME TIMESTAMP)");
+        s.executeUpdate("CREATE TABLE APP.NAMES(ID INT, NAME VARCHAR(30))");
+
+        
+        s.executeUpdate("CREATE TRIGGER  APP.MYTRIG AFTER DELETE ON APP.TAB REFERENCING OLD_TABLE
AS OLDROWS FOR EACH STATEMENT INSERT INTO APP.LOG(i,name,deltime) SELECT OLDROWS.I, NAMES.NAME,
CURRENT_TIMESTAMP FROM --DERBY-PROPERTIES joinOrder=FIXED\n NAMES, OLDROWS --DERBY-PROPERTIES
joinStrategy = NESTEDLOOP\n WHERE (OLDROWS.i = NAMES.ID) AND (1 = 1)");
+        
+        s.executeUpdate("insert into APP.tab values(1)");
+        s.executeUpdate("insert into APP.tab values(2)");
+        s.executeUpdate("insert into APP.tab values(3)");
+
+        s.executeUpdate("insert into APP.names values(1,'Charlie')");
+        s.executeUpdate("insert into APP.names values(2,'Hugh')");
+        s.executeUpdate("insert into APP.names values(3,'Alex')");
+
+        // Now delete a row so we fire the trigger.
+        s.executeUpdate("delete from tab where i = 1");
+        // Check the log to make sure the trigger fired ok
+        ResultSet loggedDeletes = s.executeQuery("SELECT * FROM APP.LOG");
+        JDBC.assertDrainResults(loggedDeletes, 1);
+                 
+        s.executeUpdate("DROP TABLE APP.TAB");
+        s.executeUpdate("DROP TABLE APP.LOG");
+        s.executeUpdate("DROP TABLE APP.NAMES");
+        
+    }
+    
+    /**
+     * Test that a nested loop join that accesses the 
+     * TriggerNewTransitionRowsVTI can reopen the ResultSet properly 
+     * when it re-executes.
+     * @throws SQLException
+     */
+    public void testDerby4095NewTriggerRows() throws SQLException {
+        Statement s = createStatement();
+        s.executeUpdate("CREATE TABLE APP.TAB (I INT)");
+        s.executeUpdate("CREATE TABLE APP.LOG (I INT, NAME VARCHAR(30), UPDTIME TIMESTAMP,
NEWVALUE INT)");
+        s.executeUpdate("CREATE TABLE APP.NAMES(ID INT, NAME VARCHAR(30))");
+
+        
+        s.executeUpdate("CREATE TRIGGER  APP.MYTRIG AFTER UPDATE ON APP.TAB REFERENCING OLD_TABLE
AS OLDROWS NEW_TABLE AS NEWROWS FOR EACH STATEMENT INSERT INTO APP.LOG(i,name,updtime,newvalue)
SELECT OLDROWS.I, NAMES.NAME, CURRENT_TIMESTAMP, NEWROWS.I  FROM --DERBY-PROPERTIES joinOrder=FIXED\n
NAMES, NEWROWS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP\n ,OLDROWS WHERE (NEWROWS.i =
NAMES.ID) AND (1 = 1)");
+        
+        s.executeUpdate("insert into tab values(1)");
+        s.executeUpdate("insert into tab values(2)");
+        s.executeUpdate("insert into tab values(3)");
+
+        s.executeUpdate("insert into names values(1,'Charlie')");
+        s.executeUpdate("insert into names values(2,'Hugh')");
+        s.executeUpdate("insert into names values(3,'Alex')");
+
+        // Now update a row to fire the trigger
+        s.executeUpdate("update tab set i=1 where i = 1");
+
+        // Check the log to make sure the trigger fired ok
+        ResultSet loggedUpdates = s.executeQuery("SELECT * FROM APP.LOG");
+        JDBC.assertDrainResults(loggedUpdates, 1);
+        
+        
+        s.executeUpdate("DROP TABLE APP.TAB");
+        s.executeUpdate("DROP TABLE APP.LOG");
+        s.executeUpdate("DROP TABLE APP.NAMES");
+    }
+    
+    
 }



Mime
View raw message