Skip navigation links
20.1.4.3 Configuring a Connector/ODBC DSN on Windows »
Section Navigation      [Toggle]

20.1.4.2. Connector/ODBC Connection Parameters

You can specify the parameters in the following tables for Connector/ODBC when configuring a DSN:

Users on Windows can use the Options and Advanced panels when configuring a DSN to set these parameters; see the table for information on which options relate to which fields and check boxes. On Unix and Mac OS X, use the parameter name and value as the keyword/value pair in the DSN configuration. Alternatively, you can set these parameters within the InConnectionString argument in the SQLDriverConnect() call.

Table 20.3. Connector/ODBC DSN Configuration Options

Parameter Default Value Comment
user ODBC The user name used to connect to MySQL.
uid ODBC Synonymous with user. Added in 3.51.16.
server localhost The host name of the MySQL server.
database   The default database.
option 0 Options that specify how Connector/ODBC works. See below.
port 3306 The TCP/IP port to use if server is not localhost.
initstmt   Initial statement. A statement to execute when connecting to MySQL. In version 3.51 the parameter is called stmt. Note, the driver supports the initial statement being executed only at the time of the initial connection.
password   The password for the user account on server.
pwd   Synonymous with password. Added in 3.51.16.
socket   The Unix socket file or Windows named pipe to connect to if server is localhost.
sslca   The path to a file with a list of trust SSL CAs. Added in 3.51.16.
sslcapath   The path to a directory that contains trusted SSL CA certificates in PEM format. Added in 3.51.16.
sslcert   The name of the SSL certificate file to use for establishing a secure connection. Added in 3.51.16.
sslcipher   A list of permissible ciphers to use for SSL encryption. The cipher list has the same format as the openssl ciphers command Added in 3.51.16.
sslkey   The name of the SSL key file to use for establishing a secure connection. Added in 3.51.16.
charset   The character set to use for the connection. Added in 3.51.17.
sslverify   If set to 1, the SSL certificate will be verified when used with the MySQL connection. If not set, then the default behavior is to ignore SSL certificate verification.
readtimeout   The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes. This option works only for TCP/IP connections, and only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_READ_TIMEOUT option of the MySQL Client Library. This option was added in Connector/ODBC 3.51.27.
writetimeout   The timeout in seconds for attempts to write to the server. Each attempt uses this timeout value and there are net_retry_count retries if necessary, so the total effective timeout value is net_retry_count times the option value. This option works only for TCP/IP connections, and only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_WRITE_TIMEOUT option of the MySQL Client Library. This option was added in Connector/ODBC 3.51.27.
interactive   Enables the CLIENT_INTERACTIVE connection option of mysql_real_connect.
prefetch 0

When set to a non-zero value N, causes all queries in the connection to return N rows at a time rather than the entire result set. Useful for queries against very large tables where it is not practical to retrieve the whole result set at once. You can scroll through the result set, N records at a time.

This option works only with forward-only cursors. It does not work when the FLAG_MULTI_STATEMENTS flag is set. It can be used in combination with the FLAG_NO_CACHE option. Its behavior in ADO applications is undefined: the prefetching might or might not occur.

Note

The SSL configuration parameters can also be automatically loaded from a my.ini or my.cnf file.

The option argument is used to tell Connector/ODBC that the client isn't 100% ODBC compliant. On Windows, you normally select options by toggling the check boxes in the connection screen, but you can also select them in the option argument. The following options are listed in the order in which they appear in the Connector/ODBC connect screen.

Table 20.4. Connector/ODBC Option Flags

Flag Name GUI Option Constant Value Description
FLAG_FIELD_LENGTH Do not Optimize Column Width   The client cannot handle that Connector/ODBC returns the real width of a column. This option was removed in 3.51.18.
FLAG_FOUND_ROWS Return Matching Rows 2 The client cannot handle that MySQL returns the true value of affected rows. If this flag is set, MySQL returns “found rows” instead. You must have MySQL 3.21.14 or newer for this to work.
FLAG_DEBUG Trace Driver Calls To myodbc.log   Make a debug log in C:\myodbc.log on Windows, or /tmp/myodbc.log on Unix variants. This option was removed in Connector/ODBC 3.51.18.
FLAG_BIG_PACKETS Allow Big Results 8 Do not set any packet limit for results and bind parameters. Without this option, parameter binding will be truncated to 255 characters.
FLAG_NO_PROMPT Do not Prompt Upon Connect 16 Do not prompt for questions even if driver would like to prompt.
FLAG_DYNAMIC_CURSOR Enable Dynamic Cursor 32 Enable or disable the dynamic cursor support.
FLAG_NO_SCHEMA Ignore # in Table Name 64 Ignore use of database name in db_name.tbl_name.col_name.
FLAG_NO_DEFAULT_CURSOR User Manager Cursors 128 Force use of ODBC manager cursors (experimental).
FLAG_NO_LOCALE Do not Use Set Locale 256 Disable the use of extended fetch (experimental).
FLAG_PAD_SPACE Pad Char To Full Length 512 Pad CHAR columns to full column length.
FLAG_FULL_COLUMN_NAMES Return Table Names for SQLDescribeCol 1024 SQLDescribeCol() returns fully qualified column names.
FLAG_COMPRESSED_PROTO Use Compressed Protocol 2048 Use the compressed client/server protocol.
FLAG_IGNORE_SPACE Ignore Space After Function Names 4096 Tell server to ignore space after function name and before “(” (needed by PowerBuilder). This makes all function names keywords.
FLAG_NAMED_PIPE Force Use of Named Pipes 8192 Connect with named pipes to a mysqld server running on NT.
FLAG_NO_BIGINT Change BIGINT Columns to Int 16384 Change BIGINT columns to INT columns (some applications cannot handle BIGINT).
FLAG_NO_CATALOG No Catalog 32768 Forces results from the catalog functions, such as SQLTables, to always return NULL and the driver to report that catalogs are not supported.
FLAG_USE_MYCNF Read Options From my.cnf 65536 Read parameters from the [client] and [odbc] groups from my.cnf.
FLAG_SAFE Safe 131072 Add some extra safety checks.
FLAG_NO_TRANSACTIONS Disable transactions 262144 Disable transactions.
FLAG_LOG_QUERY Save queries to myodbc.sql 524288 Enable query logging to c:\myodbc.sql(/tmp/myodbc.sql) file. (Enabled only in debug mode.)
FLAG_NO_CACHE Do not Cache Result (forward only cursors) 1048576 Do not cache the results locally in the driver, instead read from server (mysql_use_result()). This works only for forward-only cursors. This option is very important in dealing with large tables when you do not want the driver to cache the entire result set.
FLAG_FORWARD_CURSOR Force Use Of Forward Only Cursors 2097152 Force the use of Forward-only cursor type. In case of applications setting the default static/dynamic cursor type, and one wants the driver to use noncache result sets, then this option ensures the forward-only cursor behavior.
FLAG_AUTO_RECONNECT Enable auto-reconnect. 4194304 Enables auto-reconnection functionality. Do not use this option with transactions, since a auto reconnection during a incomplete transaction may cause corruption. Note that an auto-reconnected connection will not inherit the same settings and environment as the original. This option was added in Connector/ODBC 3.51.13.
FLAG_AUTO_IS_NULL Flag Auto Is Null 8388608

When FLAG_AUTO_IS_NULL is set, the driver does not change the default value of sql_auto_is_null, leaving it at 1, so you get the MySQL default, not the SQL standard behavior.

When FLAG_AUTO_IS_NULL is not set, the driver changes the default value of SQL_AUTO_IS_NULL to 0 after connecting, so you get the SQL standard, not the MySQL default behavior.

Thus, omitting the flag disables the compatibility option and forces SQL standard behavior.

See IS NULL. This option was added in Connector/ODBC 3.51.13.

FLAG_ZERO_DATE_TO_MIN Return SQL_NULL_DATA for zero date 16777216 Translates zero dates (XXXX-00-00) into the minimum date values supported by ODBC, XXXX-01-01. This resolves an issue where some statements will not work because the date returned and the minimum ODBC date value are incompatible. This option was added in Connector/ODBC 3.51.17.
FLAG_MIN_DATE_TO_ZERO Bind minimal date as zero date 33554432 Translates the minimum ODBC date value (XXXX-01-01) to the zero date format supported by MySQL (XXXX-00-00). This resolves an issue where some statements will not work because the date returned and the minimum ODBC date value are incompatible. This option was added in Connector/ODBC 3.51.17.
FLAG_MULTI_STATEMENTS Allow multiple statements 67108864 Enables support for batched statements. This option was added in Connector/ODBC 3.51.18.
FLAG_COLUMN_SIZE_S32 Limit column size to 32-bit value 134217728 Limits the column size to a signed 32-bit value to prevent problems with larger column sizes in applications that do not support them. This option is automatically enabled when working with ADO applications. This option was added in Connector/ODBC 3.51.22.
FLAG_NO_BINARY_RESULT Always handle binary function results as character data 268435456 When set this option disables charset 63 for columns with an empty org_table. This option was added in Connector/ODBC 3.51.26.
FLAG_DFLT_BIGINT_BIND_STR   536870912 Causes BIGINT parameters to be bound as strings. Microsoft Access treats BIGINT as a string on linked tables. The value is read correctly, but bound as a string. This option is used automatically if the driver is used by Microsoft Access.
FLAG_NO_INFORMATION_SCHEMA   1073741824 Tells catalog functions not to use INFORMATION_SCHEMA, but rather use legacy algorithms. The trade-off here is usually speed for information quality. Using INFORMATION_SCHEMA is often slow, but the information obtained is more complete.

To select multiple options, add together their values, using the numbers from the Constant Value column in the table.

Note

From version of MySQL Connector/ODBC 5.1.6 onwards, you can use the flag name directly as a parameter in the connection string, by specifying the flag name without the FLAG_ prefix. So, in addition to using the options parameter with various flags set, it is now possible to use the flags directly as parameters. For example, FIELD_LENGTH, FOUND_ROWS and DEBUG could all be used as parameters.

The following table shows some recommended option values for various configurations.

Configuration Option Value
Microsoft Access, Visual Basic 3
Driver trace generation (Debug mode) 4
Microsoft Access (with improved DELETE queries) 35
Large tables with too many rows 2049
Sybase PowerBuilder 135168
Query log generation (Debug mode) 524288
Generate driver trace as well as query log (Debug mode) 524292
Large tables with no-cache results 3145731
Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Dormakov Nikolay on October 9 2008 10:19am[Delete] [Edit]

Information is absent about where should be situated my.cnf file and what parameters is included to [odbc] part.

"Read parameters from the [client] and [odbc] groups from my.cnf."

Posted by Grigore Dolghin on August 23 2011 2:37am[Delete] [Edit]

"To select multiple options, add together their values."

mmmmright. And the values are...? (Yes, I know I could calculate the power of 2, but you used to have the decimal value listed in the table. Why is that column missing now?)

Posted by todorp na on August 30 2011 5:07am[Delete] [Edit]

"To select multiple options, add together their values."

FLAG_FOUND_ROWS 2
FLAG_BIG_PACKETS 8
FLAG_NO_PROMPT 16
FLAG_DYNAMIC_CURSOR 32
FLAG_NO_SCHEMA 64
FLAG_NO_DEFAULT_CURSOR 128
FLAG_NO_LOCALE 256
FLAG_PAD_SPACE 512
FLAG_FULL_COLUMN_NAMES 1024
FLAG_COMPRESSED_PROTO 2048
FLAG_IGNORE_SPACE 4096
FLAG_NAMED_PIPE 8192
FLAG_NO_BIGINT 16384
FLAG_NO_CATALOG 32768
FLAG_USE_MYCNF 65536
FLAG_SAFE 131072
FLAG_NO_TRANSACTIONS 262144
FLAG_LOG_QUERY 524288
FLAG_NO_CACHE 1048576
FLAG_FORWARD_CURSOR 2097152
FLAG_AUTO_RECONNECT 4194304
FLAG_AUTO_IS_NULL 8388608
FLAG_ZERO_DATE_TO_MIN 16777216
FLAG_MIN_DATE_TO_ZERO 33554432
FLAG_MULTI_STATEMENTS 67108864
FLAG_COLUMN_SIZE_S32 134217728
FLAG_NO_BINARY_RESULT 268435456
FLAG_DFLT_BIGINT_BIND_STR 536870912
FLAG_NO_INFORMATION_SCHEMA 1073741824

I found the values in the sources ;) in installer.h

/*
* Deprecated connection parameters
*/
#define FLAG_FOUND_ROWS 2 /* Access can't handle affected_rows */
#define FLAG_BIG_PACKETS 8 /* Allow BIG packets. */
#define FLAG_NO_PROMPT 16 /* Don't prompt on connection */
#define FLAG_DYNAMIC_CURSOR 32 /* Enables the dynamic cursor */
#define FLAG_NO_SCHEMA 64 /* Ignore the schema defination */
#define FLAG_NO_DEFAULT_CURSOR 128 /* No default cursor */
#define FLAG_NO_LOCALE 256 /* No locale specification */
#define FLAG_PAD_SPACE 512 /* Pad CHAR:s with space to max length */
#define FLAG_FULL_COLUMN_NAMES 1024 /* Extends SQLDescribeCol */
#define FLAG_COMPRESSED_PROTO 2048 /* Use compressed protocol */
#define FLAG_IGNORE_SPACE 4096 /* Ignore spaces after function names */
#define FLAG_NAMED_PIPE 8192 /* Force use of named pipes */
#define FLAG_NO_BIGINT 16384 /* Change BIGINT to INT */
#define FLAG_NO_CATALOG 32768 /* No catalog support */
#define FLAG_USE_MYCNF 65536L /* Read my.cnf at start */
#define FLAG_SAFE 131072L /* Try to be as safe as possible */
#define FLAG_NO_TRANSACTIONS (FLAG_SAFE << 1) /* Disable transactions */
#define FLAG_LOG_QUERY (FLAG_SAFE << 2) /* Query logging, debug */
#define FLAG_NO_CACHE (FLAG_SAFE << 3) /* Don't cache the resultset */
/* Force use of forward-only cursors */
#define FLAG_FORWARD_CURSOR (FLAG_SAFE << 4)
/* Force auto-reconnect */
#define FLAG_AUTO_RECONNECT (FLAG_SAFE << 5)
#define FLAG_AUTO_IS_NULL (FLAG_SAFE << 6) /* 8388608 Enables SQL_AUTO_IS_NULL */
#define FLAG_ZERO_DATE_TO_MIN (1 << 24) /* Convert XXXX-00-00 date to ODBC min date on results */
#define FLAG_MIN_DATE_TO_ZERO (1 << 25) /* Convert ODBC min date to 0000-00-00 on query */
#define FLAG_MULTI_STATEMENTS (1 << 26) /* Allow multiple statements in a query */
#define FLAG_COLUMN_SIZE_S32 (1 << 27) /* Limit column size to a signed 32-bit value (automatically set for ADO) */
#define FLAG_NO_BINARY_RESULT (1 << 28) /* Disables charset 63 for columns with empty org_table */
/*
When binding SQL_BIGINT as SQL_C_DEFAULT, treat it as a string
(automatically set for MS Access) see bug#24535
*/
#define FLAG_DFLT_BIGINT_BIND_STR (1 << 29)
/*
Use SHOW TABLE STATUS instead Information_Schema DB for table metadata
*/
#define FLAG_NO_INFORMATION_SCHEMA (1 << 30)

Add your own comment.