5.2 Connector/ODBC Connection Parameters

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

Initial statement. A statement to execute when connecting to MySQL. In
version 3.51 the parameter is called
stmt. The driver supports the initial
statement being executed only at the time of the initial
connection.

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.

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_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
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 Name

GUI Option

Constant Value

Description

FOUND_ROWS

Return matched rows instead of affected rows

2

The 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_PACKETS

Allow big result set

8

Do not set any packet limit for results and bind parameters. Without
this option, parameter binding will be truncated to 255
characters.

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.

FORWARD_CURSOR

Force use of forward-only cursors

2097152

Force 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_RECONNECT

Enable automatic reconnect

4194304

Enables auto-reconnection functionality. Do not use this option with
transactions,
since an auto-reconnection during a incomplete
transaction may cause corruption. An auto-reconnected
connection will not inherit the same settings and
environment as the original connection. Added in
3.51.13.

AUTO_IS_NULL

Enable SQL_AUTO_IS_NULL

8388608

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.

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.

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.

MULTI_STATEMENTS

Allow multiple statements

67108864

Enables support for batched statements. Added in 3.51.18.

COLUMN_SIZE_S32

Limit column size to signed 32-bit range

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.

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.

DFLT_BIGINT_BIND_STR

[This option is not on the GUI dialog box]

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. Added in
5.1.3.

NO_INFORMATION_SCHEMA

Don't use INFORMATION_SCHEMA for metadata

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. Added in
5.1.7.

INTERACTIVE

Interactive Client

-

Makes the client interactive and uses
interactive_timeout instead of
wait_timeout. Added in 5.1.7.

CAN_HANDLE_EXP_PWD

Can Handle Expired Password

-

Enables handling of expired password. Added in 5.2.4.

ENABLE_CLEARTEXT_PLUGIN

Enable Cleartext Authentication

-

Enables cleartext authentication. Added in 5.1.13 and 5.2.5.

NO_SSPS

Prepare statements on the client

-

Prepares statements on the client instead of the server. Added in 5.2.0.

PREFETCH

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

User Comments

When accessing MySQL via ODBC from Classic ASP, you can get Unicode data when you specify charset=ucs2. If you try charset=utf8, you get garbled characters. The ODBC connection can use ucs2, and the page can still use the UTF-8 codepage and response charset.

PROBLEM 1: Using PowerBuilder (or other tools) you may CRASH when selecting from a text column containing data with 4 byte UTF-8 chars. SOLUTION: Change Character Set in ODBC, use utf8mb4 instead of utf8 (supported in MySql 5.5.3 or greater)

PROBLEM 2: You may select TEXT columns but get empty results back.WORKAROUND: Use substring(text_colname, 1, 10000) and you can get the data but this solution is more of a work around.SOLUTION: In your ODBC settings window, in the Metadata tab, select: Limit column size to signed 32-bit range. You will be able to select TEXT columns! ** Also remember to use the Driver-Specific Parameters in PowerBuilder, use: IGNORE_SPACE=1;FLAG_SAFE=1;