Re: How to get next serial value

In article <340a6716.1325636@gate.idg.no>, Nils Myklebust <Nils.Myklebust@idg.no> writes >rory@inetnebr.com (Rory Reynoldson) wrote: > >: >:Quick question about Informix's serial datatypes (I'm used to working with >:Oracle sequences). >: >:In Oracle, I would have a table OTAB (fld1 number) and sequence OTAB_SEQ >:and insert as: >: insert into OTAB (fld1) values (OTAB_SEQ.nextval); >: >:In Informix, I would have a table ITAB (fld1 serial) and insert as: >: insert into ITAB (fld1) values (0); >: >:Both give me unique numbers... >: >:But, in Oracle if I want the unique number before the insert I would do: >: select OTAB_SEQ.nextval from dual; >:and the insert becomes >: insert into OTAB (fld1) values (:some_var); >: >:Is there a similar thing to do in Informix? Basically I want the unique >:number without inserting and then reselecting to retrieve the unique number >:(We got lazy under Oracle:).. > >There is no sequence number generator in Informix that's separate from >the serial datatype. You have at least two options: > >1. You can insert first and then either use the sqlca record if it's >available in whatever language you use, or use dbinfo in a select >statement to retreive the last serial value inserted in your session. >This is the best way if there is a column that shall contain a unique >numeric value over all rows in the table. That's the main purpose of >the serial datatype. > >2. You can use the mechanism in 1. to implement a sequence generator >using a table that contains nothing but one column of type serial. You >insert into this table first and retreive the serial number as above. >You can delete all rows in this table after every insert if you want,>so the table never grows. This can all be easily implemented as a >stored procedure so it would work more or less exactly like your >description of the Oracle unique number generator. But you then need to make sure that two processes don't pick up the same number with (almost) concurrent reads, which would mean using LOCKs in such a way that the whole application doesn't end up with lots of hangs waiting for the process with the lock to release it. Just letting Informix put on the number & then picking it up solves this problem. -- Sally Woolrich My Email address has been altered to limit junk mail. Please remove the second 'x' in the company name to Email me.