Re: 4GL and Stored Procedures

johnstoni@brinson.com wrote: > > Is there a simple way to execute a stored procedure with parameters > that > returns values from a 4GL program. I can prepare a statement with > parameters, execute it with. EXECUTE prepare statement USING > parameters > > But cannot obtain the return values. > > Alternatively I can prepare a non-parameter execution of a procedure > that > returns values and use a cursor with FOREACH loop to obtain the > returned > values. > > Can I do both? You've worked out the first part, any PREPARED statement that returns values must be executed with a CURSOR. The second part is to choose the right CURSOR commands. Try: PREPARE pre_xxx FROM string DECLARE sel_xxx CURSOR FOR pre_xxx OPEN sel_xxx USING params WHILE TRUE FETCH sel_xxx INTO variables IF SQLCA.SQLCODE = NOTFOUND THEN EXIT WHILE END IF : END WHILE In later versions of 4GL (4.14/6.02?), I believe FOREACH can now also be used with a USING clause. Check your release notes. So you can say something like: FOREACH sel_xxx USING params INTO variables : END FOREACH Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+