Re: Stored Procedures returning more than 1 values

This was covered about a week ago in this news group -- I sent an answer on 28th February. It was also submitted to Kerry (our noble FAQ editor) for inclusion in a future edition of the FAQ. What I said then applies now, so I risk being repetitive. Those of you who read about stored procedures about a week ago can go to the next article now. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =========================================================================== >From: mastek1@shakti.ncst.ernet.in (Mastek Limited) >Date: Wed, 8 Mar 1995 04:18:37 GMT >X-Informix-List-Id: <news.12044> > >We have been working on the Informix ver 6.0 recently. >We faced a problem while calling a stored Procedure created by Dbaccess >which returns more than one values from a 4gl program. >The same procudure works well when used from dbaccess. > >Please can some body suggest a way by which I can call from a 4gl >program a Stored Procedure which has been created by dbaccess and returns >two or more values. > >I have bet with my colleuges that I will show it to them by 10/03/95 evening >or else I will take them to dinner. If only I could save my pocket. > >Sanjay Mehrotra >mastek1@shakti.ncst.ernet.in =========================================================================== Date: Tue Feb 28 08:29:25 1995 From: johnl (Jonathan Leffler) Subject: Re: calling stored procedures from 4gl >Date: Mon, 27 Feb 1995 21:47:14 -0500 >From: JohnSchore@aol.com >X-Informix-List-Id: <list.5666> > >How do you call a stored procedure from 4gl that returns two values? Thanks. This doesn't seem to be covered in the FAQ and probably should be. If the stored procedure does not return any values or take any arguments, then you simply prepare it and execute it: LET str = "EXECUTE PROCEDURE some_procedure1()" PREPARE p_proc1 FROM str EXECUTE p_proc1 FREE p_proc1 If the stored procedure takes arguments, then you use question marks in place of the arguments and supply the values with the EXECUTE: LET str = "EXECUTE PROCEDURE some_procedure2(?,?,?)" PREPARE p_proc2 FROM str EXECUTE p_proc2 USING variable1, variable2, variable3 FREE p_proc2 If the stored procedure returns values, then you declare a cursor for it: LET str = "EXECUTE PROCEDURE some_procedure3()" PREPARE p_proc3 FROM str DECLARE c_proc3 CURSOR FOR p_proc3 FOREACH c_proc3 INTO record1.* [ ... ] END FOREACH FREE p_proc3 -- Free just this in pre-6.00 versions FREE c_proc3 -- Free this too in 6.00 and later And if the stored procedure takes arguments and returns values, you have to use cursor with OPEN, FETCH and CLOSE: LET str = "EXECUTE PROCEDURE some_procedure4(?,?,?)" PREPARE p_proc4 FROM str DECLARE c_proc4 CURSOR FOR p_proc4 OPEN c_proc4 USING variable1, variable2, variable3 WHILE STATUS = 0 FETCH c_proc4 INTO record1.* IF STATUS != 0 THEN EXIT WHILE END IF [ ... ] END WHILE CLOSE c_proc4 FREE p_proc4 -- Free just this in pre-6.00 versions FREE c_proc4 -- Free this too in 6.00 and later