[gameprogrammer] Re: Slightly offtopic: SQL Command
- From: Sami Näätänen <sn.ml@xxxxxxxxxxxx>
- To: gameprogrammer@xxxxxxxxxxxxx
- Date: Tue, 31 Oct 2006 10:51:18 +0300
On Tuesday 31 October 2006 03:38, Matthew Weigel wrote:
> 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')
PostgreSQL at least returns the oid of the inserted tuple.
This of course is unique. This can be used to get any auto_increment etc
stuff whit ease. So check the behaviour of the DB you use. It can
provide some similar way to get that.
PS. How it seams that every time I encounter new DB system PostgreSQL
seams to do things right or better. :) Or maybe I'm a litle bit biased
to PostgreSQL. ;)
---------------------
To unsubscribe go to http://gameprogrammer.com/mailinglist.html
Other related posts:
