Re: Select for update in SPL

Well, as I wrote, I'd like to put a promotable lock on a table record WITHOUT updating/deleiting it, b/c the logic of the code (update may not occur). I have no problem to do that in 4GL or dbaccess using FOR UPDATE. Your suggestion of using FOREACH to open cursor does not place a promotable lock on the record that I tested using the following create procedure test_lock() returning char(15); define dummy char(15); begin work; set isolation to repeatable read;foreach c_curs for select my_field into dummy from my_table where my_id = 1 return my_field with resume; -- do something that takes a while, so we can check running that procedure -- from another session and see if it gets lock exception exit foreach; end foreach; commit; end procedure Now I run the procedure one after another in 2 different connections and the one that starts later has no problem to put "the promotional lock" on the record even though the first already placed one (was supposed to place the lock). Only when I make the record to be updated, I see that the record is locked. That tells me that FOREACH does not "automatically applies the 'FOR UPDATE"! The example you referencing actually deletes records. Do you know about any other way to place a lock on a record in SPL without actually updating it? Thanks - Boris "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:BFzXc.14628$3O3.12037@newsread2.news.pas.earthlink.net... > Boris Niyazov wrote: > > Thanks Jonathan, > > > > So you're saying that foreach will implicitly open cursor for update - well, > > the manual (I have 7.31 one) does not say it explicitly :-) Anyway, I will > > try to test it. > > The example on p3-25 of 4367.pdf shows a cursor for update in SPL. > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:BFzXc.14628$3O3.12037@newsread2.news.pas.earthlink.net... > Boris Niyazov wrote: > > Thanks Jonathan, > > > > So you're saying that foreach will implicitly open cursor for update - well, > > the manual (I have 7.31 one) does not say it explicitly :-) Anyway, I will > > try to test it. > > The example on p3-25 of 4367.pdf shows a cursor for update in SPL. > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Boris Niyazov wrote: > Well, as I wrote, I'd like to put a promotable lock on a table record > WITHOUT updating/deleiting it, b/c the logic of the code (update may not > occur). I have no problem to do that in 4GL or dbaccess using FOR UPDATE. > Your suggestion of using FOREACH to open cursor does not place a promotable > lock on the record that I tested using the following > [example snipped] > > Now I run the procedure one after another in 2 different connections and the > one > that starts later has no problem to put "the promotional lock" on the record > even though the first already placed one (was supposed to place the lock). > > Only when I make the record to be updated, I see that the record is locked. > That > tells me that FOREACH does not "automatically applies the 'FOR UPDATE"! >

Thanks June. You're right! The solution is to use update ... where current of in the spl code EVEN THOUGH the statement never gets reached due to an IF branch! So if the engine sees the update statement in the procedure code it compiles the procedure creating update cursor, otherwise it creates ordinary one. I guess, its some kind of compiler optimizer.