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