Return-Path: 
 <derby-commits-return-9012-apmail-db-derby-commits-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-commits-archive@www.apache.org
Received: (qmail 32513 invoked from network); 10 Dec 2007 18:45:36 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 10 Dec 2007 18:45:36 -0000
Received: (qmail 80401 invoked by uid 500); 10 Dec 2007 18:45:25 -0000
Delivered-To: apmail-db-derby-commits-archive@db.apache.org
Received: (qmail 80378 invoked by uid 500); 10 Dec 2007 18:45:25 -0000
Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-commits-help@db.apache.org>
list-unsubscribe: <mailto:derby-commits-unsubscribe@db.apache.org>
List-Post: <mailto:derby-commits@db.apache.org>
Reply-To: "Derby Development" <derby-dev@db.apache.org>
List-Id: <derby-commits.db.apache.org>
Delivered-To: mailing list derby-commits@db.apache.org
Received: (qmail 80366 invoked by uid 99); 10 Dec 2007 18:45:25 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 10 Dec 2007 10:45:25 -0800
X-ASF-Spam-Status: No, hits=-100.0 required=10.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 10 Dec 2007 18:45:32 +0000
Received: by eris.apache.org (Postfix, from userid 65534)
	id 9FCAD1A9832; Mon, 10 Dec 2007 10:45:10 -0800 (PST)
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r602991 - in
 /db/derby/code/trunk/java/engine/org/apache/derby:
 iapi/jdbc/ConnectionContext.java impl/jdbc/EmbedConnectionContext.java
 impl/jdbc/EmbedResultSet.java impl/jdbc/EmbedStatement.java
 impl/sql/execute/CallStatementResultSet.java
Date: Mon, 10 Dec 2007 18:45:09 -0000
To: derby-commits@db.apache.org
From: djd@apache.org
X-Mailer: svnmailer-1.0.8
Message-Id: <20071210184510.9FCAD1A9832@eris.apache.org>
X-Virus-Checked: Checked by ClamAV on apache.org

Author: djd
Date: Mon Dec 10 10:45:05 2007
New Revision: 602991

URL: http://svn.apache.org/viewvc?rev=602991&view=rev
Log:
DERBY-1585 For a CALL statement that generates dynamic result sets add code so that the result sets are closed if they are inaccessible following the SQL Standard.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/jdbc/ConnectionContext.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/jdbc/ConnectionContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/jdbc/ConnectionContext.java?rev=602991&r1=602990&r2=602991&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/jdbc/ConnectionContext.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/jdbc/ConnectionContext.java Mon Dec 10 10:45:05 2007
@@ -21,7 +21,6 @@
 
 package org.apache.derby.iapi.jdbc;
 
-import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.sql.ResultSet;
 
 import java.sql.Connection;
@@ -57,4 +56,27 @@
 	(
 		ResultSet 				executionResultSet
 	) throws java.sql.SQLException;
+    
+    /**
+     * Process the resultSet as a dynamic result for closure.
+     * The result set will have been created in a Java procedure.
+     * If the ResultSet is a valid dynamic ResultSet for
+     * this connection, then it is set up as a dynamic result
+     * which includes:
+     * <UL>
+     * <LI> breaking its link with the JDBC connection
+     * that created it, since there is a good chance that connection
+     * was closed explicitly by the Java procedure.
+     * <LI> marking its activation as single use to ensure the
+     * close of the ResultSet will close the activation.
+     * </UL>
+     * <P>
+     * If the result set a valid dynamic result then false will
+     * be returned and no action made against it.
+     * 
+     * @param resultSet ResultSet to process.
+     * @return True if this ResultSet was created by this connection
+     * and the result set is open. False otherwise.
+     */
+    public boolean processInaccessibleDynamicResult(java.sql.ResultSet resultSet);
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java?rev=602991&r1=602990&r2=602991&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java Mon Dec 10 10:45:05 2007
@@ -133,4 +133,25 @@
 							false, (EmbedStatement) null, true);
 		return rs;
 	}
+
+    /**
+     * Process a ResultSet from a procedure to be a dynamic result,
+     * but one that will be closed due to it being inaccessible. We cannot simply
+     * close the ResultSet as it the nested connection that created
+     * it might be closed, leading to its close method being a no-op.
+     * This performs all the conversion (linking the ResultSet
+     * to a valid connection) required but does not close
+     * the ResultSet.
+     * 
+     *   @see EmbedStatement#processDynamicResult(EmbedConnection, java.sql.ResultSet, EmbedStatement)
+     */
+    public boolean processInaccessibleDynamicResult(java.sql.ResultSet resultSet) {
+        EmbedConnection conn = (EmbedConnection) connRef.get();
+        if (conn == null)
+            return false;
+        
+        // Pass in null as the Statement to own the ResultSet since
+        // we don't have one since the dynamic result will be inaccessible.
+        return EmbedStatement.processDynamicResult(conn, resultSet, null) != null;
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java?rev=602991&r1=602990&r2=602991&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java Mon Dec 10 10:45:05 2007
@@ -565,7 +565,7 @@
 				// just give up and return
 				return;
 			}
-
+            
 			try	{
 				try	{
 					theResults.close(); 
@@ -4389,15 +4389,25 @@
 	}
 
 	/**
-		A dynamic result set was created in a procedure by a nested connection.
+		A dynamic result was created in a procedure by a nested connection.
 		Once the procedure returns, there is a good chance that connection is closed,
 		so we re-attach the result set to the connection of the statement the called
 		the procedure, which will be still open.
+        <BR>
+        In the case where the dynamic result will not be accessible
+        then owningStmt will be null, the ResultSet will be linked to
+        the root connection to allow its close method to work. It
+        will remain attached to its original statement.
 	*/
 	void setDynamicResultSet(EmbedStatement owningStmt) {
 
-		this.owningStmt = owningStmt;
-		this.localConn = owningStmt.getEmbedConnection();
+        
+        if (owningStmt != null) {
+		    this.owningStmt = owningStmt;
+            this.localConn = owningStmt.getEmbedConnection();
+        }
+        else
+            this.localConn = this.localConn.rootConnection;
         
         // The activation that created these results now becomes
         // a single use activation so it will be closed when this

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java?rev=602991&r1=602990&r2=602991&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java Mon Dec 10 10:45:05 2007
@@ -1272,8 +1272,6 @@
 					}
 
 					updateCount = resultsToWrap.modifiedRowCount();
-
-					resultsToWrap.finish();	// Don't need the result set any more
 					results = null; // note that we have none.
 
                     int dynamicResultCount = 0;
@@ -1282,6 +1280,8 @@
                             processDynamicResults(a.getDynamicResults(),
                                                   a.getMaxDynamicResults());
 					}
+                    
+                    resultsToWrap.finish(); // Don't need the result set any more
 
                     // executeQuery() is not allowed if the statement
                     // doesn't return exactly one ResultSet.
@@ -1553,26 +1553,25 @@
 
 			java.sql.ResultSet[] param = holder[i];
 
-			if (param[0] == null)
-				continue;
-
 			java.sql.ResultSet rs = param[0];
-			param[0] = null;
-
-			// ignore non-Derby result sets or results sets from another connection
-			if (!(rs instanceof EmbedResultSet))
-				continue;
-
-			EmbedResultSet lrs = (EmbedResultSet) rs;
-
-			if (lrs.getEmbedConnection().rootConnection != getEmbedConnection().rootConnection)
-				continue;
 
-			// ignore closed result sets.
-			if (lrs.isClosed)
-				continue;
+            // Clear the JDBC dynamic ResultSet from the language
+            // ResultSet for the CALL statement. This stops the
+            // CALL statement closing the ResultSet when its language
+            // ResultSet is closed, which will happen just after the
+            // call to the processDynamicResults() method.
+			param[0] = null;
+            
+            // ignore non-Derby result sets or results sets from another connection
+            // and closed result sets.
+            EmbedResultSet lrs = EmbedStatement.processDynamicResult(
+                    getEmbedConnection(), rs, this);
+            
+            if (lrs == null)
+            {
+                continue;
+            }
 
-			lrs.setDynamicResultSet(this);
 			sorted[actualCount++] = lrs;
 		}
 
@@ -1608,6 +1607,51 @@
 
 		return actualCount;
 	}
+    
+    /**
+     * Process a ResultSet created in a Java procedure as a dynamic result.
+     * To be a valid dynamic result the ResultSet must be:
+     * <UL>
+     * <LI> From a Derby system
+     * <LI> From a nested connection of connection passed in
+     * or from the connection itself.
+     * <LI> Open
+     * </UL>
+     * Any invalid ResultSet is ignored.
+     * 
+     * <P>
+     * if b
+     * 
+     * @param conn Connection ResultSet needs to belong to
+     * @param resultSet ResultSet to be tested
+     * @param callStatement Statement that executed the CALL, null if 
+     * @return The result set cast down to EmbedResultSet, null if not a valid
+     * dynamic result.
+     */
+    static EmbedResultSet processDynamicResult(EmbedConnection conn,
+            java.sql.ResultSet resultSet,
+            EmbedStatement callStatement)
+    {
+        if (resultSet == null)
+            return null;
+
+        // ignore non-Derby result sets or results sets from another connection
+        if (!(resultSet instanceof EmbedResultSet))
+            return null;
+
+        EmbedResultSet lrs = (EmbedResultSet) resultSet;
+
+        if (lrs.getEmbedConnection().rootConnection != conn.rootConnection)
+            return null;
+
+        // ignore closed result sets.
+        if (lrs.isClosed)
+            return null;
+        
+        lrs.setDynamicResultSet(callStatement);
+
+        return lrs;
+    }
 
 	/**
 		Callback on the statement when one of its result sets is closed.

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=602991&r1=602990&r2=602991&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 Dec 10 10:45:05 2007
@@ -21,14 +21,30 @@
 
 package org.apache.derby.impl.sql.execute;
 
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
 import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.jdbc.ConnectionContext;
 import org.apache.derby.iapi.services.loader.GeneratedMethod;
 import org.apache.derby.iapi.sql.Activation;
-import org.apache.derby.iapi.sql.ResultSet;
 
 /**
- * Call the specified expression, ignoring the return, if any.
- *
+ * Call a Java procedure. This calls a generated method in the
+ * activation which sets up the parameters and then calls the
+ * Java method that the procedure resolved to.
+ * <P>
+ * Valid dynamic results returned by the procedure will be closed
+ * as inaccessible when this is closed (e.g. a CALL within a trigger).
+ * 
+ * <BR>
+ * Any code that requires the dynamic results to be accessible
+ * (such as the JDBC Statement object executing the CALL) must
+ * obtain the dynamic results from Activation.getDynamicResults()
+ * and remove each ResultSet it will be handling by clearing the
+ * reference in the object returned.
+ * 
+ * @see Activation#getDynamicResults()
  */
 class CallStatementResultSet extends NoRowsResultSetImpl
 {
@@ -49,13 +65,105 @@
 	}
 
 	/**
+     * Just invoke the method.
 		@exception StandardException Standard Derby error policy
 	*/
 	public void open() throws StandardException
 	{
 		setup();
 		methodCall.invoke(activation);
-		close();
+    }
+    
+    /**
+     * Need to explicitly close any dynamic result sets.
+     * <BR>
+     * If the dynamic results are not accessible then they
+     * need to be destroyed (ie. closed) according the the
+     * SQL Standard.
+     * <BR>
+     * An execution of a CALL statement through JDBC makes the
+     * dynamic results accessible, in this case the closing
+     * of the dynamic result sets is handled by the JDBC
+     * statement object (EmbedStatement) that executed the CALL.
+     * We cannot unify the closing of dynamic result sets to
+     * this close, as in accessible case it is called during
+     * the Statement.execute call, thus it would close the
+     * dynamic results before the application has a change
+     * to use them.
+     * 
+     * <BR>
+     * With an execution of a CALL
+     * statement as a trigger's action statement the dynamic
+     * result sets are not accessible. In this case this close
+     * method is called after the execution of the trigger's
+     * action statement.
+     * <BR>
+     * <BR>
+     * Section 4.27.5 of the TECHNICAL CORRIGENDUM 1 to the SQL 2003
+     * Standard details what happens to dynamic result sets in detail,
+     * the SQL 2003 foundation document is missing these details.
+     */
+    public void close() throws StandardException
+    {
+        super.close();
+        
+        
+        
+        ResultSet[][] dynamicResults = getActivation().getDynamicResults();
+        if (dynamicResults != null)
+        {
+            // Need to ensure all the result sets opened by this
+            // CALL statement for this connection are closed.
+            // If any close() results in an exception we need to keep going,
+            // save any exceptions and then throw them once we are complete.
+            StandardException errorOnClose = null;
+            
+            ConnectionContext jdbcContext = null;
+            
+            for (int i = 0; i < dynamicResults.length; i++)
+            {
+                ResultSet[] param = dynamicResults[i];
+                ResultSet drs = param[0];
+                
+                // Can be null if the procedure never set this parameter
+                // or if the dynamic results were processed by JDBC (EmbedStatement).
+                if (drs == null)
+                    continue;
+                
+                if (jdbcContext == null)
+                    jdbcContext = (ConnectionContext)
+                   lcc.getContextManager().getContext(ConnectionContext.CONTEXT_ID);
+               
+                try {
+                    
+                    // Is this a valid, open dynamic result set for this connection?
+                    if (!jdbcContext.processInaccessibleDynamicResult(drs))
+                    {
+                        // If not just ignore it, not Derby's problem.
+                        continue;
+                    }
+                    
+                    drs.close();
+                    
+                } catch (SQLException e) {
+                    
+                    // Just report the first error
+                    if (errorOnClose == null)
+                    {
+                        StandardException se = StandardException.plainWrapException(e);
+                        errorOnClose = se;
+                    }
+                }
+                finally {
+                    // Remove any reference to the ResultSet to allow
+                    // it and any associated resources to be garbage collected.
+                    param[0] = null;
+                }
+            }
+            
+            if (errorOnClose != null)
+                throw errorOnClose;
+        }       
     }
 
 	/**