Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
Message-ID: <478659F4.7060103@apache.org>
Date: Thu, 10 Jan 2008 09:46:28 -0800
From: Daniel John Debrunner <djd@apache.org>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Nested Call statements with dynamic result sets WAS Re: svn commit:
 r610184 - ...
References: <20080108215801.DB7FD1A9876@eris.apache.org>
 <x1oxr6gp1yow.fsf@sun.com>
In-Reply-To: <x1oxr6gp1yow.fsf@sun.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.