Re: I4gl query

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 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