[gameprogrammer] Re: Slightly offtopic: SQL Command
- From: Matthew Weigel <unique@xxxxxxxxxxx>
- To: gameprogrammer@xxxxxxxxxxxxx
- Date: Mon, 30 Oct 2006 19:38:15 -0600
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:
