Dag H. Wanvik wrote:
[EMAIL PROTECTED] 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.
