Re: prepared statements with "where current of" failing

---- you wrote: > > We recently had to port a piece of 4gl code which works in version 4.10 > to 7.20 where it doesn't. > > Here is a sample piece of code that demonstrates this problem (you can > run this against any database, although you will want to drop the test > table it creates after you are done, as the drop table command never > gets executed as the code dies): > > main > define scratch char(256), a_value integer > > create table test_table (the_key integer, the_string char(1)) > insert into test_table values (1,"A")> > let scratch = "select the_string from test_table where the_key=? for > update" > prepare prep1 from scratch > declare curs1 cursor for prep1 > > let scratch = "update test_table set the_string = the_string where > current of curs1" > prepare exec_stat from scratch > > begin work > let a_value = 1 > open curs1 using a_value > fetch curs1 into scratch > execute exec_stat > commit work > drop table test_table > end main> > The problem is that you get an error -507, Cursor (curs1) not found when > the execute statement is attempted.. Informix verified this against a > number of different 7.X 4GLs, so this is not a specific version or > platform issue. > > Informix also told us that this type of statement is no longer > supported. They suggested that we simply change the execute statement > to be the text of the scratch variable. That is fine in this little > test, but not in the programs that use this sort method, as we have one > program that over 50 of these in the form > > update TABLE set (col1, col2)=(?,?) where current of CURSOR_NAME"> > and they get called from many, many places. > > We looked at the generated code to see why this now fails, and it seems > to have to do with the changes for Global and Local cursors. It looks > like the generates code now uses system-generated tags for the cursors, > which is why it cannnot "find" the referenced cursor (the text in the > prepare doesn't get looked at during compilation, and the curs1 in the > generated code has a new Hex name. > > So, any ideas on ways to approach this? We are considered using Global > Cursors for this one module, but don't know if we are opening a > Pandora's box. > > Any thoughts would be appreciated. Thanks in advance. Have you tried using the -globcurs compiler flag Dude? Or upgrade to 7.30. I think this version is supposed to eliminate this problem again. AB ---------------------------------------------------------------- Get your free email from AltaVista at http://altavista.iname.com