MySQL Last Inserted ID

[Peter_Stallo]

Is there an easy way in MySQL to determine the ID of a record you just inserted? I’m using Xojo web and will have just inserted a master record and then will insert the child data, but would need to know the master record ID so that I can tie them together.

[Albin_Kiland]

Try executing LAST_INSERT_ID() after your INSERT statement: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value

rs = yourDB.SQLSelect("LAST_INSERT_ID")

[Alex_von_Siebenthal]

I am not entitely sure but I think I’ve read somewhere that you can do a SQLSelect instead of a SQLExecute, resulting in a Recordset containing the new row/id. Might be worth a try.

[Albin_Kiland]

@222028:@Alex von Siebenthal

I am not entitely sure but I think I’ve read somewhere that you can do a SQLSelect instead of a SQLExecute, resulting in a Recordset containing the new row/id. Might be worth a try.

I know you can do that in PostgreSQL(I use it a lot) with RETURNING. Not sure about MySQL. Might be possible

[Peter_Stallo]

Thanks for all the suggestions! I’m currently using a SELECT MAX(id) statement on the record ID to get the highest ID (which works), but my concern is that there could possibly be an insert statement by one user that intervenes between the insert of another and the most recent ID would be off.

I’ll try to find out about the SQLSelect. I’ve found that MySQL doesn’t have a lot of the handy functions that other databases have

[Travis_Hill]

Yeah- last_insert_id() is the way to go in the case you mention because it is for the last ID generated for that connection specifically, so you don’t accidentally get the wrong value from something else inserting in the interim…

[David_Cox]

Using the SELECT MAX(id) statement is only failsafe if you can also insert a unique value (e.g. userID of person who added record), then read only that record back.

[Peter_Stallo]

David, I did what you suggested because I had a unique username I could pull from. So, for anyone else who would like an example of this route, this is what I used that appears to be working correctly:

“Select MAX(savedexamsid) as lastid from savedexams where username = '” + Session.username + “’”

thisID = rs.field(“lastid”).integervalue

I may tinker with last_insert_id today as well, just so I’ll remember that it’s available.

[Jean-Yves_Pochez]

using MAX instead of the real last inserted rowid can lead to very difficult to find bugs …

[Tim_Hare]

Does MySQLCommunityServer.GetInsertID not work in this case?