Re: Stored Procedures...
>From: Romulo Albuquerque <bicudo@elogica.com.br>
>Date: Fri, 24 Jan 1997 23:27:59 -0200
>X-Informix-List-Id: <list.12824>
>
> Anyone knows how can I issue an "Select * from table FOR UPDATE" within
>a Store Procedure ?? Every time I try this one, I get an error message... :(
It isn't particularly clear from the manuals (any version of Informix Guide
to SQL: Syntax from 5.0 through 7.2), but this worked for me (OnLine
7.21.UC1):
CREATE TABLE SomeTable(IndexNumber INTEGER NOT NULL);
ALTER TABLE SomeTable ADD CONSTRAINT PRIMARY KEY(IndexNumber)
CONSTRAINT pk_sometable;
INSERT INTO SomeTable VALUES(1);
INSERT INTO SomeTable VALUES(3);
CREATE PROCEDURE for_update() RETURNING INT; DEFINE i INTEGER;
FOREACH a_cursor FOR SELECT IndexNumber INTO i FROM SomeTable
RETURN i WITH RESUME;
UPDATE SomeTable SET IndexNumber = i + 3
WHERE CURRENT OF a_cursor; END FOREACH;
END PROCEDURE
BEGIN WORK;
EXECUTE PROCEDURE for_update();
SELECT * FROM SomeTable;ROLLBACK WORK;
Note that I declared a name for the cursor, and this automatically (I
think) made it a cursor FOR UPDATE.
Obviously, my database has transactions; you don't need the begin/rollback
if your database is not logged, and you don't need the begin if your
database is MODE ANSI.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>