Re: prepared statements with "where current of" failing

Douglas Wilson wrote: > On Fri, 22 Oct 1999 00:36:52 GMT, Gregory King <gking@ibcinc.com> wrote: > > > > > let scratch = "update test_table set the_string = the_string where > >current of curs1" > > prepare exec_stat from scratch > > >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. > > Didn't Informix tell you about the 'cursor_name' (I think) function > mentioned in the 4gl supplement? Look there, there's instructions on > how to use it. I'd say RTFM, but it is a bit hard to find if you don't > know where it is, though I'm sure you could have found this > exact problem with the solution on dejanews or in the CDI archives. No, Informix did NOT mention this -- and strangely enough, I was talking to a second level tech (a first level tech had already punted). I was able to find the documentation for this function in some old release notes for 4GL 6.0, but I now have a question based on those docs. The documentation makes it seem rather trivial to fix, at least at first. They show a piece of code as follows: Old Code: DECLARE c_name CURSOR FOR SELECT ... FOR UPDATE PREPARE p_update FROM "UPDATE SomeTable SET SomeColumn = ? WHERE CURRENT OF c_name" New Code: DECLARE c_name CURSOR FOR SELECT ... FOR UPDATE LET s = "UPDATE SomeTable SET SomeColumn = ? WHERE CURRENT OF ", CURSOR_NAME("c_name") PREPARE p_update FROM s That's not so bad (especially since almost all of our prepares are already done from defined strings). But then they go on to provide a more complete example as follows: LET y = "some_name" LET x = CURSOR_NAME(y) DISPLAY x CLIPPED CALL CURSOR_NAME(y) RETURNING x DISPLAY x CLIPPED DECLARE some_name CURSOR FOR SELECT col01 FROM b31661 FOR UPDATE LET s = "UPDATE b31661 SET col01 = -1 WHERE CURRENT OF ", x PREPARE p_update FROM s Now, can anyone see any reason why they seem to set X twice? Or any reason why they use such a verbose way of doing this, versus the nice, compact way first shown above? > I'm a bit worried about that part about 'no longer being supported', since > its in the supplement, it must be supported (though in a different syntax), > so I wonder if you just got an idiot at tech support (likely), > or if they're really not going to support preparing that sort of statement > (not likely). I will follow-up with tech support on this today and post any interesting information. Gregory King IBC

Just to show that the cursorname function can be called either way. Art S. Kagel Gregory King wrote: > > Douglas Wilson wrote: > > > On Fri, 22 Oct 1999 00:36:52 GMT, Gregory King <gking@ibcinc.com> wrote: > > > > > > > > let scratch = "update test_table set the_string = the_string where > > >current of curs1" > > > prepare exec_stat from scratch > > > > >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. > > > > Didn't Informix tell you about the 'cursor_name' (I think) function > > mentioned in the 4gl supplement? Look there, there's instructions on > > how to use it. I'd say RTFM, but it is a bit hard to find if you don't > > know where it is, though I'm sure you could have found this > > exact problem with the solution on dejanews or in the CDI archives. > > No, Informix did NOT mention this -- and strangely enough, I was talking to a > second level tech (a first level tech had already punted). > > I was able to find the documentation for this function in some old release > notes for 4GL 6.0, but I now have a question based on those docs. > > The documentation makes it seem rather trivial to fix, at least at first. They > show a piece of code as follows: > > Old Code: > DECLARE c_name CURSOR FOR SELECT ... FOR UPDATE > PREPARE p_update FROM > "UPDATE SomeTable SET SomeColumn = ? WHERE CURRENT OF > c_name" > > New Code: > DECLARE c_name CURSOR FOR SELECT ... FOR UPDATE > LET s = "UPDATE SomeTable SET SomeColumn = ? WHERE CURRENT > OF ", CURSOR_NAME("c_name") > PREPARE p_update FROM s > > That's not so bad (especially since almost all of our prepares are already done > from defined strings). But then they go on to provide a more complete example > as follows: > > LET y = "some_name" > LET x = CURSOR_NAME(y) > DISPLAY x CLIPPED > CALL CURSOR_NAME(y) RETURNING x > DISPLAY x CLIPPED > DECLARE some_name CURSOR FOR SELECT col01 FROM b31661 > FOR UPDATE > LET s = "UPDATE b31661 SET col01 = -1 WHERE CURRENT OF ", x > PREPARE p_update FROM s > > Now, can anyone see any reason why they seem to set X twice? Or any reason why > they use such a verbose way of doing this, versus the nice, compact way first > shown above? > > > I'm a bit worried about that part about 'no longer being supported', since > > its in the supplement, it must be supported (though in a different syntax), > > so I wonder if you just got an idiot at tech support (likely), > > or if they're really not going to support preparing that sort of statement > > (not likely). > > I will follow-up with tech support on this today and post any interesting > information. > > Gregory King > IBC