Re: best practice for inserting next token ?

Version Floyd, version! If you are using 9.40 or later you can use a SEQUENCE to supply the value for the key. A sequence is monotonically increasing so no two sessions could get the same key value for it. An alternative, if you have an earlier version that does not support SEQUENCEs is to create a table with a single SERIAL column and in a transaction: 1. BEGIN WORK; 2. INSERT INTO dummy_table values (0); 3. Get the inserted serial value - SELECT DBINFO( 'sqlca.sqlerrd[2]') from systables where tabid = 1; -- or using the sqlca structure directly. 4. ROLLBACK WORK; A SERIAL value can't be rolled back, so while the table will still have no rows in it after you are finished, the serial number for the next insert will have been incremented. It's a bit less efficient than using a SEQUENCE would be, but you use what you have. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 14, 2010 at 10:05 AM, Floyd Wellershaus <floyd@fwellers.com>wrote: > Hi, > We have a table whose primary key ( an integer ) acts like a serial token, > but is not a serial datatype. > We have a need to have a piece of code that would randomly insert new rows. > As far as I know, the only way is to select max(token) and then use the > next highest integer in the insert statement. > The problem is we need to make sure that nothing sneeks in and grabs that > token, between the select max and the insert. > The only think i can think of is to lock the table for update before doing > the select in a transaction. > Is there something I'm missing or is there another practice that is used in > these scenarios ? > > No, I can't change the datatype to what it should be, because that means > our developers would have to modify code and they don't do that. < satire > > > Thanks !! > Floyd > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >