Sign in to follow this  

[DB] Getting the ID of the record you just created

This topic is 3557 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

If I use a DAO pattern, I might have method Horse createHorse(Horse horse), which does an INSERT. A Horse has an id, but the DB will automatically allocate this during the INSERT. But I want the createHorse method to return the Horseobject with it's allocated ID. I don't know if I can do the insert and then simply ask the DB for the largest ID, because what if two calls to createHorse happen at the same time from different users? It must be a standard thing... what's the answer?

Share this post


Link to post
Share on other sites
For security reasons, you should have all DB access go through a stored procedure. I assume what ever DB you're using supports them.


DECLARE @SystemID uniqueidentifier
SET @SystemID = NEWID()

INSERT <your new record>

SELECT @SystemID


Happy coding.

Share this post


Link to post
Share on other sites
SPs aren't necessarily allowed. Plus I'm working on a quick prototype so it's not the fastest way to do things.

Share this post


Link to post
Share on other sites
Depends on the database in question. In MySQL you can use the mysql_insert_id() function. In SQL Server it's scope_identity().

insert into [..] values (..); select scope_identity()/mysql_insert_id()

Both are safe in the case of simultaneous inserts.

Share this post


Link to post
Share on other sites
Sign in to follow this