Obtaining the value of column that uses AUTO_INCREMENT after an INSERT statement can be achieved in a number of different ways. To obtain the value immediately after an INSERT, use a SELECT query with the LAST_INSERT_ID() function.

For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.

INSERT INTO tbl (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();

If you do not require the value within your application, but do require the value as part of another INSERT, the entire process can be handled by executing the following statements:

INSERT INTO tbl (auto,text) VALUES(NULL,'text');
INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),'text');

Certain ODBC applications (including Delphi and Access) may have trouble obtaining the auto-increment value using the previous examples. In this case, try the following statement as an alternative:

SELECT * FROM tbl WHERE auto IS NULL;

This alternative method requires that sql_auto_is_null variable is not set to 0. See Section 5.1.5, “Session System Variables”.

See also Section 20.9.10.3, “How to Get the Unique ID for the Last Inserted Row”.

Support for the dynamic cursor is provided in Connector/ODBC 3.51, but dynamic cursors are not enabled by default. You can enable this function within Windows by selecting the Enable Dynamic Cursor checkbox within the ODBC Data Source Administrator.

On other platforms, you can enable the dynamic cursor by adding 32 to the OPTION value when creating the DSN.

The Connector/ODBC driver has been optimized to provide very fast performance. If you experience problems with the performance of Connector/ODBC, or notice a large amount of disk activity for simple queries, there are a number of aspects you should check:

For more information on how to set the query timeout on Microsoft Windows when executing queries through an ODBC connection, read the Microsoft knowledgebase document at http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B153756.