Re: best practice for inserting next token ?

#1

Hi.
First, I'm not sure you can't change the datatype... The code to insert
doesn't exist. If your current datatype is an integer, they would'n probably
need to change any code... But any way:

- You can use sequences
- You could BEGIN WORK; INSERT maximum possible value, get the correct
value; UPDATE the value.
Other sessions should be lock mode wait... and should be waiting on this
transaction. Needs testing/checking
- You could have a table with just one record.... and do a BEGIN WORK;UPDATE
table SET val=val+1; SELECT val FROM table;COMMIT
This is a workaround for not having sequences.

Regards.

On Mon, Jun 14, 2010 at 3:05 PM, Floyd Wellershaus <floyd (AT) fwellers (DOT) com>wrote:

Quote:

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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

#2

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 (AT) iiug (DOT) 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 (AT) fwellers (DOT) com>wrote:

Quote:

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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: best practice for inserting next token ?

comp.databases.informix comp.databases.informix

Re: best practice for inserting next token ? - 06-14-2010 , 09:25 AM

Re: best practice for inserting next token ? - 06-14-2010 , 10:43 AM