Re: Returning recordsets from stored procedures
Art S. Kagel (Oninit LLC) wrote: > Lello, Nick wrote: >> >> Returning recordsets from stored procedures - Is this even possible in >> IDS ? >> >> >> >> We have a web application using stored procedures to generate the >> report data into temp tables from where we currently select the data; >> is it possible in IDS to circumvent the need for the temp tables and >> return the data direct from the stored procedure ? >> >> >> >> IDS versions in use are 10.0.FC4 and 11.10.FC2 >> > > Absolutely, or to build the temp table anyway for convenience and return > the data from the SPL proc itself anyway. Just add the WITH RESUME > clause to the RETURN statement inside the FOREACH loop and each time the > procedure is called from a cursor in the calling program it will return > the next data row. > > Art S. Kagel > Oninit >> Hi Art, I'm stuck with the same problem the original poster has to deal with, I guess. We could use the way it's described here: http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls566.htm#wq1622 To avoid the temp table I tried returning the results of the SPL as a LIST row type as decribed in this posting http://groups.google.de/group/comp.databases.informix/browse_thread/thread/4b5995ea90e5465e/001d593d85cfc0d6 but this doesn't seem to work either. Rewriting the SPL to use virtual tables and LIST and MULTISETs result in allocating large amount of memory and finally the database runs out of memory. So are there any workarounds? BTW: I tried both IDS 10 and 11 -- Roland Wintgen (Systemadministrator) rw@evg.de Tel. +49 2166 5508-23 Fax +49 2166 5508-20 EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co. KG Trompeterallee 244-246, 41189 M'nchengladbach Tel. +49 2166 5508-0, Fax +49 2166 5508-90 www.evg.de info@evg.de Sitz: M'nchengladbach, Rechtsform: KG, MG HRA 9281 Komplement'r: Martens & Hamacher Verwaltungsgesellschaft mbH Sitz: M'nchengladbach, MG HRB 2523 Gesch'ftsf'hrer: J'rg R. Martens, Axel Hamacher, Dirk Martens-Ritz
Roland Wintgen wrote:
> Art S. Kagel (Oninit LLC) wrote:
>> Lello, Nick wrote:
>>>
>>> Returning recordsets from stored procedures - Is this even possible
>>> in IDS ?
>>>
>>>
>>>
>>> We have a web application using stored procedures to generate the
>>> report data into temp tables from where we currently select the
>>> data; is it possible in IDS to circumvent the need for the temp
>>> tables and return the data direct from the stored procedure ?
>>>
>>>
>>>
>>> IDS versions in use are 10.0.FC4 and 11.10.FC2
>>>
>>
>> Absolutely, or to build the temp table anyway for convenience and
>> return the data from the SPL proc itself anyway. Just add the WITH
>> RESUME clause to the RETURN statement inside the FOREACH loop and
>> each time the procedure is called from a cursor in the calling
>> program it will return the next data row.
>>
>> Art S. Kagel
>> Oninit
>>>
> Hi Art,
>
> I'm stuck with the same problem the original poster has to deal with,
> I guess. We could use the way it's described here:
> http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls566.htm#wq1622
>
>
> To avoid the temp table I tried returning the results of the SPL as a
> LIST row type as decribed in this posting
> http://groups.google.de/group/comp.databases.informix/browse_thread/thread/4b5995ea90e5465e/001d593d85cfc0d6
>
> but this doesn't seem to work either.
> Rewriting the SPL to use virtual tables and LIST and MULTISETs result
> in allocating large amount of memory and finally the database runs out
> of memory.
> So are there any workarounds?
>
> BTW: I tried both IDS 10 and 11
>
You're all making this harder than you need to. The manual reference
above is a suggestion for returning multiple values from a 'C' or Java
UDR FUNCTION (which are limited to returning a single value) not the
correct way to return multiple row result sets from an SPL PRODEDURE
which is restricted neither as to the number of values it can return in
a single row nor as to the number of rows it can return in a result set.
To expand on my suggestion above, just write:
CREATE PROCEDURE my_dataset_return_procedure ( keyvalue int )
RETURNING int, int, char, char; DEFINE ival1, ival2 int;
DEFINE cval3, cval4 char(20);
FOREACH my_fetch_cursor FOR
SELECT col1, col2, col3, col4
INTO ivar1, ivar2, cvar3, cvar4
FROM some_table
WHERE keycol = keyvalue
...... -- Some code to execute BEFORE returning each row of the
result set.
RETURN ivar1, ivar2, cvar3, cvar4 WITH RESUME; -- This last
clause is the key to returning multiple rows.
{
Now each time the engine calls the procedure after the first time the
procedure will continue at this point and loop back to the top of the
foreach loop after processing any code found at this point. Yes, that
means you can run code AFTER each row is returned here. However, any
code after the last row that is actually fetched and any code after the
end foreach verb will NOT be executed if the calling cursor in your code
is not called repeatedly until SQLNOTFOUND is returned. So if your
cursor loop (as below) has an 'if (numrows_returned >= N) break;'
statement block and there are more than n-1 rows in the result set -
then code in the procedure after the RETURN will not be run for the Nth
row and code after the END FOREACH will not be run at all.
}
END FOREACH;
END PROCEDURE;
Then you can call the procedure from an execute statement in pure SQL,
or you can cast the procedure call to a MULTISET, or in 11.10+ you can
EXECUTE it in the FROM clause of a SELECT statement. In any of these
cases it will return a multi row result set if multiple rows satisfy the
underlying SELECT inside the FOREACH loop. In a host language
application written in say ESQL/C, ODBC, JDBC, Perl DBD/DBI, etc. you
can do any of the above in a CURSOR to handle single rows as they are
returned. So:
EXECUTE PROCEDURE my_dataset_return_procedure( 12 );
SELECT *
FROM (EXECUTE PROCEDURE my_dataset_return_procedure( 12 )) ...;
or from ESQL/C:
EXEC SQL BEGIN DECLARE SECTION;
int ivar1, ivar2;
string cvar3[21], cvar4[21];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE dataset CURSOR FOR EXECUTE PROCEDURE
my_dataset_return_procedure( 12 );
EXEC SQL OPEN dataset;
for (;;) {
EXEC SQL FETCH dataset INTO :ivar1, :ivar2, :cvar3, :cvar4;
if (sqlca.sqlcode != 0)
break;;
.....
}
if (sqlca.sqlcode < 0) {
/* Error handling. */
} else if (sqlca.sqlcode == SQLNOTFOUND) {
/* EOD handling. */
}
...
Art S. Kagel
Oninit
===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.oninit.com/home/disclaimer.php
===========================================================================================
Art S. Kagel (Oninit LLC) wrote:
> Roland Wintgen wrote:
>> Art S. Kagel (Oninit LLC) wrote:
>>> Lello, Nick wrote:
>>>>
>>>> Returning recordsets from stored procedures - Is this even possible
>>>> in IDS ?
>>>>
>>>>
>>>>
>>>> We have a web application using stored procedures to generate the
>>>> report data into temp tables from where we currently select the
>>>> data; is it possible in IDS to circumvent the need for the temp
>>>> tables and return the data direct from the stored procedure ?
>>>>
>>>>
>>>>
>>>> IDS versions in use are 10.0.FC4 and 11.10.FC2
>>>>
>>>
>>> Absolutely, or to build the temp table anyway for convenience and
>>> return the data from the SPL proc itself anyway. Just add the WITH
>>> RESUME clause to the RETURN statement inside the FOREACH loop and
>>> each time the procedure is called from a cursor in the calling
>>> program it will return the next data row.
>>>
>>> Art S. Kagel
>>> Oninit
>>>>
>> Hi Art,
>>
>> I'm stuck with the same problem the original poster has to deal with,
>> I guess. We could use the way it's described here:
>> http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls566.htm#wq1622
>>
>>
>> To avoid the temp table I tried returning the results of the SPL as a
>> LIST row type as decribed in this posting
>> http://groups.google.de/group/comp.databases.informix/browse_thread/thread/4b5995ea90e5465e/001d593d85cfc0d6
>>
>> but this doesn't seem to work either.
>> Rewriting the SPL to use virtual tables and LIST and MULTISETs result
>> in allocating large amount of memory and finally the database runs out
>> of memory.
>> So are there any workarounds?
>>
>> BTW: I tried both IDS 10 and 11
>>
>
> You're all making this harder than you need to. The manual reference
> above is a suggestion for returning multiple values from a 'C' or Java
> UDR FUNCTION (which are limited to returning a single value) not the
> correct way to return multiple row result sets from an SPL PRODEDURE
> which is restricted neither as to the number of values it can return in
> a single row nor as to the number of rows it can return in a result set.
>
> To expand on my suggestion above, just write:
>
> CREATE PROCEDURE my_dataset_return_procedure ( keyvalue int )
> RETURNING int, int, char, char;> DEFINE ival1, ival2 int;
> DEFINE cval3, cval4 char(20);
>
> FOREACH my_fetch_cursor FOR
> SELECT col1, col2, col3, col4
> INTO ivar1, ivar2, cvar3, cvar4
> FROM some_table
> WHERE keycol = keyvalue>
> ...... -- Some code to execute BEFORE returning each row of the
> result set.
>
> RETURN ivar1, ivar2, cvar3, cvar4 WITH RESUME; -- This last
> clause is the key to returning multiple rows.
> {
> Now each time the engine calls the procedure after the first time the
> procedure will continue at this point and loop back to the top of the
> foreach loop after processing any code found at this point. Yes, that
> means you can run code AFTER each row is returned here. However, any
> code after the last row that is actually fetched and any code after the
> end foreach verb will NOT be executed if the calling cursor in your code
> is not called repeatedly until SQLNOTFOUND is returned. So if your
> cursor loop (as below) has an 'if (numrows_returned >= N) break;'
> statement block and there are more than n-1 rows in the result set -
> then code in the procedure after the RETURN will not be run for the Nth
> row and code after the END FOREACH will not be run at all.
> }
> END FOREACH;
>
> END PROCEDURE;
>
> Then you can call the procedure from an execute statement in pure SQL,
> or you can cast the procedure call to a MULTISET, or in 11.10+ you can
> EXECUTE it in the FROM clause of a SELECT statement. In any of these
> cases it will return a multi row result set if multiple rows satisfy the
> underlying SELECT inside the FOREACH loop. In a host language
> application written in say ESQL/C, ODBC, JDBC, Perl DBD/DBI, etc. you
> can do any of the above in a CURSOR to handle single rows as they are
> returned. So:
>
> EXECUTE PROCEDURE my_dataset_return_procedure( 12 );>
> SELECT *
> FROM (EXECUTE PROCEDURE my_dataset_return_procedure( 12 )) ...;>
> or from ESQL/C:
>
> EXEC SQL BEGIN DECLARE SECTION;
> int ivar1, ivar2;
> string cvar3[21], cvar4[21];
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL DECLARE dataset CURSOR FOR EXECUTE PROCEDURE
> my_dataset_return_procedure( 12 );
> EXEC SQL OPEN dataset;
> for (;;) {
> EXEC SQL FETCH dataset INTO :ivar1, :ivar2, :cvar3, :cvar4;
> if (sqlca.sqlcode != 0)
> break;;
> .....
> }
> if (sqlca.sqlcode < 0) {
> /* Error handling. */
> } else if (sqlca.sqlcode == SQLNOTFOUND) {
> /* EOD handling. */
> }
> ...
>
> Art S. Kagel
> Oninit
Thanks Art,
the WITH RESUME keywords are quite clear to me, this works fine, no
problem. However I'm still struggling with the select * from (execute
procedure(...)) syntax and the cast to a MULTISET. Especially if I use
temp table within the SPL to preprocess some calculations.
But I'll give it another try this evening.
--
Roland Wintgen (Systemadministrator)
rw@evg.de
Tel. +49 2166 5508-23
Fax +49 2166 5508-20
EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co. KG
Trompeterallee 244-246, 41189 M'nchengladbach
Tel. +49 2166 5508-0, Fax +49 2166 5508-90
www.evg.de info@evg.de
Sitz: M'nchengladbach, Rechtsform: KG, MG HRA 9281
Komplement'r: Martens & Hamacher Verwaltungsgesellschaft mbH
Sitz: M'nchengladbach, MG HRB 2523
Gesch'ftsf'hrer: J'rg R. Martens, Axel Hamacher, Dirk Martens-Ritz
Roland Wintgen wrote: > Art S. Kagel (Oninit LLC) wrote: > >> Roland Wintgen wrote: >> >>> Art S. Kagel (Oninit LLC) wrote: >>> >>>> Lello, Nick wrote: >> <SNIP> > Thanks Art, > > the WITH RESUME keywords are quite clear to me, this works fine, no > problem. However I'm still struggling with the select * from (execute > procedure(...)) syntax and the cast to a MULTISET. Especially if I use > temp table within the SPL to preprocess some calculations. > But I'll give it another try this evening. Note that the SELECT ... FROM (EXECUTE .... ) syntax ONLY works in IDS 11.10 and later (you haven't stated what version of IDS you are using nor what platform you run on - always a good idea). That syntax is NOT available in any earlier releases of IDS nor in OL 5.xx or Informix SE. However, you can just use the EXECUTE PROCEDURE syntax to return the result set, and, yes, if you must you can use the MULTISET method (scan the CDI archives, the exact syntax has been posted many times) in versions 9.xx and later. As far as temp tables in stored procedures, there are some things to keep in mind when using them: - If the procedure is executed in a cursor and the cursor is not drained (ie fetched until SQLNOTFOUND is returned) then any code following the FOREACH loop in the procedure will, as I stated, not be executed so any code there to DROP the temp table will not be run and the temp table will survive the execution of the procedure. If the caller does not drop the temp table and the procedure is called again, the CREATE TEMP table or any SELECT.... INTO TEMP ... will fail. To solution is to begin the procedure with a DROP TEMP TABLE statement. If the temp table does not already exist this will fail and in the ON EXCEPTION block for the 'Table not found' error you ignore it and continue. - Another problem, creating a temp table within a stored procedure may cause the procedure to recompile itself each time it is executed as it's compile time query plan will be invalid. This will only be a problem for procedures that are time critical or may be executed many times in some loop structure (besides a FETCH loop). Art S. Kagel Oninit =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================
> SELECT * FROM (EXECUTE PROCEDURE my_dataset_return_procedure( 12 )) ...;
Looks like the key I was looking for; I was aware of the RETURN .... WITH RESUME but was stuck on how to receive the data over JDBC (actually using Coldfusion) - this looks like the key I was looking for.
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Roland Wintgen
Sent: 05 February 2008 13:55
To: informix-list@iiug.org
Subject: Re: Returning recordsets from stored procedures
Art S. Kagel (Oninit LLC) wrote:
> Roland Wintgen wrote:
>> Art S. Kagel (Oninit LLC) wrote:
>>> Lello, Nick wrote:
>>>>
>>>> Returning recordsets from stored procedures - Is this even possible
>>>> in IDS ?
>>>>
>>>>
>>>>
>>>> We have a web application using stored procedures to generate the
>>>> report data into temp tables from where we currently select the
>>>> data; is it possible in IDS to circumvent the need for the temp
>>>> tables and return the data direct from the stored procedure ?
>>>>
>>>>
>>>>
>>>> IDS versions in use are 10.0.FC4 and 11.10.FC2
>>>>
>>>
>>> Absolutely, or to build the temp table anyway for convenience and
>>> return the data from the SPL proc itself anyway. Just add the WITH
>>> RESUME clause to the RETURN statement inside the FOREACH loop and
>>> each time the procedure is called from a cursor in the calling
>>> program it will return the next data row.
>>>
>>> Art S. Kagel
>>> Oninit
>>>>
>> Hi Art,
>>
>> I'm stuck with the same problem the original poster has to deal with,
>> I guess. We could use the way it's described here:
>> http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls566.htm#wq1622
>>
>>
>> To avoid the temp table I tried returning the results of the SPL as a
>> LIST row type as decribed in this posting
>> http://groups.google.de/group/comp.databases.informix/browse_thread/thread/4b5995ea90e5465e/001d593d85cfc0d6
>>
>> but this doesn't seem to work either.
>> Rewriting the SPL to use virtual tables and LIST and MULTISETs result
>> in allocating large amount of memory and finally the database runs out
>> of memory.
>> So are there any workarounds?
>>
>> BTW: I tried both IDS 10 and 11
>>
>
> You're all making this harder than you need to. The manual reference
> above is a suggestion for returning multiple values from a 'C' or Java
> UDR FUNCTION (which are limited to returning a single value) not the
> correct way to return multiple row result sets from an SPL PRODEDURE
> which is restricted neither as to the number of values it can return in
> a single row nor as to the number of rows it can return in a result set.
>
> To expand on my suggestion above, just write:
>
> CREATE PROCEDURE my_dataset_return_procedure ( keyvalue int )
> RETURNING int, int, char, char;> DEFINE ival1, ival2 int;
> DEFINE cval3, cval4 char(20);
>
> FOREACH my_fetch_cursor FOR
> SELECT col1, col2, col3, col4
> INTO ivar1, ivar2, cvar3, cvar4
> FROM some_table
> WHERE keycol = keyvalue>
> ...... -- Some code to execute BEFORE returning each row of the
> result set.
>
> RETURN ivar1, ivar2, cvar3, cvar4 WITH RESUME; -- This last
> clause is the key to returning multiple rows.
> {
> Now each time the engine calls the procedure after the first time the
> procedure will continue at this point and loop back to the top of the
> foreach loop after processing any code found at this point. Yes, that
> means you can run code AFTER each row is returned here. However, any
> code after the last row that is actually fetched and any code after the
> end foreach verb will NOT be executed if the calling cursor in your code
> is not called repeatedly until SQLNOTFOUND is returned. So if your
> cursor loop (as below) has an 'if (numrows_returned >= N) break;'
> statement block and there are more than n-1 rows in the result set -
> then code in the procedure after the RETURN will not be run for the Nth
> row and code after the END FOREACH will not be run at all.
> }
> END FOREACH;
>
> END PROCEDURE;
>
> Then you can call the procedure from an execute statement in pure SQL,
> or you can cast the procedure call to a MULTISET, or in 11.10+ you can
> EXECUTE it in the FROM clause of a SELECT statement. In any of these
> cases it will return a multi row result set if multiple rows satisfy the
> underlying SELECT inside the FOREACH loop. In a host language
> application written in say ESQL/C, ODBC, JDBC, Perl DBD/DBI, etc. you
> can do any of the above in a CURSOR to handle single rows as they are
> returned. So:
>
> EXECUTE PROCEDURE my_dataset_return_procedure( 12 );>
> SELECT *
> FROM (EXECUTE PROCEDURE my_dataset_return_procedure( 12 )) ...;>
> or from ESQL/C:
>
> EXEC SQL BEGIN DECLARE SECTION;
> int ivar1, ivar2;
> string cvar3[21], cvar4[21];
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL DECLARE dataset CURSOR FOR EXECUTE PROCEDURE
> my_dataset_return_procedure( 12 );
> EXEC SQL OPEN dataset;
> for (;;) {
> EXEC SQL FETCH dataset INTO :ivar1, :ivar2, :cvar3, :cvar4;
> if (sqlca.sqlcode != 0)
> break;;
> .....
> }
> if (sqlca.sqlcode < 0) {
> /* Error handling. */
> } else if (sqlca.sqlcode == SQLNOTFOUND) {
> /* EOD handling. */
> }
> ...
>
> Art S. Kagel
> Oninit
Thanks Art,
the WITH RESUME keywords are quite clear to me, this works fine, no
problem. However I'm still struggling with the select * from (execute
procedure(...)) syntax and the cast to a MULTISET. Especially if I use
temp table within the SPL to preprocess some calculations.
But I'll give it another try this evening.
--
Roland Wintgen (Systemadministrator)
rw@evg.de
Tel. +49 2166 5508-23
Fax +49 2166 5508-20
EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co. KG
Trompeterallee 244-246, 41189 Mönchengladbach
Tel. +49 2166 5508-0, Fax +49 2166 5508-90
www.evg.de info@evg.de
Sitz: Mönchengladbach, Rechtsform: KG, MG HRA 9281
Komplementär: Martens & Hamacher Verwaltungsgesellschaft mbH
Sitz: Mönchengladbach, MG HRB 2523
Geschäftsführer: Jörg R. Martens, Axel Hamacher, Dirk Martens-Ritz
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list