Connecting to sqlite3 with ODBC: choosing a database file?

Help! Tools
1

Using SQLite with ODBC, I have two options on how to chose a database file:

  1. I can define a DSN in a configuration file, and hardcode the file there; this way I can use odbc_connect/3.
  2. I can not bother with a configuration file and use odbc_driver_connect/3, then I can choose the database file from the Prolog source.

How are others doing this? I almost feel like for SQLite, using odbc_driver_connect/3 is the correct approach, despite the advice from the docs which says,

Whenever possible, applications should use odbc_connect/3.

(Figuring out all of this took a while so I will just share it here. It belongs to a Wiki maybe but I’d like someone with more experience to comment on this first)

First, my OS and hardware: MacOS Ventura 13.2.1 on Intel

I have installed SWI-Prolog using the Mac OS X disk image with relocatable application bundle. I see:

$ swipl --version
SWI-Prolog version 9.1.7 for fat-darwin

Then, I used homebrew to install sqliteodbc (which has sqlite and unixodbc as dependencies):

$ brew update && brew install sqlite unixodbc sqliteodbc

Now it gets interesting. After banging my head against the wall for a while, I ran:

$ odbcinst -j
unixODBC 2.3.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/xxx/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Before that, following the docs from sqliteodbc, I used /etc/odbcinst.ini instead of /usr/local/etc/odbcinst.ini. Annoyingly, isql doesn’t care which of the two files has the configuration in it, so running

$ isql -v testdsn

… happily works, no matter in which of the two files you put the driver config. :smiley:

And of course make sure you put the correct path for the driver, some docs might be missing the “3” there in the middle:

$ cat odbcinst.ini 
[SQLite3]
Description = SQLite3 ODBC Driver
Driver      = /usr/local/lib/libsqlite3odbc.so
Setup       = /usr/local/lib/libsqlite3odbc.so
Threading   = 2

If someone knows if the “Threading=2” is necessary and what it means, please share. It was in the sqliteodbc README.

Then comes your personal “USER DATA SOURCES” file in ~/.odbc.ini. You might read the sqliteodbc README; and even man odbc.ini on your system and they might tell you that: “[ODBC Data Sources]” is a mandatory section; this is not true for my case at least.

Even worse, the man page for odbc.ini claims:

Each data source is identified by a [section header], which is the DSN name used by applications. Each DSN definition section may contain values for the keys:

• Driver (REQUIRED)
The name of the ODBC driver to use for the DSN. The name must exactly match the
[section name] of the driver definition stored in odbcinst.ini (and listed by
odbcinst -q -d).

This is a dirty rotten lie. In fact, if I do that and try to use odbc_connect/3, I see:

ERROR: ODBC: State IM002: [unixODBC][Driver Manager]Data source name not found and no default driver specified

Instead, I must put the full path to the driver installed by sqliteodbc, so:

$ cat ~/.odbc.ini
[testdsn]
Description = Test DSN
Driver      = /usr/local/lib/libsqlite3odbc.so
Database    = <full path to the database file>

Now I can do:

?- odbc_connect(testdsn, X, []), odbc_disconnect(X).
X = '$odbc_connection'(105553118773888).

I can also skip the Database line from ~/.odbc.ini completely, it still “works”, with a transient in-memory database I assume. I didn’t figure out how to save the state to a file.

The other option is to use odbc_driver_connect/3 and explicitly list the driver and the database. This works even if the configurations files are empty (or wrong):

?- odbc_driver_connect('Driver=/usr/local/lib/libsqlite3odbc.so;Database=<full path to database file>;', X, []),
   odbc_disconnect(X).
X = '$odbc_connection'(105553167451472)

… but still wondering how to choose the database file other than defining multiple DSNs.

2

FYI

I am not actively using SQL with Prolog, the main reason is impedence mismatch, having to convert to/from SQL types to Prolog types, thus why my focus on using RocksDB with Prolog. While I know RocksDB will work with Linux and can work with Windows using either WSL 2 or MSYS2, I am still after a native build of the code. :slightly_smiling_face:

3

Well it seems that SQLite and RocksDB are quite different, so their use cases are different, too. Either way SQLite does not have the same approach to data types as other RDBMSs.

4

I’ve just started building something that involves accessing a PostgreSQL database from Prolog. I’ve also been using odbc_driver_connect/3 and hard-coding the full path to the driver, despite the warning in the documentation. I really dislike the need to globally define a database connection stanza; it interacts poorly with how I prefer to structure my deploy procedures.

1 Like