Blog of Raivo Laanemets

Some time ago I needed to connect a MySQL database from SWI-Prolog to process the data inside it. I had previously tried to get this working through ODBC but without much success. I gave it a last try and was able to get it working.

ODBC

ODBC is an old standard (from early 1990's) developed by Microsoft to make it easier to query SQL databases. It also has implementation on Linux called unixODBC. SWI-Prolog contains an ODBC interface that can use unixODBC but setting up the proper configuration can be a bit confusing as there are so many layers to configure. The context diagram for ODBC looks like this:

Unlike with direct MySQL interfaces in other languages, you need to specify the configuration parameters in multiple files scattered throughout the system. This was the step where I got stuck the last time.

ODBC is an old technology but matured and stable and works well (once you manage to configure it). The drivers are provided and maintained for lots of databases.

Configuration files

The actual location of configuration files can be checked after unixODBC is installed (either from a system package or by compiling from source). This can be done through the command odbcinst -j. This will print something like this:

unixODBC 2.3.4
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/raivo/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The database drivers will be configured in /usr/local/etc/odbcinst.ini by this output. Each database driver (MySQL, SQLite, PostgreSQL, anything that has ODBC driver) can be added here. The file will take the location for the loadable module and some driver-specific options.

MySQL ODBC driver

The actual driver has to be installed before using it. I downloaded a compiled binary driver for my machine architecture directly from the MySQL site. It is possible to compile it yourself but you need a proper version of MySQL client libraries and the boost library installed.

The binary package contains the driver file lib/libmyodbc5a.so. This can be installed in a straightforward way by copying it to the system and adding the ODBC driver configuration entry manually as the root user:

cp lib/libmyodbc5a.so /usr/local/lib/
chown root:root /usr/local/lib/libmyodbc5a.so

and into /usr/local/etc/odbcinst.ini:

[mysql]
Driver=/usr/local/lib/libmyodbc5a.so

Multiple driver sections can be added into the file.

Connections

After the driver is configured, a connection using the driver can be added. I put my connection into the user-local file ~/.odbc.ini. It is possible to add system-wide connections too.

The file contains the database driver name with some additional parameters and driver-specific options such as the server hostname, port and the MySQL database name:

[test]
Description = MySQL test database
Trace       = Off
TraceFile   = stderr
Driver      = mysql
SERVER      = localhost
PORT        = 3306
DATABASE    = my_database

Multiple sections for multiple connections can be added to the file. Note that the connection username and password does not have to be set here. While they can be added here, they can also be set in SWI while activating the connection.

Connecting and querying

Connecting and querying is easy once everything is configured:

use_module(library(odbc)).

odbc_connect(test, C, [user(your_user), password(your_password)]),
odbc_query(C, 'SELECT * FROM my_table', Rows).

Further information on prepared statements and other useful features supported by SWI-Prolog's ODBC interface can be found in its docs. The similar connectivity can be set up for SWI in Windows too, except the drivers and connections are configured using the Windows own graphical tools.