GAMBAS BOOK 3.19.1

There are two ways to install the special ODBC drivers for the DBMS MySQL.

The ODBC driver 'odbc-mariadb' from the DBMS MariaDB can also be used as a special ODBC driver for the DBMS MySQL. You can either install this driver via the application management or enter the following line in a terminal:

$ sudo apt-get install odbc-mariadb

The special ODBC drivers required for the DBMS MySQL can also be downloaded from the MySQL project page at https://dev.mysql.com/downloads/connector/odbc/. Make sure you select the correct operating system and version of MySQL in the DEB package:

Figure 22.9.3.1.1: Selection dialogue for the operating system and the MySQL version

• First unpack the archive - for example mysql-connector-odbc_8.0.24-1ubuntu20.04_amd64.deb.
• Then unpack the archive data.tar.xz in the archive.
• Finally, copy the two driver files libmyodbc8a.so and libmyodbc8w.so, which are located in the directory …/usr/lib/x86_64-linux-gnu/odbc, to the directory /usr/lib/x86_64-linux-gnu/odbc under root rights.

Conclusion: The ANSI driver is located in the libmyodbc8a.so file and the Unicode driver in the libmyodbc8w.so file.

The special ODBC drivers for MySQL are configured in the system-wide configuration file /etc/odbcinst.ini. Complete this file with root rights by inserting the following lines for the three ODBC drivers at the end:

[MySQL ANSI]
Description=MySQL ODBC Driver (ANSI version)
Driver=libmyodbc8a.so
UsageCount=1
 
[MySQL Unicode]
Description=MySQL ODBC Driver (Unicode version)
Driver=libmyodbc8w.so
UsageCount=1
 
[MySQL MDB]
Description=MariaDB ODBC Driver
Driver=libmaodbc.so
UsageCount=1

The names of the ODBC drivers are in square brackets, which are required when configuring the data source name (DataSourceName).

Add the following two DSN sections to the local configuration file ~/.odbc.ini. The first section uses the special driver 'MySQL Unicode' and the standard port 3306 for MySQL:

[dsn_my_mysql]
Description             = MySQL DB `mysql`(Unicode)
Driver	                = MySQL Unicode
Servername	        = localhost
Port		        = 3306
User        	        = root
Password      	        = ampw
Database		= mysql
ReadOnly		= No

You access the ODBC driver 'MySQL MDB' from the file /etc/odbcinst.ini with this configuration:

[dsn_my_mdb_mysql]
Description		= MySQL DB `mysql`(MariaDB)
Driver		        = MySQL MDB
Servername	        = localhost
Port		        = 3306
User          	        = root
Password      	        = ampw
Database		= mysql
ReadOnly		= No

No data source name is configured for the ANSI driver.

You can use the odbcinst console programme to inspect the ODBC drivers. The -q and -d options list the names of all (special) ODBC drivers that have previously registered with unixODBC:

$ odbcinst -d -q
[PostgreSQL ANSI]
[PostgreSQL Unicode]
[SQLite]
[SQLite3]
[MySQL ANSI]
[MySQL Unicode]
[MySQL MDB]

The command odbcinst -q -s, on the other hand, lists all ODBC data source names (DSN) that you have defined in the configuration file ~/.odbc.ini:

$ odbcinst -q -s
[dsn_pg_postgres]
[dsn_sl3_contacts]
[dsn_my_mysql]
[dsn_my_mdb_mysql]

Two tests are carried out for the DBMS MySQL because of the ODBC drivers from different packages. For both tests, the (system) database `mysql` was entered in the configuration file ~/.odbc.ini, but this is not used. Instead, the version of MySQL is queried. Knowing the version is of secondary importance here, because the test only involved successfully connecting to a MySQL database via the obdc-capable client programme isql.

$ isql -v dsn_my_mysql root ampw
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT version();
+------------------------+
| version()              |
+------------------------+
| 8.0.23-0ubuntu0.20.04.1|
+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
$ 

$ isql -v dsn_my_mdb_mysql root ampw
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT version();
+------------------------+
| version()              |
+------------------------+
| 8.0.23-0ubuntu0.20.04.1|
+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
$ 

Conclusion: Both tests were successful!