MySQL Connector/ODBC 8.0 on Linux

Oracle's MySQL server can be connected and used with ODBC driver in a similar fashion that we have discussed for Microsoft's MsSQL server in the previous post. For that, we will use the latest MySQL Connector/ODBC 8.0 driver by building and installing it from the source.

Therefore, the procedure can be used for various SBCs with different architectures. An advantage for this approach is that we can use the same C++ code for both MsSQL server and MySQL server just by changing the DSN. At first, following tools need to be installed in the machine.

sudo apt install mysql-client libmysqlclient-dev
sudo apt install unixodbc unixodbc-dev
sudo apt install libgtk-3-dev cmake
sudo apt install libssl-dev

1. Getting the Source
2. Building and Installing
3. DSN
4. References

Getting the Source

Connector/ODBC can be obtained at the following link. As we want to build it from the source, choose 'Source Code' in the 'Select Operating System' drop down box and choose 'Generic Linux (Architecture Independent)' in the 'Select OS Version' drop down box.

https://dev.mysql.com/downloads/connector/odbc/8.0.html

Figure 1. Download Connector/ODBC

Extract the file and make a directory to build.

$ tar zxvf ~/Downloads/mysql-connector-odbc-8.0.13-src.tar.gz
$ cd mysql-connector-odbc-8.0.13-src/
$ mkdir build
$ cd build

Building and Installing

We can build and install the the downloaded source using cmake as follows. [Ora18a]။

$ cmake .. -G "Unix Makefiles" -DWITH_UNIXODBC=1
$ make
$ sudo make install

In case, there is an error in finding MySQL headers and libraries, you can define the paths like

$ export MYSQL_INCLUDE_DIR=/usr/local/mysql/include
$ export MYSQL_LIB_DIR=/usr/local/mysql/lib
$ export MYSQL_DIR=/usr/local/mysql

Thereafter, the following libraries can be found in /usr/local/lib.

1. libmyodbc8a.so
2. libmyodbc8w.so
3. libmyodbc8S.so

libmyodbc8a.so, libmyodbc8w.so, and libmyodbc8S.so are for ASCII driver, Unicode driver, setup respectively. You can also use the following command to find the libraries.

$ find / -name 'lib*odbc*.so'

DSN

To register ODBC driver [Coo18a, Ora18b], /etc/odbcinst.ini can be edited as follow.

$ sudo nano /etc/odbcinst.ini

When it is opened, fill up the following, save the changes, and exit by pressing ctrl+o and ctrl+x.

[myodbc8a]
Description     = Connector/ODBC 8.0 ANSI Driver DSN
Driver          = /usr/local/lib/libmyodbc8a.so
Setup           = /usr/local/lib/libmyodbc8S.so

[myodbc8w]
Description     = Connector/ODBC 8.0 UNICODE Driver DSN
Driver          = /usr/local/lib/libmyodbc8w.so
Setup           = /usr/local/lib/libmyodbc8S.so

Then, ODBC Data Source Name (DSN) can be defined in /etc/odbc.ini. Enter

$ sudo nano /etc/odbc.ini

and fill the DSNs as shown below.

[ODBC Data Sources]
myodbc8w     = MyODBC 8.0 UNICODE Driver DSN
myodbc8a     = MyODBC 8.0 ANSI Driver DSN

[myodbc8w]
Driver       = /usr/local/lib/libmyodbc8w.so
Description  = Connector/ODBC 8.0 UNICODE Driver DSN
SERVER       = localhost
PORT         = 3306
USER         =
Password     =
Database     = mytest
OPTION       = 3
SOCKET       =

[myodbc8a]
Driver       = /usr/local/lib/libmyodbc8a.so
Description  = Connector/ODBC 8.0 ANSI Driver DSN
SERVER       = localhost
PORT         = 3306
USER         =
Password     =
Database     = mytest
OPTION       = 3
SOCKET       =

USER , Password, and SOCKET can be left blank. In case of socket error, the path for mysqld.sock can be define like

SOCKET       = /var/run/mysqld/mysqld.sock

To test the database, isql command line tool can be used to connect to 'myodbc8a' as follow.

$ isql -v myodbc8a

If the library cannot be opened and file not found error is encountered, client library 'libmysqlclient.so.21' can be copied as follow.

$ sudo cp /usr/local/mysql/lib/libmysqlclient.so.21 /usr/local/lib/

Thereafter, ODBC client applications can use these DSNs as discussed in the following post.

Using MS SQL Server from C++ with wxWidgets

References

[Ora18a] Oracle. Building Connector/ODBC from a Source Distribution on Unix. 2018.
url: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-source-unix.html.

[Ora18b] Oracle. Configuring a Connector/ODBC DSN on Unix. 2018.
url: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-unix.html.

[Coo18a] Cool Emerald. Using Microsoft SQL Server from Linux Machines with ODBC & FreeTDS. 2018.
url: http://cool-emerald.blogspot.com/2018/10/using-microsoft-sql-server-from-linux.html.