Easysoft ODBC-MySQL Driver User's Guide - Configuration

Configuring the Easysoft ODBC-MySQL Driver

The Easysoft ODBC-MySQL Driver is installed on the computer where your applications are running. ODBC applications access ODBC drivers through the ODBC Driver Manager and a data source. The data source tells the Driver Manager which ODBC driver to load, which MySQL server to connect to and how to connect to it. This chapter describes how to create data sources, use DSN-less connections and configure the Easysoft ODBC-MySQL Driver.

Before setting up a data source, you must have successfully installed the Easysoft ODBC-MySQL Driver.

For Easysoft ODBC-MySQL Driver installation instructions, see Installation.

Chapter Guide

Configuring the Easysoft ODBC-MySQL Driver

This section describes how to configure the Easysoft ODBC-MySQL Driver to connect to a MySQL database by using a data source or a DSN-less connection string. The section assumes you are, or are able to consult with, a database administrator.

Refer to the section relevant to your platform to continue:

Setting Up Data Sources on Unix

There are two ways to set up a data source to your MySQL data:

¯ OR ¯

By default, the Easysoft ODBC-MySQL Driver installation creates a SYSTEM data source named [MYSQL_SAMPLE]. If you are using the unixODBC included in the Easysoft ODBC-MySQL Driver distribution, the SYSTEM odbc.ini file is in /etc.

If you built unixODBC yourself, or installed it from some other source, SYSTEM data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built, it defaults to /usr/local/etc.

If you accepted the default choices when installing the Easysoft ODBC-MySQL Driver, USER data sources must be created and edited in $HOME/.odbc.ini.


Note

To display the directory where unixODBC stores SYSTEM and USER data sources, type odbcinst -j.

By default, you must be logged in as root to edit a SYSTEM data source defined in /etc/odbc.ini.


You can either edit the sample data source or create new data sources.

Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.


Note

Attribute names in odbc.ini are not case sensitive.


The Driver attribute identifies the ODBC driver in the odbcinst.ini file to use for a data source.

When the Easysoft ODBC-MySQL Driver is installed into unixODBC, it places an Easysoft ODBC-MySQL entry in odbcinst.ini. For Easysoft ODBC-MySQL Driver data sources therefore, you need to include a Driver = Easysoft ODBC-MySQL entry.

To configure a MySQL data source, in your odbc.ini file, you need to specify:

For example:

 [MySQL]

 Driver= Easysoft ODBC-MySQL

 Server= my_mysql_hostname

 Database = Employees

 User = my_user

 Password = my_password

 

Environment

The Easysoft ODBC-MySQL Driver must be able to find the following shared objects, which are installed during the Easysoft ODBC-MySQL Driver installation:

By default, this is located in /usr/local/easysoft/unixODBC/lib.

By default, this is located in /usr/local/easysoft/lib.

By default, this is located in /usr/local/easysoft/lib.

You may need to set and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on your operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libessupp.so are located.


Note

The shared object file extension (.so) may vary depending on the operating system (.so, .a or .sl).


Establishing a Test Connection

The isql query tool lets you test your Easysoft ODBC-MySQL Driver data sources.

To test the Easysoft ODBC-MySQL Driver connection

1.  Change directory into /usr/local/easysoft/unixODBC/bin.

2.  Type ./isql.sh -v data_source, where data_source is the name of the target data source.

3.  At the prompt, type an SQL query. For example:

 SQL> select * from mytable;

¯ OR ¯

 Type help to return a list of tables:

 SQL> help

 

Setting Up Data Sources on Windows

To connect an ODBC application on a Windows machine to a Salesforce database:

1.  Open ODBC Data Source Administrator:

 The ODBC Data Source Administrator dialog box is displayed:

Figure 3: The ODBC Data Source Administrator dialog box

2.  Select the User DSN tab to set up a data source that only you can access.

¯ OR ¯

 Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.

3.  Click Add... to add a new data source.

 The Create New Data Source dialog box displays a list of drivers:

Figure 4: The Create New Data Source dialog box

4.  Select Easysoft ODBC-MySQL Driver and click Finish.

 The Easysoft ODBC-MySQL Driver DSN Setup dialog box is displayed:

Figure 5: The Easysoft ODBC-MySQL Driver DSN Setup dialog box

For details of the attributes that can be set on this dialog box, see Attribute Fields.


64-bit Windows

The Easysoft installer program installs both a 32-bit and a 64-bit version of the Easysoft ODBC-MySQL Driver. If you want to use a 64-bit ODBC application, you need to use the 64-bit Easysoft ODBC-MySQL Driver. If you want to use a 32-bit ODBC application, you need to use the 32-bit Easysoft ODBC-MySQL Driver.

There is both a 32-bit and a 64-bit version of ODBC Administrator. The 64-bit ODBC Administrator is located in Control Panel under Administrative tools. To access the 32-bit ODBC Administrator in Windows 7 and earlier, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

On Windows 8, both the 32-bit and 64-bit ODBC Administrator are located in Control Panel under Administrative tools: ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit).

Easysoft ODBC-MySQL Driver data sources created in the 64-bit ODBC Administrator will specify the 64-bit version of the Easysoft ODBC-MySQL Driver. Easysoft ODBC-MySQL Driver data sources created in the 32-bit ODBC Administrator will specify the 32-bit version of the Easysoft ODBC-MySQL Driver.

If you want to create an Easysoft ODBC-MySQL Driver System data source for use with a 64-bit application, use the 64-bit ODBC Administrator. If you want to create an Easysoft ODBC-MySQL Driver System data source for use with a 32-bit application, use the 32-bit ODBC Administrator.

For Easysoft ODBC-MySQL Driver User data sources, it does not matter which version of the ODBC Administrator that you use.


Attribute Fields

This section lists the attributes which can be set for the Easysoft ODBC-MySQL Driver in a table showing:

Attributes which are text fields are displayed as value.

Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".

If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.

For example, in the statement:

DIALECT=1|2|3

the value entered may be "1", "2" or "3".

DSN

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

Interface Value

DSN Dialog Box (Windows)

DSN

odbc.ini file (Unix)

[value]

Connect String

DSN=value

Description

Descriptive text that may be retrieved by certain applications to describe the data source.

Interface Value

DSN Dialog Box (Windows)

Description

odbc.ini file (Unix)

Description=value

Connect String

Not Used

Database

The database to to connect to. For example, to connect to the MySQL sample database set the Database attribute value to Employees.



Interface Value

DSN Dialog Box (Windows)

Database

odbc.ini file (Unix)

Database = value

Connect String

DATABASE=value

User

The user name to use when connecting to MySQL. You must specify a name of a user who has access to the target MySQL server.

To specify the user name in the connection string, use UID rather than User. For more information about specifying Easysoft ODBC-MySQL Driver attributes in the connection string, see DSN-less Connections.



Interface Value

DSN Dialog Box (Windows)

User

odbc.ini file (Unix)

User = value

Connect String

USER=value

Password

The password for the user name.

To specify the password in the connection string, use PWD rather than Password.



Interface Value

DSN Dialog Box (Windows)

Password

odbc.ini file (Unix)

Password = value

Connect String

PASSWORD=value

Server

The host name or IP address of the machine on which the MySQL server is running.



Interface Value

DSN Dialog Box (Windows)

Server

odbc.ini file (Unix)

Server = value

Connect String

SERVER = value

Port

The TCP port that the MySQL server is listening on.

If you are connecting to a default instance that is listening on port 3036, the Port setting can be omitted.



Interface Value

DSN Dialog Box (Windows)

Port

odbc.ini file (Unix)

Port = num

Connect String

PORT=num

IPv6

Set IPv6 to Yes when connecting to a MySQL instance that is listening on an IPv6 address.

By default, IPv6 is OFF (set to Yes), which means that the Easysoft ODBC-MySQL Driver assumes that the target MySQL instance is listening on an IPv4 address.



Interface Value

DSN Dialog Box (Windows)

IPv6

odbc.ini file (Unix)

IPv6 = Yes | No

Connect String

IPV6=Yes | No

SSL Encryption

Whether the Easysoft ODBC-MySQL Driver requests an encrypted connection to MySQL.



Interface Value

DSN Dialog Box (Windows)

SSL Encryption

odbc.ini file (Unix)

Encrypt = Yes | No

Connect String

ENCRYPT = Yes | No

Compress

Whether to use the compressed MySQL client server protocol. To use compression, set Compress to Yes. By default, compression is not used.



Interface Value

DSN Dialog Box (Windows)

Compress

odbc.ini file (Unix)

Compress = Yes | No

Connect String

COMPRESS = Yes | No

Private Key File

The client's private key file. For example, /etc/mysql-ssl/client-key.pem.



Interface Value

DSN Dialog Box (Windows)

Private Key File

odbc.ini file (Unix)

PrivateKeyFile = value

Connect String

PRIVATEKEYFILE = value

Certificate File

The CA certificate file. For example, /etc/mysql-ssl/ca.pem



Interface Value

DSN Dialog Box (Windows)

Database = value

odbc.ini file (Unix)

CertificateFile = filename

Connect String

CERTIFICATEFILE = filename

RSA Cert

The name of the PEM file that contains the RSA public key for using the SHA256 authentication plugin. If you do not set this attribute, the MySQL Server will send its copy of the key to the Easysoft ODBC-MySQL Driver. Use this RSA Cert to avoid this process, if you have a local copy of the key on the client machine.



Interface Value

DSN Dialog Box (Windows)

RSA Cert = value

odbc.ini file (Unix)

RSAKeyFile = filename

Connect String

RSAKeyFile = filename

Trust Cert

Whether the Easysoft ODBC-MySQL Driver tries to validate the server certificate to verify the identity of the MySQL machine. Set Trust Cert to Yes if your MySQL machine is using a self-signed SSL certificate. Otherwise, leave Trust Cert set to No and set the Certificate File flag instead.



Interface Value

DSN Dialog Box (Windows)

Trust Cert

odbc.ini file (Unix)

TrustServerCertificate = Yes | No

Connect String

TRUSTSERVERCERTIFICATE = Yes | No

Text Prep

When ON (set to Yes), the Easysoft ODBC-MySQL Driver use the MySQL Text Protocol for prepared statements. By default, the Easysoft ODBC-MySQL Driver uses the Binary Protocol for prepared statements.



Interface Value

DSN Dialog Box (Windows)

Text Prep

odbc.ini file (Unix)

Text_Prepare = Yes | No

Connect String

TEXT_PREPARE = Yes | No

No Cache

When ON (set to Yes), the number of active statements allowed is limited to 1 (SQL_MAX_CONCURRENT_ACTIVITES set to 1). When OFF (set to No), the number of active statements allowed is unlimited (SQL_MAX_CONCURRENT_ACTIVITES set to 0). By default, No Cache is OFF.



Interface Value

DSN Dialog Box (Windows)

No Cache

odbc.ini file (Unix)

NoCache = Yes | No

Connect String

NOCACHE = Yes | No

Conv To UTF

Whether to convert 8-bit data to UTF-8.



Interface Value

DSN Dialog Box (Windows)

Conv To UTF

odbc.ini file (Unix)

ConvToUTF = Yes | No

Connect String

ConvToUTF = Yes | No

Req Charset

The character set to use for the connection. Set this attribute to 33 (UTF-8) if you are using a UTF-8 database in MySQL 3.51.17-4.x.



Interface Value

DSN Dialog Box (Windows)

Req Charset

odbc.ini file (Unix)

ReqCharset = Value

Connect String

REQCHARSET = Value

Ansi Mode

When ON (set to Yes), the Easysoft ODBC-MySQL Driver sets the SQL mode to ANSI_QUOTES. In this mode, " is treated as an identifier quote character and not as a string quote character.



Interface Value

DSN Dialog Box (Windows)

Ansi Mode

odbc.ini file (Unix)

AnsiMode =Yes |No

Connect String

ANSIMODE = Yes | No

NTLMv2

When ON (set to Yes), the Easysoft ODBC-MySQL Driver .



Interface Value

DSN Dialog Box (Windows)

NTLMv2

odbc.ini file (Unix)

NTLMv2 =Yes |No

Connect String

NTLMV2 = Yes | No

Kerberos

Enable Kerberos if you are using MySQL (as opposed to MariaDB) and want to use Kerberos to authenticate the Easysoft ODBC-MySQL Driver connection.



Interface Value

DSN Dialog Box (Windows)

Kerberos

odbc.ini file (Unix)

Kerberos =Yes |No

Connect String

Kerberos = Yes | No

Windows User

The primary part of the Kerberos Principal, if different to User.



Interface Value

DSN Dialog Box (Windows)

Windows User

odbc.ini file (Unix)

WindowsUser = value

Connect String

WINDOWSUSER = value

Principal

The Kerberos Principal for the machine on which the Easysoft ODBC-MySQL Driver is installed. Specify Principle in the form instance@realm. For example, MYMACHINEFQDN@MYDOMAIN. You only need to specify a Kerberos Principal if you are attempting to use Kerberos with MySQL (as opposed to MariaDB).



Interface Value

DSN Dialog Box (Windows)

Principal

odbc.ini file (Unix)

Principal = value

Connect String

PRINCIPAL = value

Null Datetimes

Use NULL for invalid datetime columns.



Interface Value

DSN Dialog Box (Windows)

Null Datetimes

odbc.ini file (Unix)

NullDatetimes = Yes | No

Connect String

NULLDATETIMES = Yes | No

TimeAsTime

Return MySQL TIME columns as a SQL_TIME type. By default, the Easysoft ODBC-MySQL Driver reports TIME colums as SQL_CHAR.



Interface Value

DSN Dialog Box (Windows)

Not Available

odbc.ini file (Unix)

TimeAsTime = Yes | No

Connect String

TIMEASTIME = Yes | No

DSN-less Connections

In addition to using a data source, you can also connect to a database by using a DSN-less connection string of the form:

SQLDriverConnect(..."DRIVER={Easysoft ODBC-MySQL};

Server=server;UID=user;PWD=password;

Database=database;"...)

where server is the host name or IP address of the machine on which the MySQL instance is running, user and password are a valid operating system user name and password and database isthe MySQL database you want to connect to. You need to use the Easysoft ODBC-MySQL DRIVER keyword to identify the Easysoft ODBC-MySQL Driver.

Other Easysoft ODBC-MySQL Driver attribute settings, as described in Setting Up Data Sources on Unix, can be added to the connection string using the same PARAMETER=value; format. For example, the following connection string connects to a MySQL server that is listening on a non-standard port.

Linux example:

"DRIVER={Easysoft ODBC-MySQL};Server=myhost;UID=myuser;PWD=mypassword;Database=Employees;Port=50001"

Windows example:

"DRIVER={Easysoft MySQL ODBC Driver};Server=myhost;UID=myuser;PWD=mypassword;Database=Employees;Port=50001"