Returning recordsets from stored procedures

[Lello, Nick]

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

 

[Art S. Kagel (Oninit LLC)]

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
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================

[Roland Wintgen]

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)

r...@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 in...@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

[Art S. Kagel (Oninit LLC)]

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

[Roland Wintgen]

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.

[Art S. Kagel (Oninit LLC)]

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).

[Lello, Nick]

> 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.

Thanks Art,

--
Roland Wintgen (Systemadministrator)

[Art S. Kagel (Oninit LLC)]

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-l...@iiug.org [mailto:informix-l...@iiug.org] On Behalf Of Roland Wintgen
> Sent: 05 February 2008 13:55
> To: inform...@iiug.org

[Doug Lawry]

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)" <a...@oninit.com> wrote in message
news:mailman.411.120222512...@iiug.org...