Re: Generating portable SQL primary key sequences

Generating IDs can be broken down into two cases:

perform an action before the insert (call a sequence, generator or pull the next value from a table)
perform an action after the insert (generally by asking the driver for a property -- 'mysql_insertid' -- or calling another statement to fetch a value -- 'SELECT @@IDENTITY')

So the easiest way I've found to do this is create pre/post actions for the insert and register certain generative capabilities with certain drivers. (This is how we do it in SPOPS.) If you're using PostgreSQL/Oracle/FirebirdSQL you need to fetch the next value from a sequence/generator. And if you're using MySQL/Sybase you need to create an INSERT using all fields except the ID field, then get the ID afterwards.

IME trying to be portable with something like this tends to get you in trouble, since most people already have some scheme for doing this that works quickly, atomically and reliably.

Best of luck,

Chris
M-x auto-bs-mode

Comment on Re: Generating portable SQL primary key sequences

Replies are listed 'Best First'.
Re^2: Generating portable SQL primary key sequences by iburrell (Chaplain) on Jun 22, 2004 at 22:07 UTC
The databases I am familiar with that use sequences support getting the previous value. With PostgreSQL it is: `SELECT currval('table_key_seq')` [download] With Oracle it is: `SELECT table_key_seq.currval FROM dual` [download] Both of them can use sequences for the default value on the primary key. This makes the usage model the same as MySQL or SQL Server of insert the row without the key, and fetch the just-inserted value of the key.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: Generating portable SQL primary key sequences
by iburrell (Chaplain) on Jun 22, 2004 at 22:07 UTC

SELECT currval('table_key_seq')
[download]

SELECT table_key_seq.currval FROM dual
[download]

[reply]
[d/l]
[select]