prepared statements with "where current of" failing
Posted in 1999
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.
Gregory King
IBC