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

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 should work. See below.
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.
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.
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 checkboxes 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.

FlagnameGUI OptionDescription
FLAG_FIELD_LENGTHDo not Optimize Column WidthThe client cannot handle that Connector/ODBC returns the real width of a column. This option was removed in 3.51.18.
FLAG_FOUND_ROWSReturn Matching RowsThe 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 to get this to work.
FLAG_DEBUGTrace Driver Calls To myodbc.logMake 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_PACKETSAllow Big ResultsDo not set any packet limit for results and bind parameters. Without this option, parameter binding will be truncated to 255 characters.
FLAG_NO_PROMPTDo not Prompt Upon ConnectDo not prompt for questions even if driver would like to prompt.
FLAG_DYNAMIC_CURSOREnable Dynamic CursorEnable or disable the dynamic cursor support.
FLAG_NO_SCHEMAIgnore # in Table NameIgnore use of database name in db_name.tbl_name.col_name.
FLAG_NO_DEFAULT_CURSORUser Manager CursorsForce use of ODBC manager cursors (experimental).
FLAG_NO_LOCALEDo not Use Set LocaleDisable the use of extended fetch (experimental).
FLAG_PAD_SPACEPad Char To Full LengthPad CHAR columns to full column length.
FLAG_FULL_COLUMN_NAMESReturn Table Names for SQLDescribeColSQLDescribeCol() returns fully qualified column names.
FLAG_COMPRESSED_PROTOUse Compressed ProtocolUse the compressed client/server protocol.
FLAG_IGNORE_SPACEIgnore Space After Function NamesTell server to ignore space after function name and before “(” (needed by PowerBuilder). This makes all function names keywords.
FLAG_NAMED_PIPEForce Use of Named PipesConnect with named pipes to a mysqld server running on NT.
FLAG_NO_BIGINTChange BIGINT Columns to IntChange BIGINT columns to INT columns (some applications cannot handle BIGINT).
FLAG_NO_CATALOGNo CatalogForces results from the catalog functions, such as SQLTables, to always return NULL and the driver to report that catalogs are not supported.
FLAG_USE_MYCNFRead Options From my.cnfRead parameters from the [client] and [odbc] groups from my.cnf.
FLAG_SAFESafeAdd some extra safety checks.
FLAG_NO_TRANSACTIONSDisable transactionsDisable transactions.
FLAG_LOG_QUERYSave queries to myodbc.sqlEnable query logging to c:\myodbc.sql(/tmp/myodbc.sql) file. (Enabled only in debug mode.)
FLAG_NO_CACHEDo not Cache Result (forward only cursors)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_CURSORForce Use Of Forward Only CursorsForce 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_RECONNECTEnable auto-reconnect.Enables auto-reconnection functionality. You should 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_NULLFlag Auto Is Null

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_MINReturn SQL_NULL_DATA for zero dateTranslates 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_ZEROBind minimal date as zero dateTranslates 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_STATEMENTSAllow multiple statementsEnables support for batched statements. This option was added in Connector/ODBC 3.51.18.
FLAG_COLUMN_SIZE_S32Limit column size to 32-bit valueLimits 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_RESULTAlways handle binary function results as character dataWhen set this option disables charset 63 for columns with an empty org_table. This option was added in Connector/ODBC 3.51.26.
FLAG_NO_INFORMATION_SCHEMA 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.
FLAG_DFLT_BIGINT_BIND_STR 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.

To select multiple options, add together their values.

Note

From version of MySQL Connector/ODBC 5.1.6 onwards, it is possible to use the flag name directly as a parameter in the connection string, by using 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.

ConfigurationOption Value
Microsoft Access, Visual Basic3
Driver trace generation (Debug mode)4
Microsoft Access (with improved DELETE queries)35
Large tables with too many rows2049
Sybase PowerBuilder135168
Query log generation (Debug mode)524288
Generate driver trace as well as query log (Debug mode)524292
Large tables with no-cache results3145731
Copyright © 2010-2024 Platon Technologies, s.r.o.           Index | Man stránky | tLDP | Dokumenty | Utilitky | O projekte
Design by styleshout