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 dj...@apache.org on 2008/01/08 22:58:01 UTC
svn commit: r610184 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
Author: djd
Date: Tue Jan 8 13:58:00 2008
New Revision: 610184
URL: http://svn.apache.org/viewvc?rev=610184&view=rev
Log:
Add additional test to ProcedureTest that tests a procedure call within a procedure call, the outer returning the dynamic result sets of the inner.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java?rev=610184&r1=610183&r2=610184&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java Tue Jan 8 13:58:00 2008
@@ -729,7 +729,12 @@
"CREATE PROCEDURE PROC_WITH_SIDE_EFFECTS(ret INT) LANGUAGE JAVA " +
"PARAMETER STYLE JAVA EXTERNAL NAME '" +
ProcedureTest.class.getName() + ".procWithSideEffects' " +
- "DYNAMIC RESULT SETS 2"
+ "DYNAMIC RESULT SETS 2",
+
+ "CREATE PROCEDURE NESTED_RESULT_SETS(proctext VARCHAR(128)) LANGUAGE JAVA " +
+ "PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ ProcedureTest.class.getName() + ".nestedDynamicResultSets' " +
+ "DYNAMIC RESULT SETS 6"
};
@@ -842,6 +847,43 @@
}
c.close();
}
+
+ /**
+ * Method for a Java procedure that calls another procedure
+ * and just passes on the dynamic results from that call.
+ */
+ public static void nestedDynamicResultSets(String procedureText,
+ ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4,
+ ResultSet[] rs5, ResultSet[] rs6)
+ throws SQLException
+ {
+ Connection c = DriverManager.getConnection("jdbc:default:connection");
+
+ CallableStatement cs = c.prepareCall("CALL " + procedureText);
+
+ cs.execute();
+
+ // Mix up the order of the result sets in the returned
+ // parameters, ensures order is defined by creation
+ // and not parameter order.
+ rs6[0] = cs.getResultSet();
+ if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
+ return;
+ rs3[0] = cs.getResultSet();
+ if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
+ return;
+ rs4[0] = cs.getResultSet();
+ if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
+ return;
+ rs2[0] = cs.getResultSet();
+ if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
+ return;
+ rs1[0] = cs.getResultSet();
+ if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
+ return;
+ rs5[0] = cs.getResultSet();
+
+ }
/**
@@ -881,6 +923,14 @@
java.util.Arrays.fill(allRS, null);
checkCSCloseClosesResults(cs,allRS);
java.util.Arrays.fill(allRS, null);
+
+ // a procedure that calls another procedure that returns
+ // dynamic result sets, see if the result sets are handled
+ // correctly through the nesting.
+ CallableStatement nestedCs = prepareCall(
+ "CALL NESTED_RESULT_SETS('MRS.FIVEJP()')");
+ defaultGetMoreResults(nestedCs, allRS);
+
}
Nested Call statements with dynamic result sets WAS Re: svn commit:
r610184 - ...
Posted by Daniel John Debrunner <dj...@apache.org>.
Dag H. Wanvik wrote:
> djd@apache.org writes:
>
>> Author: djd
>> Date: Tue Jan 8 13:58:00 2008
>> New Revision: 610184
>>
>> URL: http://svn.apache.org/viewvc?rev=610184&view=rev
>> Log:
>> Add additional test to ProcedureTest that tests a procedure call within a procedure call, the outer returning the dynamic result sets of the inner.
>>
>
> Just curious here; I just read the Section 4.27.5 of the TECHNICAL
> CORRIGENDUM 1 to the SQL 2003 which describes what happens to dynamic
> result sets. I see this note:
>
>> NOTE 48.3 -: Only the immediate invoker is considered. For example, if
>> an externally-invoked procedure EIP executes a <call statement>
>> invoking an SQL-invoked procedure SIP3 that invokes SIP1, then the
>> result set sequence returned by SIP1 is available only to SIP3, until
>> either SIP3 returns control to EIP or another invocation of SIP1 by
>> SIP3 is given before SIP3 returns. There is no mechanism whereby SIP3
>> can return SIP1's result set sequence to the invoker of SIP3, even if
>> SIP3 is defined to be able to return a result set sequence.
>
> On the face of it it looks Derby allows this, but SQL prohibits it?
> This test seems to do what the last sentence says is not available, or
> maybe I missed something?
Hmmmm, it would seem that SQL prohibits this, that is a procedure
returning the dynamic result sets of a procedure it calls.
I wonder if it applies to SQL-invoked procedures implemented in Java
though, the mechanism for returning a result set sequence is different
in Java (See SQL part 13 section 8.3 GR) 18 & 19). From the JDBC level
it's impossible to tell if a ResultSet comes from a CALL statement or
any other statement since the api to obtain them is identical, that's
not true in SQL. I guess the SQL engine (i.e. Derby) could keep internal
state to track which result sets are dynamic and not allow them to be
processed twice as dynamic ones.
Just seems strange that returning a dynamic result from another SQL
connection is implementation defined, whereas a valid dynamic result set
from a nested CALL would be disallowed. It might be due to the fact that
in SQL there is no mechanism to return such items, whereas in Java there
can be a mechanism.
Dan.
Re: svn commit: r610184 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
djd@apache.org writes:
> Author: djd
> Date: Tue Jan 8 13:58:00 2008
> New Revision: 610184
>
> URL: http://svn.apache.org/viewvc?rev=610184&view=rev
> Log:
> Add additional test to ProcedureTest that tests a procedure call within a procedure call, the outer returning the dynamic result sets of the inner.
>
Just curious here; I just read the Section 4.27.5 of the TECHNICAL
CORRIGENDUM 1 to the SQL 2003 which describes what happens to dynamic
result sets. I see this note:
> NOTE 48.3 -: Only the immediate invoker is considered. For example, if
> an externally-invoked procedure EIP executes a <call statement>
> invoking an SQL-invoked procedure SIP3 that invokes SIP1, then the
> result set sequence returned by SIP1 is available only to SIP3, until
> either SIP3 returns control to EIP or another invocation of SIP1 by
> SIP3 is given before SIP3 returns. There is no mechanism whereby SIP3
> can return SIP1's result set sequence to the invoker of SIP3, even if
> SIP3 is defined to be able to return a result set sequence.
On the face of it it looks Derby allows this, but SQL prohibits it?
This test seems to do what the last sentence says is not available, or
maybe I missed something?
> Modified:
> db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
>
> Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
> URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java?rev=610184&r1=610183&r2=610184&view=diff
> ==============================================================================
> --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java (original)
> +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java Tue Jan 8 13:58:00 2008
> @@ -729,7 +729,12 @@
> "CREATE PROCEDURE PROC_WITH_SIDE_EFFECTS(ret INT) LANGUAGE JAVA " +
> "PARAMETER STYLE JAVA EXTERNAL NAME '" +
> ProcedureTest.class.getName() + ".procWithSideEffects' " +
> - "DYNAMIC RESULT SETS 2"
> + "DYNAMIC RESULT SETS 2",
> +
> + "CREATE PROCEDURE NESTED_RESULT_SETS(proctext VARCHAR(128)) LANGUAGE JAVA " +
> + "PARAMETER STYLE JAVA EXTERNAL NAME '" +
> + ProcedureTest.class.getName() + ".nestedDynamicResultSets' " +
> + "DYNAMIC RESULT SETS 6"
>
> };
>
> @@ -842,6 +847,43 @@
> }
> c.close();
> }
> +
> + /**
> + * Method for a Java procedure that calls another procedure
> + * and just passes on the dynamic results from that call.
> + */
> + public static void nestedDynamicResultSets(String procedureText,
> + ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4,
> + ResultSet[] rs5, ResultSet[] rs6)
> + throws SQLException
> + {
> + Connection c = DriverManager.getConnection("jdbc:default:connection");
> +
> + CallableStatement cs = c.prepareCall("CALL " + procedureText);
> +
> + cs.execute();
> +
> + // Mix up the order of the result sets in the returned
> + // parameters, ensures order is defined by creation
> + // and not parameter order.
> + rs6[0] = cs.getResultSet();
> + if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
> + return;
> + rs3[0] = cs.getResultSet();
> + if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
> + return;
> + rs4[0] = cs.getResultSet();
> + if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
> + return;
> + rs2[0] = cs.getResultSet();
> + if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
> + return;
> + rs1[0] = cs.getResultSet();
> + if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
> + return;
> + rs5[0] = cs.getResultSet();
> +
> + }
>
>
> /**
> @@ -881,6 +923,14 @@
> java.util.Arrays.fill(allRS, null);
> checkCSCloseClosesResults(cs,allRS);
> java.util.Arrays.fill(allRS, null);
> +