Re: Serial database

db wrote: > > Hi, > I am using the serial datatype to generate factless keys. It is > sometimnes necessary to retrieve the value; I have been doing this using > the following SQL code: > > select factless_key_field from informix:entity_name where> factless_key_field = factless_key_field. > > This works for some entities, however it for one of the entities it > writes the new record and increments the factless key but returns the > value of the highest factless key which was oringinally loaded into the > database, rather than the value of the new factless_key just generated. Assuming your tool is ESQL-C or 4GL the serial number just added is returned in the sqlca structure immediately after the INSERT/PUT/FLUSH statement that actually INSERTED the row (PUT to an INSERT CURSOR may not actually add the row but writes to a buffer the FLUSH actually writes the row to the database). This is sqlca.sqlerrd[1] in ESQL-C or sqlca.sqlerrd[2] in 4GL. After a FLUSH the serial number for the last row added is reported so if you need each serial and use an INSERT CURSOR you must FLUSH after each PUT. For other tools, I posted a stored procedure solution to this problem last week for someone using ODBC of JDBC or some such. Art S. Kagel