Re: single row select in 4gl

If you get bored with ancient history and/or are not interested in the features in I4GL 7.30 and D4GL 3.00, now is a good time to move on to the next item... On Sat 17 Jul 1999, Steve Wright <steve@wrightnet.demon.co.uk> wrote: >Andras Simon <asimon@math.bme.hu> writes >>Octav Chiriac <com@netinfo-moldova.com> writes: >>> On Sat, Jul 17, 1999 at 08:17:48AM +0200, Andras Simon wrote: >>[...] >>>> let crit = "rowid=1" -- so the result will be unique >> >>>I think with this you will get no rows (most likely). >> >>Just wanted to present the problem as simply as possible. >> >>> You have to declare a cursor on this statement and then make a >>> foreach on it. >> >>I will, if I have to... :-( >> >>What I find a little discomforting about this is that I see nothing in >>the docs about any restriction on EXECUTE .. INTO .. that would apply >>here (e.g. EXECUTE .. USING .. works). Am I missing something about >>EXECUTE? > >I had the same problem about a year ago. The manual said it was >possible, but every attempt I tried ended up with a compilation error. > >We contacted Informix and got the response "You are correct, it doesn't >work. The documentation is wrong, we'll get it changed" > >It would be nice if EXECUTE INTO worked, however DECLARE, OPEN, FETCH >will just have to do :-) And on Sat 17 Jul 1999, Octav Chiriac <com@netinfo-moldova.com> wrote: >On Sat, Jul 17, 1999 at 03:05:26PM +0200, Simon Andr's wrote: >> > Sorry, I didn't found any reference in the docs about an EXECUTE .. INTO >> >> I based my expectation on the entry for EXECUTE, on page 1-281 of >> Informix Guide to SQL: Syntax. Can it be that I'm looking at the wrong >> place? > >I was looking at the Informix Guide to SQL Syntax Version 6.0. > >In the new guide there is a reference to EXECUTE ...INTO but only >for ESQL. > >If you have a specific 4GL Syntax docs, you can see that EXECUTE ..INTO >is not allowed there (at least in 4GL 6.X). At the same time there is >an EXECUTE ... [USING ...] command. > >> > There is of course an EXECUTE PROCEDURE .. INTO, but its use is limited >> > to SPL and ESQL/C. >> >> To be sure, the entry for EXECUTE starts with the ESQL icon; this >> could mean that the whole construct is not usable in 4gl. But if that >> is the case, how come that EXECUTE (and EXECUTE .. USING ..) does work??? There are two sets of versions of I4GL to consider; the new 7.30 release to be available shortly, and everything else (all of them older). The older versions only support the syntax available in the 4.10 SQL and ESQL/C. This means that a number of convenient facilities are not available. Specifically, items like EXECUTE p_stmt INTO is not available; it was not a part of 4.10 ESQL/C. Stored procedures were not in the 4.10 product; therefore you cannot use EXECUTE PROCEDURE, etc. The new 7.30 version does handle these newer constructs. It handles ESQL/C statements like EXECUTE p_stmt INTO correctly -- the following code compiles and produces the correct answer ("<<systables>>"). DATABASE stores MAIN DEFINE x CHAR(18) LET x = "junk" PREPARE p_x FROM "SELECT tabname FROM Systables where tabid = 1" EXECUTE p_x INTO x DISPLAY "<<", x clipped, ">>" END MAIN Newer SQL statements such as EXECUTE PROCEDURE are handled in SQL blocks: SQL EXECUTE PROCEDURE someproc($val1, $val2) END SQL If the procedure returns a single row of data, you can do: SQL EXECUTE PROCEDURE someproc($val1, $val2) INTO $res1, $res2 END SQL If the procedure can return multiple rows, you need to declare a cursor for it: DECLARE c_proc CURSOR FOR SQL EXECUTE PROCEDURE someproc($val1, $val2) INTO $res1, $res2 END SQL FOREACH c_proc ...process res1 and res2... END FOREACH FREE c_proc Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn