[gameprogrammer] Re: Slightly offtopic: SQL Command

Kevin Jenkins wrote:
> If I use the SQL command INSERT to insert a row into a table which has a
> primary key with the serial property, to automatically generate a
> per-row unique ID, how do I get the ID of the thing I just inserted?
> Should I immediately query for the newest ID?

It varies by database.

First, there's the obvious race condition (I do an insert, you do an
insert, I query the table to find the newest ID and get yours) that you
have to avoid.  Second, ANSI SQL does not define any kind of serial
property, so the property itself (and how to deal with problems like
this) are thus implemented by each database distinctly.

For MySQL, you'd use
  SELECT LAST_INSERT_ID()

For Transact-SQL (used by SQL Server and Sybase), you'd use
  SELECT SCOPE_IDENTITY()

For PostgreSQL, you'd use
  SELECT CURRVAL('tablename_columnname_seq')

For others, I'm not sure.
-- 
 Matthew Weigel


---------------------
To unsubscribe go to http://gameprogrammer.com/mailinglist.html

Other related posts: