How do I get the last insert id from a database using a ODBC connection?
I'm looking for a solution similar to the mysql_insert_id() function.
+2
A:
If you're using databases with PHP I strongly recommend using PDO (simple database wrapper for a lot of common database engines, more and more supported all the time, part of PHP canon), and hence use PDO::lastInsertId if your database supports the equivalent of mysql_insert_id.
Don't use "SELECT max(id) FROM table;" as it can result in seriously freaky and hard-to-find bugs later on.
* **UPDATE : Ok, you're using ODBC, and I suspect you're after odbc_cursor. I still stand by the strong recomendation to use PDO, as it has an ODBC driver. (ODBC in my eyes is an grumpy bitter old man who mumbles under his breath driving his truck that's falling apart, as the hip and effective PDO guys race past in their sexy VOLVO S90's)
AlexanderJohannesen
2009-04-23 13:37:20
Question updated to reflect OP's response to comments.
epochwolf
2009-04-23 13:40:10
+1 as almost 100% spot on on this answer, except it's Volvo S40s ;).
Sohnee
2010-02-25 14:35:10
A:
It depends on the database type, but look into the SEQUENCE syntax for your rdbm.
inxilpro
2009-04-23 13:41:59
+1
A:
Are you inserting rows into your database via PHP? If so, perhaps you can generate a unique primary key using uniqid() - then you will know the ID without having to query the database.
If it is not possible to update the key type, perhaps you can still insert a unique id when you do the inserts, so you could do a query like this:
SELECT id FROM mytable WHERE rowid = '$myuniqueid'
That way you're ensuring that you're pulling back the correct ID from the database - a much better solution than MAX(id).
BrynJ
2009-04-23 13:52:46
A:
Using
SELECT max(id) FROM table;
should be fine from inside a transaction, or does ODBC not support transactions?
Keep in mind that IDs aren't always auto_increment. There could also be systems when if we delete a row then the next inserted row will have a smaller ID.Also, as you mention, this would only work from inside a transaction.
Michael
2010-08-13 02:54:17
A:
If you have MySQL under ODBC - you can use the next query:
"SELECT LAST_INSERT_ID( );"
It have to be executed mmediately after executing INSERT-query.
For other database - use other specific queries...
Denjs
2010-06-02 12:40:05
A:
I used "SELECT @@IDENTITY AS LastID", while working with PHP/MSSQL, through ODBC. That brought some issues under SQL 2005 server (or was it 2000?). Either way if you do like this:
SET NOCOUNT ON
[NOW INSERT YOUR INSERT SQL QUERY]
SELECT @@IDENTITY AS LastID
you should be just fine in any MSSQL server version.
"SET NOCOUNT ON" will prevent sql server from sending messages like '1 rows inserted', which will keep your script working properly.