Re: How to write a java stored procedure in Informix?

On Wed, 3 Feb 1999, Ipellew wrote: > 4GL - Esql Q:- > > In 4gl, a foreach inside a foreach can have the cursors prepared and declared > thus:- > > PREPARE t_s from SELECT * FROM systables WHERE tabid < 100 > DECLARE t_c cursor for t_s > PREPARE c_s from SELECT * FROM syscolumns WHERE tabid = tr.tabid > DECLARE c_c cursor for c_s > FOREACH t_c into tr.* > FOREACH c_c into cr.* > whatever > END FOREACH > ENDFOREACH Quotes needed, twice; also in the ESQL/C below. Why not: LET s = "SELECT t.*, c.* FROM SysTables t, SysColumns c", " WHERE t.tabid < 100 AND t.tabid = c.tabid", " ORDER BY t.tabid, c.colno" PREPARE t_s FROM s FOREACH t_s INTO tr.*, cr.* whatever END FOREACH I'm ignoring my usual advice to use 'informix'.systables instead of just systables because of MODE ANSI databases. > This has everything the compiler needs (tr.tabid) to open cursor c_c. > > In Esql, however, there must be a way to do the same functionality. My current > method is:- > > $PREPARE t_s from SELECT * FROM systables WHERE tabid < 100; > $DECLARE t_c cursor for t_s; > $PREPARE c_s from SELECT * FROM syscolumns WHERE tabid = ?; > $DECLARE c_c cursor for c_s; > $OPEN t_c; > while (!SQLCODE) { > $FETCH t_c into rt.*; if (SQLCODE) break; > $OPEN using tr.tabid; /* compilation error above -- use line below */ $OPEN c_c USING tr.tabid; > while (!SQLCODE) { > $FETCH c_c into cr.*; if (SQLCODE) break; > whatever; > } $CLOSE c_c; > } $CLOSE t_c; $FREE c_c; $FREE c_s; $FREE t_c; $FREE t_s; > Here the compiler has no notion of what the ? is. This must force the > open to a lot more work to form the correct select statement. It is > inside a loop, so I wonder what the overheads are compared to the 4gl > version! Nil. The ESQL/C generated by I4GL is essentially identical to the amended ESQL/C. > The Esql manual(s) is quiet specific in its examples:- > Programmers manual V 7.2 P.10.7 gives two types of prepare > 1. sprintf(sp_call, "SELECT *\\ > FROM %s:syscolumns\\ > WHERE tabname = %s", dbsB, rt.tabname); > Here the declare would have to go inside the first while, and declares > are expensive on system resources, are they not!. Yes, so do it your way, if you must use two loops; or use a single SELECT statement. The engine can do a better job optimizing for you if you give it more information to work on. > 2. sprintf(sp_call, "SELECT *\\ > FROM %s:syscolumns\\ > WHERE tabid = ?", dbsB); > > > I guess my question is:- > When inside an iteration, what is the method to get a cursor open > using known variable. As you showed. Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN LET s = "SELECT t.*, c.* FROM SysTables t, SysColumns c", " WHERE t.tabid < 100 AND t.tabid = c.tabid", " ORDER BY t.tabid, c.colno" PREPARE t_s FROM s FOREACH t_s INTO tr.*, cr.* whatever END FOREACH Informix IDN for D4GL & Linux -- http://www.informix.com/idn