22.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 22.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 Table 22.4, "Connector/ODBC Option Flags" and Table 22.5, "Recommended Connector/ODBC option Values for Different Configurations".
`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. Added in 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. Added in 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.
`no_ssps`	0	In Connector/ODBC 5.2, by default, server-side prepared statements are used. When this option is set to a non-zero value, prepared statements are emulated on the client side, which is the same behavior as in 5.1 and 3.51. Added in 5.2.
`can_handle_exp_pwd`	0	Indicates that the application can deal with an expired password, which is signalled by an SQL state of `08004` ("Server rejected the connection") and a native error code `ER_MUST_CHANGE_PASSWORD_LOGIN` (1862). The connection is "sandboxed", and can do nothing other than issue a `SET PASSWORD` statement. To establish a connection in this case, your application must either use the `initstmt` connection option to set a new password at the start, or issue a `SET PASSWORD` statement immediately after connecting. Once the expired password is reset, the restrictions on the connection are lifted. See Section 13.7.1.1, "`ALTER USER` Syntax" for details about passwordexpiration for MySQL server accounts. Added in 5.2.4.

Note

The SSL configuration parameters can also be automatically loaded from a my.ini or my.cnf file. See Section 4.2.3.3, "Using Option Files".

The option argument is used to tell Connector/ODBC that the client is not 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 22.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 an 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. Added in 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`. Added in 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. Added in 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. Added in 3.51.17.
`FLAG_MULTI_STATEMENTS`	Allow multiple statements	67108864	Enables support for batched statements. Added in 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. Added in 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`. Added in 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, butthe 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

In MySQL Connector/ODBC 5.2, 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, you can 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:

Table 22.5. Recommended Connector/ODBC option Values for Different 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