I'm not 100% sure, but this type of programming would seem to be opening the doors for bugs down the road that get difficult to trace and to track. If you are using a multi-threaded app, then you have to make sure that you prevent any mods to the DB after your insert and before your select. If you are using transactions, this would be tantamount to a table-lock, which might not be the most efficient solution. Otherwise you risk that another thread updates the DB or inserts another row before you can do your select to retreive your record.
Instead of using MySQL to generate the unique id, why not generate it yourself? Then you would know ahead of time what value you are inserting. There are different libs that exist that you can D/L to generate the unique id for you, or you can create it yourself using a timestamp & random number generator, or a simple counter synchronized across multiple threads. >From personal experience, I have found that the autonumbering of any DB is mostly useful for the DB to ensure that it has a unique PK, but difficult to use from the programmer's side of thing, b/c it requires an extra DB query to get it every time. Good Luck. Eric <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] You could try creating a function in Java or C/C++ or whatever your preference is that wraps the select statements up for you and returns the insert ID. >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 2/24/04, 11:02:51 AM, David Scott <[EMAIL PROTECTED]> wrote regarding INSERT returning ID: > Hi list peeps.... > In many of my projects I have the need to insert a new record into a > table and then later on in the script update this record, Right now I am > doing this by doing the insert, then immediately doing a Select to > return the latest id (unique id) which I store later for my update. > Is there any way I can do an insert and have it return the unique > auto-assigned id? > Cheers > Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
