Reusing same Cursor name help (errors 710 and 4371)

I have code that works in Informix 4GL 6.05 and fails spottily in 7.24 (we are doing a conversion). If a table is dropped and recreated, the foreach below fails intermittently with error 710. Error 710 says to reprepare and redeclare. But if I place another prepare and declare after the drop and recreate, I get error 4371 in the recompile. The only solutions I've come up with is to either 1) force the program to stop after the table drop and recreate (not acceptable to customer) or 2) To keep a flag to indicate whether I've done the drop and recreate and have two cursors (the second defined after the drop & recreate) and two different foreach loops. Any ideas? Thank You Randy Finder The skeleton of the code in question is. Main LET statement = select * from table1 where line_num >= ? and line_num <= ? prepare prep1 from statement DECLARE cur1 CURSOR FOR prep1 MENU "USR" COMMAND "choice1" drop table table1 create table table1 (line_num serial not null,line_text char(69)) create index ab_201 on table1(line_num) update statisticscommand "choice2" load from 'line_tmp' insert into table1(line_text)command "choice3" foreach cur_1 using val_1,val_2 into text_record.* output to report(text_record.*) end foreach end menu end main

Randolph Finder wrote: > I have code that works in Informix 4GL 6.05 and fails spottily in 7.24 > (we are doing a conversion). If a table is dropped and recreated, the > foreach below fails intermittently with error 710. Error 710 says to > reprepare and redeclare. But if I place another prepare and declare > after the drop and recreate, I get error 4371 in the recompile. > > The only solutions I've come up with is to either 1) force the program > to stop after the table drop and recreate (not acceptable to customer) > or 2) To keep a flag to indicate whether I've done the drop and > recreate and have two cursors (the second defined after the drop & > recreate) and two different foreach loops. > > Any ideas? > > Thank You > Randy Finder > > The skeleton of the code in question is. > Main > > LET statement = select * from table1 where line_num >= ? and line_num > <= ? > prepare prep1 from statement > DECLARE cur1 CURSOR FOR prep1 > > MENU "USR" > COMMAND "choice1" > drop table table1 > create table table1 (line_num serial not null,line_text char(69)) > create index ab_201 on table1(line_num) > update statistics> command "choice2" > load from 'line_tmp' insert into table1(line_text)> command "choice3" > foreach cur_1 using val_1,val_2 into text_record.* > output to report(text_record.*) > end foreach > end menu > end main It has been a while since I've had the opportunity to use I4GL (and I do miss it), but how about trying PREPARE, DECLARE, FOREACH..., and FREE all within the "choice3" menu option? -- June C. Hunt

To the compiler, declaring a cursor is a bit like declaring (defining) variables: you can only do it once. At run-time, you probably need to FREE the statement and cursor before re-preparing / declaring them. You just need to re-structure your code a bit. Andy naraht@drycas.club.cc.cmu.edu (Randolph Finder) wrote in message news:<8386826b.0312010837.4ad945@posting.google.com>... > I have code that works in Informix 4GL 6.05 and fails spottily in 7.24 > (we are doing a conversion). If a table is dropped and recreated, the > foreach below fails intermittently with error 710. Error 710 says to > reprepare and redeclare. But if I place another prepare and declare > after the drop and recreate, I get error 4371 in the recompile. > > The only solutions I've come up with is to either 1) force the program > to stop after the table drop and recreate (not acceptable to customer) > or 2) To keep a flag to indicate whether I've done the drop and > recreate and have two cursors (the second defined after the drop & > recreate) and two different foreach loops. > > Any ideas? > > Thank You > Randy Finder > > The skeleton of the code in question is. > Main > > LET statement = select * from table1 where line_num >= ? and line_num > <= ? > prepare prep1 from statement > DECLARE cur1 CURSOR FOR prep1 > > MENU "USR" > COMMAND "choice1" > drop table table1 > create table table1 (line_num serial not null,line_text char(69)) > create index ab_201 on table1(line_num) > update statistics> command "choice2" > load from 'line_tmp' insert into table1(line_text)> command "choice3" > foreach cur_1 using val_1,val_2 into text_record.* > output to report(text_record.*) > end foreach > end menu > end main

When you drop the table, new table gets new table id. Thus your prepared statement will fail, since it was prepared on an old table. I believe the best solution is to prepare and declare cursor in your "Choice 3" option. Don't forget to FREE your cursor and prep1 statement after foreach has finished! Gorazd "Randolph Finder" <naraht@drycas.club.cc.cmu.edu> wrote in message news:8386826b.0312010837.4ad945@posting.google.com... > I have code that works in Informix 4GL 6.05 and fails spottily in 7.24 > (we are doing a conversion). If a table is dropped and recreated, the > foreach below fails intermittently with error 710. Error 710 says to > reprepare and redeclare. But if I place another prepare and declare > after the drop and recreate, I get error 4371 in the recompile. > > The only solutions I've come up with is to either 1) force the program > to stop after the table drop and recreate (not acceptable to customer) > or 2) To keep a flag to indicate whether I've done the drop and > recreate and have two cursors (the second defined after the drop & > recreate) and two different foreach loops. > > Any ideas? > > Thank You > Randy Finder > > The skeleton of the code in question is. > Main > > LET statement = select * from table1 where line_num >= ? and line_num > <= ? > prepare prep1 from statement > DECLARE cur1 CURSOR FOR prep1 > > MENU "USR" > COMMAND "choice1" > drop table table1 > create table table1 (line_num serial not null,line_text char(69)) > create index ab_201 on table1(line_num) > update statistics> command "choice2" > load from 'line_tmp' insert into table1(line_text)> command "choice3" > foreach cur_1 using val_1,val_2 into text_record.* > output to report(text_record.*) > end foreach > end menu > end main