Re: Returning recordsets from stored procedures
Hi Everyone.
The following "under-documented" syntax works in earlier IDS versions
including 9.4:
SELECT *
FROM TABLE (FUNCTION spl-udr-name(parameters))
AS table-alias(column-names)
For example:
CREATE PROCEDURE test(number SMALLINT)
RETURNING SMALLINT AS value;
DEFINE value SMALLINT;
FOR value = 1 TO number
RETURN value WITH RESUME;
END FOR
END PROCEDURE;
SELECT * FROM TABLE (FUNCTION test(10)) AS test_alias(test_column);
You may also find this useful:
CREATE TEMP TABLE test_temp (value SMALLINT) WITH NO LOG;
INSERT INTO test_temp EXECUTE PROCEDURE test(10);
Regards,
Doug Lawry
"Art S. Kagel (Oninit LLC)" <art@oninit.com> wrote in message
news:mailman.411.1202225121.20610.informix-list@iiug.org...
> 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