Re: Semaphore Locking Operation IN INFORMIX OnLine 7.1 Stored Procedure

Hi Sean, Sean wrote: > = > I am involved in the migrations of an Oracle 7.x application > to an Informix Online 7.1 Database. One of the requirements is > for implementing something that acts like an Oracle sequence. > (for those of you who haven=92t dealt with these objects > they behave something like this > Once created: > SELECT <sequence_name>.Nextval > FROM DUAL; Will return the next value of > the sequence an update the next value by an increment > value defined when the sequence is created.) > = > I have figured out a way on paper to do these using a table and > stored procedures Something like this: > = > CREATE TABLE ORA_SEQUENCES ( SEQUENCE_NAME varchar(20), > NEXTVAL INT;> INCREMENT_BY SMALLINT); > = > CREATE PROCEDURE getnextseq( sequence varchar(20)) > RETURNING INT;> DEFINE CURRENTVALUE INT; > DEFINE NEXTVALUE INT; > DEFINE INCR INT; > = -- Usage of an UPDATE CURSOR = FOREACH updcursor FOR > SELECT NEXTVAL, INCREMENT_BY > INTO CURRENTVALUE, INCR > FROM ORA_SEQUENCES > WHERE SEQUENCE_NAME =3D SEQUENCE > -- No For Update in Stored Procedures, but in ESQL/C> = > LET NEXTVALUE =3D CURRENTVALUE + INCR; > -- increment sequence to generate nextvalue > = > UPDATE ORA_SEQUENCES SET NEXTVAL =3D NEXTVALUE > WHERE -- don't use the primary key !!! CURRENT OF updcursor; -- Needs an END FOREACH; > = > COMMIT WORK; -- conclude transaction > = > RETURN CURRENTVALUE; -- send back sequence value > END PROCEDURE > = Update cursor are available for databases using transaction logging only inside of transactions. You will need either a BEGIN WORK before the FOREACH, or you must use an ANSI database. Bye Stefan