Re: How to get next serial value

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 In Informix you first insert the row then retrieve the serial number assigned to it from the sqlca error handling data structure. The serial number of the last row inserted is in sqlca.sqlerrd[1] in ESQL/C or sqlca.sqlerrd[2] in 4GL. If you are using an insert cursor you will need to FLUSH the cursor after each insert and the serial number will be in the sqlca data structure after the FLUSH not the PUT. If the PUT caused the internal application buffer to be flushed automatically, which should only happen if you PUT multiple rows without FLUSHing or if the individual row is >4K in length, then the sqlca structure after THAT PUT WILL contain the serial number of ONLY the last row FLUSHED. Happy Informixing. Art S. Kagel