Re: Return values ???

Try instead of declare crs_sql cursor for "execute procedure ..." to use: prepare sCrsSql from "execute procedure abc(?)" declare crs_sql cursor for sCrsSql It should work. Best Regards, Octav On Mon, Aug 09, 1999 at 05:25:13PM +0800, Wu wrote: > Below is the listing of my procedure and my 4gl module ... Hope u can find out > what's wrong with it ??? thanx ... > > My Procedure > create procedure abc (p_scn char(4)) > returning int ;> > define p_rows integer ; > > select count(*) into p_rows from man_hdr > where scn matches p_scn ;> > return p_rows ; > end procedure ; > > My 4gl module > database mandev > > main > define l_dummy char(4), l_row integer > > declare csr_sql cursor for "execute procedure abc (?)" > |________________________________________________________^ > | > | A grammatical error has been found on line 6, character 58. > | The construct is not understandable in its context. > | See error number -4373. > open csr_sql using l_dummy > fetch csr_sql into l_row > close csr_sql > > display l_row > end main > > > Octav Chiriac wrote: > > > Could you post the 4gl .err file fragment with the > > indication of what is wrong. > > > > On Mon, Aug 09, 1999 at 04:56:58PM +0800, Wu wrote: > > > With this way, I have compilation error -4373 ... > > > > > > Octav Chiriac wrote: > > > > > > > DECLARE qCursorName CURSOR FOR "EXECUTE PROCEDURE theprocedure(?,?)" > > > > > > > > OPEN qCursorName USING var1, var2 > > > > > > > > WHILE TRUE > > > > FETCH qCursorName INTO result1, result2 > > > > IF sqlca.sqlcode == NOTFOUND THEN > > > > EXIT WHILE > > > > END IF > > > > > > > > -- DO SOMETHING WITH YOUR results > > > > > > > > END WHILE > > > > > > > > CLOSE qCursorName > > > > > > > > FREE ... stuff > > > > > > > > Of course if you know the procedure will return only one result set > > > > you can exclude the while loop and use just one FETCH. > > > > > > > > And to return more than one result set your procedure should > > > > return values WITH RESUME. > > > > > > > > Hope this helps, > > > > Octav > > > > > > > > On Mon, Aug 09, 1999 at 04:07:53PM +0800, Wu wrote: > > > > > How to accept the return values from a stored procedure in i4gl module ? > > > > > Appreciate if example is given. thanx. > > > > > > > > > > -- Octav Chiriac Phone: (373) 2 22 99 67 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 22 84 88 Moldova, Republic of mailto:com@netinfo-moldova.com

Octav Chiriac wrote: > [...various variations on PREPARE and DECLARE...] > OPEN qCursorName USING var1, var2 > > WHILE TRUE > FETCH qCursorName INTO result1, result2 > IF sqlca.sqlcode == NOTFOUND THEN > EXIT WHILE > END IF > > -- DO SOMETHING WITH YOUR results > > END WHILE > > CLOSE qCursorName > > FREE ... stuff It is much better to use a FOREACH loop because it is more reliable: PREPARE p_stmt FROM "EXECUTE PROCEDURE theprocedure(?,?)" DECLARE c_stmt FROM p_stmt FOREACH c_stmt USING var1, var2 INTO result1, result2 ...Do Something With Your Results... END FOREACH FREE c_stmt FREE p_stmt Don't forget to release both the statement and the cursor. And do use FOREACH rather than ever writing out the loop from spare parts (OPEN, WHILE, FETCH, CLOSE). This has been available since at latest the 4.14/6.02 release pair, and possibly as early as the 4.12/6.00 release pair. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>