[+/-]
This section provides help with common queries and areas of functionality in MySQL and how to use them with Connector/ODBC.
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 21.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:
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 21.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.
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
Add your own comment.