Re: INSERT returning ID
Look into the mysql_insert_id function, that looks like what you want. On Feb 24, 2004, at 12:02 PM, David Scott wrote: 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT returning ID
At 17:02 + 2/24/04, David Scott wrote: 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? If you mean, can you have the insert statement itself return the ID, no. Insert statements don't return records. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT returning ID
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]
Re: INSERT returning ID
but there are ways to get this value. The Java API handles this case just fine, and I'd be willing to bet other API's do it as well. here's some simple code to do with the Java API: String sql = some insert sql statement; Statement stmt = getConnection().createStatement(); stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); setId(rs.getInt(1)); -- this is the auto_generated row id stmt.close(); Paul DuBois wrote: At 17:02 + 2/24/04, David Scott wrote: 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? If you mean, can you have the insert statement itself return the ID, no. Insert statements don't return records. -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT returning ID
At 10:25 -0700 2/24/04, Jeff Mathis wrote: but there are ways to get this value. The Java API handles this case just fine, and I'd be willing to bet other API's do it as well. I know that, but that isn't what he asked. here's some simple code to do with the Java API: String sql = some insert sql statement; Statement stmt = getConnection().createStatement(); stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); setId(rs.getInt(1)); -- this is the auto_generated row id stmt.close(); Paul DuBois wrote: At 17:02 + 2/24/04, David Scott wrote: 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? If you mean, can you have the insert statement itself return the ID, no. Insert statements don't return records. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT returning ID
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]
