Re: Semaphore Locking Operation IN INFORMIX OnLine 7.1 Stored Procedure

In addition to Stefan's excelent answer: Did you check out the Informix SERIAL type. For sequences that you want to increase by 1 this is probably the best solution. We use it for the obvious purpose of providing a column in a table that is unique and increases by 1 for every insert (you give a value of 0 in the insert). We also use it for creating other sequences (invoice numbers where one table can contain many such sequences is an example) where we use a table for each sequence with nothing but a serial column. We insert into this to get the next available value to use in another insert into the real table. There is the issue of how to get hold of the value of such a serial column from the last insert. That has been answered resently and several times earlier. Basically you use the dbinfo function in sql or a stored procedure or the sqlca record in ESQL/C, 4GL and other languages where you have direct access to it. Stefan <stefan@weideneder.de> wrote: :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 Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org