RE: Auto Increment ID of Inserted Row
well, that'll teach me not to update my java api bookmarks to 1.4 the getGeneratedKeys() calls works like a charm! Thanks for the help -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:09 PM To: Dan Greene Cc: [EMAIL PROTECTED] Subject: Re: Auto Increment ID of Inserted Row -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan Greene wrote: (newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... Is there a reason you don't hold on to the same connection during the lifespan of one of your web 'transactions'? Also, to avoid a round trip to the server you should use Statement.getGeneratedKeys(). -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/VPjstvXNTca6JD8RAs3RAKC6T+RQnStioRLAJ1cwHHsBMwuPxgCeJU+N co0jO0c6pCDxIwxMAHaHkCk= =Nkgp -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Increment ID of Inserted Row
(newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... previous info- In the last episode (Sep 19), Steven Kreuzer said: What is the SQL to get the created AutoInc ID from a row that I have just inserted? SELECT MAX(id_field) FROM table Nope. If someone else inserted a record between the time you inserted yours and the time you run that select, your answer will be wrong. Use LAST_INSERT_ID(), or whatever construct your language provides for retrieving it without doing another query. -- Dan Nelson [EMAIL PROTECTED] -- Daniel Greene Manager, Software Development Chelsea Interactive [EMAIL PROTECTED] (571)203-4105 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Increment ID of Inserted Row
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan Greene wrote: (newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... Is there a reason you don't hold on to the same connection during the lifespan of one of your web 'transactions'? Also, to avoid a round trip to the server you should use Statement.getGeneratedKeys(). -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/VPjstvXNTca6JD8RAs3RAKC6T+RQnStioRLAJ1cwHHsBMwuPxgCeJU+N co0jO0c6pCDxIwxMAHaHkCk= =Nkgp -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto Increment ID of Inserted Row
What is the SQL to get the created AutoInc ID from a row that I have just inserted? Tim Davis Sunbelt Systems Concepts, Inc [EMAIL PROTECTED] Client/Server Database Programmer/Analyst - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment ID of Inserted Row
At 12:55 -0400 9/19/02, Davis, Tim wrote: What is the SQL to get the created AutoInc ID from a row that I have just inserted? SELECT LAST_INSERT_ID(); Seems like there's an echo in here...this question has been asked about 4 times in the last couple of days. :-) Tim Davis Sunbelt Systems Concepts, Inc [EMAIL PROTECTED] Client/Server Database Programmer/Analyst - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment ID of Inserted Row
SELECT MAX(id_field) FROM table SK On Thursday, September 19, 2002, at 12:55 PM, Davis, Tim wrote: What is the SQL to get the created AutoInc ID from a row that I have just inserted? Tim Davis Sunbelt Systems Concepts, Inc [EMAIL PROTECTED] Client/Server Database Programmer/Analyst - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment ID of Inserted Row
At 13:23 -0400 9/19/02, Steven Kreuzer wrote: SELECT MAX(id_field) FROM table That won't give you the correct answer if some other client gets in there an inserts a row before you have a change to get the MAX() value. LAST_INSERT_ID() is designed for this; it's not affected by whatever other clients are doing. SK On Thursday, September 19, 2002, at 12:55 PM, Davis, Tim wrote: What is the SQL to get the created AutoInc ID from a row that I have just inserted? Tim Davis Sunbelt Systems Concepts, Inc [EMAIL PROTECTED] Client/Server Database Programmer/Analyst - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment ID of Inserted Row
From: Steven Kreuzer [EMAIL PROTECTED] SELECT MAX(id_field) FROM table On Thursday, September 19, 2002, at 12:55 PM, Davis, Tim wrote: What is the SQL to get the created AutoInc ID from a row that I have just inserted? More formally, you can do this: SELECT LAST_INSERT_ID(); --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Auto Increment ID of Inserted Row
I believe this approach work in a single connection environment and it has a potential problem when it is running in a concurrent system. What happens if there is another insert occurred through another connection just before this statement being executed?? The result of this statement would not be id you are expecting. Regards, Michael -Original Message- From: Steven Kreuzer [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 10:24 AM To: Davis, Tim Cc: Mysql (E-mail) Subject: Re: Auto Increment ID of Inserted Row SELECT MAX(id_field) FROM table SK On Thursday, September 19, 2002, at 12:55 PM, Davis, Tim wrote: What is the SQL to get the created AutoInc ID from a row that I have just inserted? Tim Davis Sunbelt Systems Concepts, Inc [EMAIL PROTECTED] Client/Server Database Programmer/Analyst - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment ID of Inserted Row
In the last episode (Sep 19), Steven Kreuzer said: What is the SQL to get the created AutoInc ID from a row that I have just inserted? SELECT MAX(id_field) FROM table Nope. If someone else inserted a record between the time you inserted yours and the time you run that select, your answer will be wrong. Use LAST_INSERT_ID(), or whatever construct your language provides for retrieving it without doing another query. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
