?
Solved

fetch current rec's AutoNumber ID

Posted on 2003-03-16
4
Medium Priority
?
1,512 Views
Last Modified: 2012-06-27
Hi list
Can anybody tell me how to get the record ID of a newly added record after updating the recordset ?
With MS Access as back end for our web application we used to do
rs.Open "SELECT * FROM table WHERE ID = -1)
rs.AddNew
rs("field1") = value1
rs("field2") = value2
etc
rs.Update

newID = rs("ID")

But this doesn't seem to work with MySQL (4.0.11) and MyODBC (3.51.06)
Any ideas ?

Thanks
ILyas

0
Comment
Question by:iluilyas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
VGR earned 100 total points
ID: 8146455
you can issue a (mySql) query "select last_insert_id();"
Other possibility : query for the same set of fields'values that were inserted, or on a combination of them the smallest possible that should be unique.

MySql doc' :

LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. See section 8.4.3.130 mysql_insert_id().
mysql> SELECT LAST_INSERT_ID();
        -> 195

The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server. If expr is given as an argument to LAST_INSERT_ID(), then the value of the argument is returned by the function, and is set as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences: First create the table:
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value (multi-user safe). You can retrieve the new ID as you would read any normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID() (without an argument) will return the new ID. The C API function mysql_insert_id() can also be used to get the value. Note that as mysql_insert_id() is only updated after INSERT and UPDATE statements, so you can't use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.

http://www.mysql.com/doc/en/Miscellaneous_functions.html
0
 
LVL 2

Assisted Solution

by:sumitamar
sumitamar earned 100 total points
ID: 8149705
SELECT LAST_INSERT_ID() will do the job, but it's evident that one has to use an extra query. if you're connecting it with PHP, you can use a simple function mysql_insert_id() for getting last inserted ID in a PHP program.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question