RE: Return values ???

Posted in 1999

Barnes, Steve (IFMG) — Tue, 10 Aug 1999 07:11:58 -0400 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEE321.29EE0726 Content-Type: text/plain; charset="iso-8859-1" Jonathan, I am curious as to why a FOREACH is considered better than OPEN, WHILE, FETCH, CLOSE. I always thought that for large numbers of reads, the overhead of a FOREACH instead of the alternative had performance impacts. This was the thought a few years ago, and maybe I am behind the times! Can you please clarify this for me please. Thanks Steve -----Original Message----- From: Jonathan Leffler [mailto:jleffler@earthlink.net] Sent: Monday, August 09, 1999 11:59 PM To: informix-list@iiug.org Subject: Re: Return values ??? 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> ------_=_NextPart_001_01BEE321.29EE0726 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: Return values ???</TITLE> </HEAD> <BODY> Jonathan, I am curious as to why a FOREACH is = considered better than OPEN, WHILE, FETCH, CLOSE. I always thought that = for large numbers of reads, the overhead of a FOREACH instead of the = alternative had performance impacts. This was the thought a few years = ago, and maybe I am behind the times! Can you please clarify this for me = please. Thanks Steve <UL><UL> <A NAME=3D"_MailData">-----Original = Message-----</A> From:   Jonathan Leffler = [<A = HREF=3D"mailto:jleffler@earthlink.net">mailto:jleffler@earthlink.net</A>= ] Sent:   Monday, August 09, 1999 11:59 PM To:     informix-list@iiug.org Subject:       = Re: Return values ??? Octav Chiriac wrote: > [...various variations on = PREPARE and DECLARE...] > OPEN qCursorName USING var1, = var2 > > WHILE TRUE >  FETCH qCursorName INTO = result1, result2 >  IF sqlca.sqlcode =3D=3D = 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 <A HREF=3D"http://www.perl.com/CPAN"; = TARGET=3D"_blank">http://www.perl.com/CPAN</A>; #include <disclaimer.h> </UL></UL>@

RE: Return values ???

Related threads

Your privacy choices