This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
The MariaDB ODBC drivers can be installed from the platform’s default package repositories. As it is missing on this test environment, let’s install the MariaDB RDBMS (v10.5.11-1 ) too, and the ODBC drivers (v3.1.9-1) as root:

# apt install mariadb-server
# apt install odbc-mariadb

A systemd service was set up and launched:

# systemctl status mariadb
● mariadb.service - MariaDB 10.5.11 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2021-08-23 21:10:48 CEST; 5min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
...

Check the ODBC driver file /etc/odbcinst.ini:

# odbcinst -q -d -n "MariaDB Unicode" 
[MariaDB Unicode]
Driver=libmaodbc.so
Description=MariaDB Connector/ODBC(Unicode)
Threading=0
UsageCount=1

Grant the necessary permissions to the test user debian using the provided native administration client, mysql:

# mysql
MariaDB [(none)]> GRANT CREATE, INSERT, SELECT, DELETE, UPDATE, DROP, ALTER ON *.* TO 'debian'@'localhost';
MariaDB [(none)]> exit;
Bye

As the user debian, create the sampledb database and its tables, and populate them using mysql again:

$ mysql --user=debian --password=debian
create database sampledb;
use sampledb;
-- create & populate the tables for mariadb by copying and pasting the statements from the above files;
-- test the data;
SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
+--------------------------+------------+------------+------------------------------------------+---------------------+
| country_name             | country_id | country_id | street_address                           | city                |
+--------------------------+------------+------------+------------------------------------------+---------------------+
| China                    | CN         | NULL       | NULL                                     | NULL                |
| United Kingdom           | UK         | UK         | 8204 Arthur St                           | London              |
| United Kingdom           | UK         | UK         | Magdalen Centre, The Oxford Science Park | Oxford              |
| United States of America | US         | US         | 2014 Jabberwocky Rd                      | Southlake           |
| United States of America | US         | US         | 2011 Interiors Blvd                      | South San Francisco |
| United States of America | US         | US         | 2004 Charade Rd                          | Seattle             |
+--------------------------+------------+------------+------------------------------------------+---------------------+
6 rows in set (0.001 sec)
q

The data are OK. Configure ODBC by editing the user’s DSN file:

$ vi ~/.odbc.ini
[mysqldb]
Description=My mysql sample database
Driver=MariaDB Unicode
Database=sampledb

Check the DSN definition:

$ odbcinst -q -s -n mysqldb
[mysqldb]
Description=My mysql sample database
Driver=MariaDB Unicode
Database=sampledb
Socket=/var/run/mysqld/mysqld.sock

See below for an explanation about the highlighted “Socket=…” line.
Test the DSN via isql:

$ isql mysqldb -v debian debian
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
+---------------------------+-----------+-----------+-----------------------------------------+---------------------+
| country_name              | country_id| country_id| street_address                          | city                |
+---------------------------+-----------+-----------+-----------------------------------------+---------------------+
| China                     | CN        |           |                                         |                     |
| United Kingdom            | UK        | UK        | 8204 Arthur St                          | London              |
| United Kingdom            | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford              |
| United States of America  | US        | US        | 2014 Jabberwocky Rd                     | Southlake           |
| United States of America  | US        | US        | 2011 Interiors Blvd                     | South San Francisco |
| United States of America  | US        | US        | 2004 Charade Rd                         | Seattle             |
+---------------------------+-----------+-----------+-----------------------------------------+---------------------+
SQLRowCount returns 6
6 rows fetched

The ODBC connection works fine. Test the DSN from a python script using the pyodbc module:

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
import pyodbc 
cnxn = pyodbc.connect(DSN='mysqldb;user=debian;password=debian')
cursor = cnxn.cursor()      
cursor.execute("""SELECT
...         c.country_name,
...         c.country_id,
...         l.country_id,
...         l.street_address,
...         l.city
... FROM
...         countries c
... LEFT JOIN locations l ON l.country_id = c.country_id
... WHERE
...         c.country_id IN ('US', 'UK', 'CN')""")

row = cursor.fetchone() 
 while row:
...     print (row) 
...     row = cursor.fetchone()
... 
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

Everything is just fine.
Note: When the MariaDB database is restarted, an error message such as the one below from isql is displayed when connecting to a database as a non-root user:

$ isql -v mysqldb debian debian
[S1000][unixODBC][ma-3.1.7]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[ISQL]ERROR: Could not SQLConnect

or from pyodbc:

pyodbc.Error: ('HY000', "[HY000] [ma-3.1.7]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) (2002) (SQLDriverConnect)")

If this happens, just make sure the line Socket=/var/run/mysqld/mysqld.sock is present in ~/.odbc.ini. Alternatively, but not as good because as it is needed each time the database is restarted, create the symlink below as root:

# ln -s /run/mysqld/mysqld.sock /tmp/mysql.sock

MariaDB is now fully accessible from any ODBC application under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel


Thumbnail [60x60]
by
Cesare Cervini