Re: STORED PROCEDURE CONTROL
>Date: Fri, 20 Dec 1996 09:40:22 -0800
>From: johnl@informix.com (Jonathan Leffler)
>X-Informix-List-Id: <list.12462>
>
>Within an SP, you treat the EXECUTE PROCEDURE statement as if it was a
>SELECT statement, using the implicit FETCH which is part of the FOREACH>loop to retrieve each record in turn.
>
>In ESQL/C (or other external-to-database program), you treat the EXEC PROC
>like a SELECT statement, declaring a cursor for it, etc.
It has been suggested that this response was too cryptic.
I apologize. I'm not sure that I really want to spend the time
being verbose, though. Oh well, here goes.
-- Stored Procedure which typically returns numerous rows...
CREATE PROCEDURE p1 () RETURNING INTEGER, CHAR(18); DEFINE xtabid INTEGER; DEFINE xtabname CHAR(18);
FOREACH SELECT TabId, TabName
INTO xtabid, xtabname
FROM 'informix'.SysTables
WHERE TabId >= 100
ORDER BY TabName
RETURN xtabid, xtabname WITH RESUME;
END FOREACH;
END PROCEDURE;
Using stored procedure p1() inside another stored procedure p2():
CREATE PROCEDURE p2() RETURNING INT, INT, CHAR(18); DEFINE i, n INTEGER; DEFINE s CHAR(18);
LET i = 0;
FOREACH EXECUTE PROCEDURE p1() INTO n, s
LET i = i + 1;
RETURN i, n, s WITH RESUME;
END FOREACH;
END PROCEDURE;
In ESQL/C, you use:
EXEC SQL BEGIN DECLARE SECTION;
int i;
char n[18];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c CURSOR FOR EXECUTE PROCEDURE p1();
EXEC SQL OPEN c;
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c INTO :i, :n;
}
EXEC SQL CLOSE c;
EXEC SQL FREE c;
I've not tested the ESQL/C for syntax, let alone to ensure that it works,
but the gist should be clear, I hope, to anyone who codes in ESQL/C. If
you work in I4GL, NewEra, or whatever, you'll have to adapt the ESQL/C
code.
This seems a long-winded way of saying what I said first time, but ...
Merry Christmas and a Happy New Year to all of you who read c.d.i.
I'm on holiday until early January.
Yours,
Jonathan Leffler (johnl@informix.com) #include <happy/christmas.h>
>}From: rnath@wrbc.com
>}Date: Fri, 20 Dec 1996 01:39:36 GMT
>}X-Informix-List-Id: <news.31872>
>}
>}I am trying to write a stored procedure that will return multiple rows
>}of data to either a program (delphi) or another stored procedure.
>}With this multiple return I need to use the FOREACH functionality
>}along with 'WITH RESUME'. What is the controlling mechanism to get
>}another record. In other words does it act like a cursor with get
>}next type capabilities etc. Thank you in advance for your help with
>}this issue.
>}
>}Rick Nath
>}rnath@wrbc.com
>}rnath@dakota.net