Select for update in SPL
Aleksandr Shneyderman could not use SELECT ... FOR UPDATE inside an SPL routine on Informix 7.31; compiling gave syntax error -201. June C. Hunt saw the same on IDS 9.20 and noted that a FOREACH cursor with UPDATE ... WHERE CURRENT OF seems to lock the row automatically. Boris Niyazov reported the same error with an explicit cursor. Serge Rielau suspected a cursor is needed. Not definitively resolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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