Skip navigation links
17.1.7.2 Connector/ODBC Application Specific Tips »
Section Navigation      [Toggle]

17.1.7.1. Connector/ODBC General Functionality

[+/-]

17.1.7.1.1. Obtaining Auto-Increment Values
17.1.7.1.2. Dynamic Cursor Support
17.1.7.1.3. Connector/ODBC Performance
17.1.7.1.4. Setting ODBC Query Timeout in Windows

This section provides help with common queries and areas of functionality in MySQL and how to use them with Connector/ODBC.

17.1.7.1.1. Obtaining Auto-Increment Values

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.3, “Server System Variables”.

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

17.1.7.1.2. Dynamic Cursor Support

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 check box 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.

17.1.7.1.3. Connector/ODBC Performance

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:

  • Ensure that ODBC Tracing is not enabled. With tracing enabled, a lot of information is recorded in the tracing file by the ODBC Manager. You can check, and disable, tracing within Windows using the Tracing panel of the ODBC Data Source Administrator. Within Mac OS X, check the Tracing panel of ODBC Administrator. See Section 17.1.4.8, “Getting an ODBC Trace File”.

  • Make sure you are using the standard version of the driver, and not the debug version. The debug version includes additional checks and reporting measures.

  • Disable the Connector/ODBC driver trace and query logs. These options are enabled for each DSN, so make sure to examine only the DSN that you are using in your application. Within Windows, you can disable the Connector/ODBC and query logs by modifying the DSN configuration. Within Mac OS X and Unix, ensure that the driver trace (option value 4) and query logging (option value 524288) are not enabled.

17.1.7.1.4. Setting ODBC Query Timeout in Windows

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.


User Comments

Posted by Oliver Schonrock on October 12 2011 1:12pm[Delete] [Edit]

be very carefult with this "feature":

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;

for those of us that do not use Access or Delphi, this is a huge trap, because if you do this:

insert into table_a (id, name) values(null, 'jonny');
select * from table_a where id is null;

result
======
id name
1 jonny

this is WRONG!! there is no row with id=null, because column "id" is typically "not null and primary key" as well as "auto_increment", so "primary is null" should never return any ROW! It is just a screwed up feature to be compatible with Access/ODBC.

What is even more crazy is that this works ACROSS tables!

eg

insert into table_b (id, size) values(null, 'big');
select * from table_a where id is null;

result
======
id name
222 somename

where 222 is the id of the new record in "table_b", so this is retrieving what can only be described as a "random row".

This really caught us out because we then proceed to delete the retrieved row...so we were effectively deleting random rows out of the database.

of course you can turn off this madness by

set sql_auto_is_null = 0;

which I suggest EVERYONE who is not using Access/ODBC should do.

Respectfully suggest to mysql devs that this "feature" be OFF by default and documented heavily in connector/ODBC which is the only place it might be useful. For everyone else it is just dangerous.

Add your own comment.