Select for update in SPL

I am trying to put a promotable lock on a table row inside a stored procedure and for some reason SPL does not allow me to use SELECT FOR UPDATE ... Any suggestions? (Informix 7.31) Thanks, Alex

Aleksandr Shneyderman wrote: > I am trying to put a promotable lock on a table row inside a stored > procedure and for some reason SPL does not allow me to use SELECT FOR UPDATE > ... > > Any suggestions? (Informix 7.31) Post the code - I'm pretty sure SPL does allow FOR UPDATE. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Aleksandr Shneyderman wrote: > I am trying to put a promotable lock on a table row inside a stored > procedure and for some reason SPL does not allow me to use SELECT FOR UPDATE > ... > > Any suggestions? (Informix 7.31) I'm using IDS 9.20 for this test and was unable to successfully use SELECT...FOR UPDATE as well. I've tried several methods and each time got a simple -201 (A syntax error has occurred.) error any time I tried to use FOR UPDATE. After reading up on it, I found the following paragraph in the Guide to SQL: Tutorial (reading for IDS 9.x) with regard to using the FOREACH loop: The WHERE CURRENT OF clause in the UPDATE statement updates only the row on which the cursor is currently positioned. The clause also automatically sets an update cursor on the current row. An update cursor places an update lock on the row so that no other user can update the row until your update occurs. I haven't found anything that states 'FOR UPDATE' cannot be used within an SPL routine, but none of the examples that I've seen shows it in use. Using FOREACH to define a cursor and using the WHERE CURRENT OF clause in the update will, apparently, automatically, give you the end results of locking one row for update. If anyone knows of anything that I've missed or may not have thought of to try, please let me know. -- June Hunt

Well, I was sure too before I wrote that: -- place a promotable lock on a record BEGIN WORK; SET ISOLATION TO REPEATABLE READ; SELECT my_field INTO dummy FROM my_table WHERE my_id = given_id FOR UPDATE; SET ISOLATION TO COMMITTED READ; ...... ...... COMMIT; Compiling the procedure gave me 201 syntax error (runs ok if I remove FOR UPDATE) Then I tried explicit update cursor: BEGIN WORK; SET ISOLATION TO REPEATABLE READ; FOREACH my_cursor SELECT my_field INTO dummy FROM my_table WHERE my_id = given_id FOR UPDATE EXIT FOREACH; END FOREACH; SET ISOLATION TO COMMITTED READ; The same 201 error :-( Sigh. "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:jNdXc.13511$3O3.7105@newsread2.news.pas.earthlink.net... > Aleksandr Shneyderman wrote: > > > I am trying to put a promotable lock on a table row inside a stored > > procedure and for some reason SPL does not allow me to use SELECT FOR UPDATE > > ... > > > > Any suggestions? (Informix 7.31) > > Post the code - I'm pretty sure SPL does allow FOR UPDATE. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

I suspect you will need to use cursor. Cheers Serge