Re: Select for update in SPL

OK - the first one (no FOREACH) was always doomed to failure - the FOR UPDATE clause can't be tacked onto a singleton SELECT in ESQL/C (-33039). Careful scrutiny of the manual (IDS 9.4 Guide to SQL: Syntax, ct1sqna.pdf, p3-27ff) shows that the notation FOREACH <cursorname> FOR <SELECT statement> automatically applies the 'FOR UPDATE' option -- you do not code it manually -- and you can subsequently use it in an UPDATE statement. This is why I say RTF(abulous)M -- the information is in there, in the section where you'd expect to find it (SPL FOREACH statement). I couldn't remember the ins and outs of the notation; I had to RTFM, and the answer was there. The keyword FOR between the cursor name and the SELECT is necessary; the keyword(s) FOR UPDATE must be missing. Translating your example: BEGIN WORK; SET ISOLATION TO REPEATABLE READ;FOREACH my_cursor FOR SELECT my_field INTO dummy FROM my_table WHERE my_id = given_id EXIT FOREACH; END FOREACH; SET ISOLATION TO COMMITTED READ;ROLLBACK WORK; Well, you probably don't want the rollback work there, but I'm dubious about a procedure which does BEGIN WORK and does not contain the matching COMMIT or ROLLBACK. However, that's fine mechanics; you do need a transaction in progress in a logged database before you open the cursor for update. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" "Boris Niyazov" <niyazov@law.columbia.edu> wrote on 08/26/2004 09:01:12 AM: > 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: > > 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. sending to informix-list

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. Appreciate your help. - Boris "Jonathan Leffler" <jleffler@us.ibm.com> wrote in message news:cgl998$l1v$1@news.xmission.com... > > OK - the first one (no FOREACH) was always doomed to failure - the FOR > UPDATE clause can't be tacked onto a singleton SELECT in ESQL/C (-33039). > > Careful scrutiny of the manual (IDS 9.4 Guide to SQL: Syntax, ct1sqna.pdf, > p3-27ff) shows that the notation FOREACH <cursorname> FOR <SELECT > statement> automatically applies the 'FOR UPDATE' option -- you do not > code it manually -- and you can subsequently use it in an UPDATE > statement. This is why I say RTF(abulous)M -- the information is in > there, in the section where you'd expect to find it (SPL FOREACH > statement). I couldn't remember the ins and outs of the notation; I had > to RTFM, and the answer was there. The keyword FOR between the cursor > name and the SELECT is necessary; the keyword(s) FOR UPDATE must be > missing. > > Translating your example: > BEGIN WORK; > SET ISOLATION TO REPEATABLE READ;> FOREACH my_cursor FOR SELECT my_field INTO dummy FROM my_table WHERE my_id > = given_id > EXIT FOREACH; > END FOREACH; > SET ISOLATION TO COMMITTED READ;> ROLLBACK WORK; > > Well, you probably don't want the rollback work there, but I'm dubious > about a procedure which does BEGIN WORK and does not contain the matching > COMMIT or ROLLBACK. However, that's fine mechanics; you do need a > transaction in progress in a logged database before you open the cursor > for update. > > -- > Jonathan Leffler (jleffler@us.ibm.com) > STSM, Informix Database Engineering, IBM Data Management > 4100 Bohannon Drive, Menlo Park, CA 94025 > Tel: +1 650-926-6921 Tie-Line: 630-6921 > "I don't suffer from insanity; I enjoy every minute of it!" > > > > "Boris Niyazov" <niyazov@law.columbia.edu> wrote on 08/26/2004 09:01:12 > AM: > > > 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: > > > 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. > > > sending to informix-list

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/