How to get last inserted row's id?

[Betalord]

I am doing a simple INSERT using ZQuery and after I do insert I need the id (it's auto increment field in the data base) because I need it with the next UPDATE command. How can I get id of last inserted line?

[jeremicm]

http://dev.mysql.com/doc/refman/5.0/en/ ... -insert-id

[Betalord]

Yeah that works but I have to do SELECT to get it. I was wondering if there is some direct way to do it? What if something happens between INSERT and SELECT (like someone else inserts another line and increases id)?

[jeremicm]

never done that but in theory, you should write lock table, execute insert, execute last_insert_id and then write unlock table...

that's only theory...


look at http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html for more details...

[Betalord]

Hm actually I read that last inserted id is "local" to the connection, meaning that if someone else increases it from another connection it won't change in current connection. Which means my approach is safe. However I was hoping ZeosLib provides some kind of feedback (result) from MySQL when inserting lines, but maybe it's just MySQL that doesn't support that?

[mdaems]

You can do it automa(t)(g)ically when you're inserting into a 'select' dataset.
1. Open a 'select id,... from <table_to_insert_into> where 1=1' TZQuery (the where condition doesn't have to retrieve rows at all)
2. Append
3. Fill fields
4. Post
5. Read fieldbyname('id').AsInteger

This way the generic resolver executes retrieval of last_insert_id by using the native libmysql function.

Mark