How to get last inserted ID from auto_increment table?

[jurekf]

Hi,

How do I do this? I'm using MySQL and I'm trying something similar to the following:

INSERT INTO worlditems(...) VALUES(...); SELECT LAST_INSERT_ID()

but while it works when run directly against the database, it fails in smartfox's DbManager.

I need the LAST_INSERT_ID because an entry in another table needs to refer to it.

[appels]

run a count query before your insert query and use the id as a value in that insert query

[jurekf]

That's no good, if another thread is accessing your database you can end up with the wrong Id. It also adds an unecessary query.

Already found the correct solution, bypassing the DbManager and going straight to java.sql gives the full API which has built-in functionality for this sort of thing.

[Flappi282]

Try this

SELECT * FROM tablename ORDER BY primarykeyidfield DESC LIMIT 1;

That will return the last row inserted. You can get the ID etc from that

[BigFIsh]

Try..

SELECT LAST_INSERT_ID() AS LASTID;

[jurekf]

Try this

SELECT * FROM tablename ORDER BY primarykeyidfield DESC LIMIT 1;

That will return the last row inserted. You can get the ID etc from that

No, if another connection/thread adds a row between your select query and your insert you will get the wrong ID. It also adds an extra SELECT which is unnecessary.

SELECT LAST_INSERT_ID() AS LASTID;

This actually should work, except that the LAST_INSERT_ID() is per Connection, and in SmartFox the database Connection is actually shared among separate threads; is this correct? I would need additional synchronization locks every time there's an insert, with a possibility for error should anyone forget to add the lock.

Maybe I'll just wrap it in an accessor that locks or something.

[BigFIsh]

SmartFox the database Connection is actually shared among separate threads; is this correct?

Correct, it is shared among separate threads.

Maybe I'll just wrap it in an accessor that locks or something.

Yea, that probably would be an ideal solution.