Cursors...

I have this situation: DEFINE rec_a LIKE table_a.* DEFINE rec_b LIKE table_b.* DECLARE CURSOR cur_a FOR SELECT * FROM table_aFOREACH cur_a INTO rec_a.* <Some elaboration> DECLARE CURSOR cur_b FOR SELECT * FROM table_b WHERE table_b.key = rec_a.key FOREACH cur_b INTO rec_b.* <Some elaboration> END FOREACH END FOREACH rec_a.key is key for table "table_b" When i execute the 2nd FOREACH in rec_b i don't find the right data, but other non-sense data (non-sense relatively to the key I correctly find with the first foreach). If, instead, i use the PREPARE statement to declare the 2nd cursor, i obtain the right values. What does it happen?

Guido Cirilli wrote: > > I have this situation: > > DEFINE rec_a LIKE table_a.* > DEFINE rec_b LIKE table_b.* > > DECLARE CURSOR cur_a FOR > SELECT * FROM table_a> FOREACH cur_a INTO rec_a.* > <Some elaboration> > DECLARE CURSOR cur_b FOR > SELECT * FROM table_b > WHERE table_b.key = rec_a.key > FOREACH cur_b INTO rec_b.* > <Some elaboration> > END FOREACH > END FOREACH> > rec_a.key is key for table "table_b" > When i execute the 2nd FOREACH in rec_b i don't find the right data, > but other non-sense data (non-sense relatively to the key I correctly > find with the first foreach). > If, instead, i use the PREPARE statement to declare the 2nd cursor, i > obtain the right values. > What does it happen? Most likely there is a problem with a global or module record called table_b which is confusing the compiler -- well, actually, it is doing what it is designed to do but it isn't what you wanted. Also, there are at least two points. First, the inner declare should be moved out of the loop; it is wasting energy to include it inside the loop. And second, you should normally be able to combine nested FOREACH loops into a single one. However, the <Some elaboration> sections may make this less sensible. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Jonathan Leffler wrote: > > > If, instead, i use the PREPARE statement to declare the 2nd cursor, i > > obtain the right values. > > What does it happen? > > Most likely there is a problem with a global or module record called > table_b which is confusing the compiler -- well, actually, it is > doing what it is designed to do but it isn't what you wanted. > > Also, there are at least two points. First, the inner declare should > be moved out of the loop; it is wasting energy to include it inside > the loop. And second, you should normally be able to combine > nested FOREACH loops into a single one. However, the <Some > elaboration> sections may make this less sensible. I think if he just moves the second cursor declaration outside the foreach and prepares the statement, then opens the second cursor he should be fine. He needs the nested foreach because for each row of A, there may be multiple rows in B. If you can do that in a single foreach, I'd like to see how. As to his question, lets look at what is happening. Lets say that there are only a couple of rows in table A. After the first itereation, and into the second itereation he is trying to redeclare the same cursor which already exists. What happens with 4GL? Does each declaration become unique even if the name isn't unique? (I don't know but how can the program know which cursor B it has to refer to?) I guess you could close the cursor and then free the cursor, but how efficient is it? (Not very.) Johnathan, are you still in charge of 4GL? Perhaps you could expand on this issue. -Mikey

Mike Segel wrote: > Jonathan Leffler wrote: > > > If, instead, i use the PREPARE statement to declare the 2nd > > > cursor, i obtain the right values. > > > What does it happen? > > > > Most likely there is a problem with a global or module record called > > table_b which is confusing the compiler -- well, actually, it is > > doing what it is designed to do but it isn't what you wanted. > > > > Also, there are at least two points. First, the inner declare > > should be moved out of the loop; it is wasting energy to include > > it inside the loop. And second, you should normally be able to > > combine nested FOREACH loops into a single one. However, the > > <Some elaboration> sections may make this less sensible. > > I think if he just moves the second cursor declaration outside > the foreach and prepares the statement, then opens the second cursor > he should be fine. He needs the nested foreach because for each row > of A, there may be multiple rows in B. If you can do that in a > single foreach, I'd like to see how. Original code: DEFINE rec_a LIKE table_a.* DEFINE rec_b LIKE table_b.* DECLARE CURSOR cur_a FOR SELECT * FROM table_a FOREACH cur_a INTO rec_a.* <Some elaboration> DECLARE CURSOR cur_b FOR SELECT * FROM table_b WHERE table_b.key = rec_a.key FOREACH cur_b INTO rec_b.* <Some elaboration> END FOREACH END FOREACH Proposed, one-cursor alternative - based on what you'd do with ACE in ISQL. DECLARE c CURSOR FOR SELECT A.*, B.* FROM Table_A A, Table_B B WHERE A.Key = B.Key FOREACH c INTO rec_a.*, rec_b.* <some code> END FOREACH Nothing hugely complex. Whether it is a good idea or not depends on many things, including the relative sizes of rows from A and B and the number of rows in B for each row in A. Etc. And also on the amount of work done in the first of the original <Some elaboration> sections. However, my <some code> section could include elements of both the <Some elaboration> sections. If there is filtering done inside the first such section, then that needs to be moved down into the engine if at all possible -- there's no point returning data to the application if the application is going to ignore it. > As to his question, lets look at what is happening. > Lets say that there are only a couple of rows in table A. > After the first itereation, and into the second itereation he is > trying to redeclare the same cursor which already exists. > What happens with 4GL? It might depend on the type of database (MODE ANSI or not), but the newly re-declared cur_b replaces the old declaration. If you try to re-open an already open cursor in a MODE ANSI database, you get an error. However, if the inner loop runs to completion each time, the cursor is closed so there should be no problem. > Does each declaration become unique even if the name isn't unique? No. > (I don't know but how can the program know which cursor B it has > to refer to?) There's only one, so there's no problem. > I guess you could close the cursor and then free the cursor, > but how efficient is it? > (Not very.) The CLOSE happens when the inner FOREACH is terminated. The FREE happens anyway when the cursor is redeclared. There is one less round-trip to the database because of the implicit FREE. However, the cost of having to re-process the SELECT on each iteration (as originally written) probably outweighs the savings from the implicit FREE. And with two loops written out like the original, there is no real room for the optimizer to optimize -- no possibility of nice fancy hash joins or whatever because the optimizer never gets to see the connections between the queries. > Johnathan, are you still in charge of 4GL? Assuming you might be referring to me, Mihke :-), no, I'm not in charge of I4GL in any direct sense. I do have some influence on the product directions (check out http://www.iiug.org/~seiug/) but no day to day 'in charge' responsibilities. > Perhaps you could expand on this issue. Anything more I need to say? The original problem is still unsolved because we don't know enough about the context to determine whether there is a name clash or something else causing trouble. Looking at the generated SQL (eg by looking at the prog.c file) would tell us more about why the prepared version of the cursor works as expected and the unprepared one doesn't, but I really don't want to have to wade through all the code that would take. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>