| Here is my reply to the Rdb list service...
Subj: Fwd: Stored procedures returning result set..
Date: 20 Jan 97 19:06:59
Content-Type:text/plain; charset="US-ASCII"
As Per wrote...
"When (internal) stored procedures were first introduced in Rdb (back in the
v60 days) the feature was immediately critizised by a couple of
Sybase-oriented colleagues of mine because an SP was unable to return a set
of result rows. BTW: I've noticed that the same restriction seems to apply
to DB2 for their first attempt at (external) stored procedures."
Firstly, when we did stored procedures we were more interested in getting it
right, than adding bells and whistles first up. At the time we were
developing stored routines there was little or no talk of procedure result
sets from our customers. That came after we shipped stored procedures in Rdb
V6.0.
"A quick $search thru the online release-notes for Rdb7 did not reveal any
news in this area (but plenty of other stuff, of course). Did I miss
something or is this feature still on the waiting list? A standard for
persistent modules is on its way, I believe. Perhaps this is what we're
waiting for?"
We believed that adding ANSI and ISO SQL-92 Temporary tables would (a) provide
standard syntax and (b) provide a mechanism that could be used to implement
result sets. When we decide on a clean syntax we may package this approach.
It will be pretty much as I describe below.
Firstly, create a GLOBAL or LOCAL temporary table in your database which will
be used by the "results" cursor. Maybe even give it the same name.
Secondly, call a stored procedure to load the temporary table. It can do this
by inserting data into the temporary table (this can be controlled by USER, or
other execution parameters).
Finally, declare and open a cursor on this temporary table and fetch the rows.
This interface has the advantage that the existing ODBC driver should work
just fine. i.e. we didn't need to make the ODBC drive understand anything new
about Rdb.
The default for stored procedures is to delete the rows on COMMIT, so you may
want to choose a different setting depending upon your application. The
temporary tables are private to an attach, therefore many users can use this
interface without interfering with each other.
Please try it, I hope you like what we did. Which I believe is a much more
general feature that can solve more than procedure result sets.
In addition you can declare temporary tables in interactive and dynamic SQL to
generate these types of cursors on the fly.
---
Ian Smith
Rdb Relational Technology
Oracle Rdb
--=_ORCL_29103822_0_11919701211904511
Content-Type:message/rfc822
|