Re: Returning recordsets from stored procedures
Lello, Nick wrote:
>> 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.
>
It will work in the IDS 11.10 instance you say you have at work, but
FROM (subquery) syntax in not supported in earlier releases like the IDS
10.00 you have at home. You can upgrade your home instance to 11.10 and
then try it out.
Art S. Kagel
>
> -----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.
>
>
===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.oninit.com/home/disclaimer.php
===========================================================================================