5.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 Table 5.1, “Connector/ODBC DSN Configuration Options” for information on which options are related 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 5.1 Connector/ODBC DSN Configuration Options

ParameterDefault ValueComment
userODBCThe user name used to connect to MySQL.
uidODBCSynonymous with user. Added in 3.51.16.
serverlocalhostThe host name of the MySQL server.
database The default database.
option0Options that specify how Connector/ODBC works. See Table 5.2, “Connector/ODBC Option Parameters” and Table 5.3, “Recommended Connector/ODBC Option Values for Different Configurations”.
port3306The 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.
rsakey The full-path name of the PEM file that contains the RSA public key for using the SHA256 authentication plugin of MySQL. Added in 5.3.4.
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.
prefetch0

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 option parameter MULTI_STATEMENTS is set. It can be used in combination with the option parameter NO_CACHE. Its behavior in ADO applications is undefined: the prefetching might or might not occur.

no_ssps0

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_pwd0Indicates 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 ALTER USER Syntax for details about password expiration 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 Using Option Files.

The behavior of Connector/ODBC can be modified by using special option parameters listed in Table 5.2, “Connector/ODBC Option Parameters”, specified in the connection string or through the GUI dialog box. Most of the connection parameters also have their own numeric constant values, which can be added up as a combined value for the option parameter for specifying those options. However, the numerical option value in the connection string can only enable, but not disable parameters enabled on the DSN, which can only be overridden by specifying the option parameters using their text names in the connection string.

Note

While the combined numerical value for the option parameter can be easily constructed by addition of the options' constant values, decomposing the value to verify if particular options are enabled can be difficult. We recommend using the options' parameter names instead in the connection string, because they are self-explanatory. Also notice that not every option parameter has a constant value.

Table 5.2 Connector/ODBC Option Parameters

Parameter NameGUI OptionConstant ValueDescription
FOUND_ROWSReturn matched rows instead of affected rows2The client cannot handle when 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.
BIG_PACKETSAllow big result set8Do not set any packet limit for results and bind parameters. Without this option, parameter binding will be truncated to 255 characters.
NO_PROMPTDon't prompt when connecting16Do not prompt for questions even if driver would like to prompt.
DYNAMIC_CURSOREnable Dynamic Cursors32Enable or disable the dynamic cursor support.
NO_SCHEMAIgnore schema in column specifications64Ignore use of database name in db_name.tbl_name.col_name.
NO_DEFAULT_CURSORDisable driver-provided cursor support128Force use of ODBC manager cursors (experimental).
NO_LOCALEDon't use setlocale()256Disable the use of extended fetch (experimental).
PAD_SPACEPad CHAR to full length with space512Pad CHAR columns to full column length.
FULL_COLUMN_NAMESInclude table name in SQLDescribeCol()1024SQLDescribeCol() returns fully-qualified column names.
COMPRESSED_PROTOUse compression2048Use the compressed client/server protocol.
IGNORE_SPACEIgnore space after function names4096Tell server to ignore space after function name and before ( (needed by PowerBuilder). This makes all function names keywords.
NAMED_PIPENamed Pipe8192Connect with named pipes to a mysqld server running on NT.
NO_BIGINTTreat BIGINT columns as INT columns16384Change BIGINT columns to INT columns (some applications cannot handle BIGINT).
NO_CATALOGDisable catalog support32768Forces results from the catalog functions, such as SQLTables, to always return NULL and the driver to report that catalogs are not supported.
USE_MYCNFRead options from my.cnf65536Read parameters from the [client] and [odbc] groups from my.cnf.
SAFEEnable safe options131072Add some extra safety checks.
NO_TRANSACTIONSDisable transaction support262144Disable transactions.
LOG_QUERYLog queries to %TEMP%\myodbc.sql524288Enable query logging to c:\myodbc.sql(/tmp/myodbc.sql) file. (Enabled only in debug mode.)
NO_CACHEDon't cache results of forward-only cursors1048576Do 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.
FORWARD_CURSORForce use of forward-only cursors2097152Force the use of Forward-only cursor type. In cases of applications setting the default static/dynamic cursor type and one wants the driver to use noncache result sets, this option ensures the forward-only cursor behavior.
AUTO_RECONNECTEnable automatic reconnect4194304Enables 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 connection. Added in 3.51.13.
AUTO_IS_NULLEnable SQL_AUTO_IS_NULL8388608

When 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 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.

ZERO_DATE_TO_MINReturn SQL_NULL_DATA for zero date16777216Translates 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.
MIN_DATE_TO_ZEROBind minimal date as zero date33554432Translates 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.
MULTI_STATEMENTSAllow multiple statements67108864Enables support for batched statements. Added in 3.51.18.
COLUMN_SIZE_S32Limit column size to signed 32-bit range134217728Limits 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.
NO_BINARY_RESULTAlways handle binary function results as character data268435456When set, this option disables charset 63 for columns with an empty org_table. Added in 3.51.26.
DFLT_BIGINT_BIND_STR[This option is not on the GUI dialog box]536870912Causes 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. Added in 5.1.3.
NO_INFORMATION_SCHEMADon't use INFORMATION_SCHEMA for metadata1073741824Tells 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. Added in 5.1.7.
INTERACTIVEInteractive Client-Makes the client interactive and uses interactive_timeout instead of wait_timeout. Added in 5.1.7.
CAN_HANDLE_EXP_PWDCan Handle Expired Password-Enables handling of expired password. Added in 5.2.4.
ENABLE_CLEARTEXT_PLUGINEnable Cleartext Authentication-Enables cleartext authentication. Added in 5.1.13 and 5.2.5.
NO_SSPSPrepare statements on the client-Prepares statements on the client instead of the server. Added in 5.2.0.
PREFETCHPrefecth from server by N rows at a time-Specifies the number of rows (N) to prefetch from the server for queries without LIMIT. It turns, for example, SELECT * FROM table into SELECT * FROM table LIMIT 0, N. If the client wants to read more rows than specified in the PREFETCH parameter, the driver runs another query SELECT * FROM table LIMIT N+1, N*2. Added in 5.1.11.

Table 5.3, “Recommended Connector/ODBC Option Values for Different Configurations” shows some recommended parameter settings and their corresponding option values for various configurations:

Table 5.3 Recommended Connector/ODBC Option Values for Different Configurations

ConfigurationParameter SettingsOption Value
Microsoft Access, Visual BasicFOUND_ROWS=1;2
Microsoft Access (with improved DELETE queries)FOUND_ROWS=1;DYNAMIC_CURSOR=1;34
Microsoft SQL ServerCOLUMN_SIZE_S32=1;134217728
Large tables with too many rowsCOMPRESSED_PROTO=1;2048
Sybase PowerBuilderIGNORE_SPACE=1;FLAG_SAFE=1;135168
Query log generation (Debug mode)LOG_QUERY=1;524288
Large tables with no-cache resultsNO_CACHE=1;FORWARD_CURSOR=1;3145728
Applications that run full-table "SELECT * FROM ... " query, but read only a small number (N) of rows from the resultPREFETCH=NNot Applicable