Re: I4gl query

Jonathan Leffler wrote in message <74mk03$poq$1@news.xmission.com>... > >On Wed, 9 Dec 1998, RIAZJKAPADIA wrote: >> I am writing this program in i4gl version 7.20D. As you can see in the >> program below that my query returns only one value that is no or rows. For >> that I have to declare it as cursor and then open the cursor to get the >> number. Is there any other way by which I can get the no of counts without >> having to declare it as a cursor and then opening it. I tried "EXECUTE >> count_rec INTO no_of_rec" but it is giving me an error saying syntax not >> recognisable > >EXECUTE INTO is not part of the 4.10 SQL syntax, so it gives you errors. >You will have to use a cursor until the 7.3 release is available. > >> DEFINE l_tt_accno integer >> ---------- >> LET sel_stmt = " SELECT COUNT(*) INTO no_of_rec FROM tt_extract", >> " WHERE tt_accno = '", l_tt_accno, > >The INTO clause won't work! It will give -201 errors when the >statement is prepared. You can only use that sort of INTO clause >(as distinct from an INTO TEMP clause, for example) when the >SELECT statement is written direct after the DECLARE statement, >not when it is prepared. > >> ----------- >> PREPARE count_rec FROM sel_stmt >> DECLARE cursor_count CURSOR FOR count_rec >> OPEN cursor_count >> FETCH cursor_count INTO no_of_rec > >This works, but if you're going to do it often, you'd be better off doing: > > DEFINE l_tt_accno integer > LET sel_stmt = "SELECT COUNT(*) FROM tt_extract WHERE tt_accno = ?" > PREPARE count_rec FROM sel_stmt > DECLARE cursor_count CURSOR FOR count_rec > FOREACH cursor_count USING l_tt_accno INTO no_of_rec > EXIT FOREACH > END FOREACH I don't think you can use FOREACH with a prepared statement using place holders! You have to open and fetch the cursor manually. This may well be a version dependant limitation. > >This way, you can reuse the single prepared statement for multiple >different values of l_tt_accno without have to re-prepare it each time. >There's a bit more code needed than I've shown (basically to determine >whether or not the cursor is currently prepared and declared), but it is >book-keeping. You could do the main activity in the body of the FOREACH >loop if you prefer, instead of writing EXIT FOREACH. > >Yours, >Jonathan Leffler (jleffler@informix.com) #include <witticism.h> >Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN >Informix IDN for D4GL & Linux -- http://www.informix.com/idn >