SWI-Prolog (snap version) connecting to sqlite3 database via ODBC DSN

Help! Predicate
1

I am working to connect to a database hosted by a cloud provider in SWI-Prolog.
The cloud database can be accessed over ODBC.

Having worked with databases and ODBC connections for decades I was not expecting it to be a trivial matter.

As such, I have focused on getting a smaller, local ODBC connection to a SQLite3 database working first.

(NB: Before posting, I have read SWI-Prolog connecting to PostgreSQL via ODBC but did not find a solution)

Setup:

Host OS: Ubuntu 20.04
unixodbc: 2.3.6-0.1build1
sqlite3: 3.31.1-4ubuntu0.2
libsqliteodbc:amd64:  0.9996-3

SWI-Prolog is installed via snap.

To help swipl access to the ODBC drivers and the sqlite3 database file I have placed a copy under my home directory:

sam@radon:~$ tree snap/mnt/odbc/
snap/mnt/odbc/
├── drivers
│   └── libsqlite3odbc.so
└── sqlite3
    └── cw4.db

My ~/.odbc.ini contains

[testsqlite]
Description = Connection to SQLite test db
Driver      = /home/sam/snap/mnt/odbc/drivers/libsqlite3odbc.so
Database    = /home/sam/snap/mnt/odbc/sqlite3/cw4.db
Timeout     = 2000

And I can access this through the ODBC complexities:

sam@radon:~$ isql -v testsqlite
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select uid from conn limit 5;
+-------------------------+
| uid                     |
+-------------------------+
| C0V3Y136ykoRwmFyma      |
| C2SoYstPCZw1ZSip3       |
| C3EKIp2njiZ5V3p7Sj      |
| C4hOrH2esSc78G862       |
| CAHnVL1tzmdstqg998      |
+-------------------------+
SQLRowCount returns 0
5 rows fetched
SQL>

So far, so good.

I now try to connect to the same ODBC DSN in swipl

sam@radon:~$ swipl
Welcome to SWI-Prolog (threaded, 64 bits, version 8.3.26)
SWI-Prolog comes with ABSOLUTELY NO WARRANTY. This is free software.
Please run ?- license. for legal details.

For online help and background, visit https://www.swi-prolog.org
For built-in help, use ?- help(Topic). or ?- apropos(Word).

?- odbc_connect(testsqlite, C, []).
ERROR: ODBC: State IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified
ERROR: In:
ERROR: [10] odbc:odbc_connect(testsqlite,_20108,[])
ERROR: [9] toplevel_call(user:user: …) at /snap/swi-prolog/35/usr/lib/swipl/boot/toplevel.pl:1115

?- ls('/home/sam/snap/mnt/odbc/drivers/libsqlite3odbc.so').
% /home/sam/snap/mnt/odbc/drivers/libsqlite3odbc.so       
true.

?- ls('/home/sam/snap/mnt/odbc/sqlite3/cw4.db').
% /home/sam/snap/mnt/odbc/sqlite3/cw4.db    
true.

?- ^D
% halt

It seems that the ODBC driver and the sqlite3 file are visible to the snap of SWI-Prolog.

Please let me know what I am doing wrong.

Many thanks in advance,

Steve

2

Combining the mysteries of odbc and snap is a challenge :slight_smile: A possible explanation is that the notion of “home” is different in a snap:

> swi-prolog.swipl
Welcome to SWI-Prolog (threaded, 64 bits, version 8.3.26)
SWI-Prolog comes with ABSOLUTELY NO WARRANTY. This is free software.
Please run ?- license. for legal details.

For online help and background, visit https://www.swi-prolog.org
For built-in help, use ?- help(Topic). or ?- apropos(Word).

?- expand_file_name(~, X).
X = ['/home/janw/snap/swi-prolog/x20'].

Don’t ask me what the x20 is :slight_smile: . Not finding ~/.odbc.ini is consistent with the error you get.

2 Likes
3

Thanks Jan.

Slight progress

sam@radon:~/snap/swi-prolog/current$ cp ~/.odbc.ini .

sam@radon:~$ export ODBCINI=/home/sam/snap/swi-prolog/current/.odbc.ini

sam@radon:~$ echo $ODBCINI
/home/sam/snap/swi-prolog/current/.odbc.ini

sam@radon:~$ swipl
Welcome to SWI-Prolog (threaded, 64 bits, version 8.3.26)
SWI-Prolog comes with ABSOLUTELY NO WARRANTY. This is free software.
Please run ?- license. for legal details.

For online help and background, visit https://www.swi-prolog.org
For built-in help, use ?- help(Topic). or ?- apropos(Word).

?- odbc_connect(testsqlite, C, []).
ERROR: ODBC: State 01000: [unixODBC][Driver Manager]Can't open lib '/home/sam/snap/mnt/odbc/drivers/libsqlite3odbc.so' : file not found
ERROR: In:
ERROR:   [10] odbc:odbc_connect(testsqlite,_20110,[])
ERROR:    [9] toplevel_call(user:user: ...) at /snap/swi-prolog/35/usr/lib/swipl/boot/toplevel.pl:1115
?-

At least it is now reading the ini file.

Still need to find a way to get snap the access to other OS components.

I think my time is better spent abandoning snap, and doing a local install.

1 Like
4

I tend to agree. Local installation is not hard and is probably needed if you want to use all the connectivity SWI-Prolog can offer. Snap is designed to avoid dependency issues, but also to reduce security risks by limiting access. AFAIK Snap does allow you to do whatever you want by giving additional permissions to a snap. Don’t ask me how.

5

Just to close this off …

I did not solve this using snap, but my work around is to delete the snap swipl and build from sources.

sam@radon:~/gits/swipl-devel/build$ swipl
Welcome to SWI-Prolog (threaded, 64 bits, version 8.3.26-5-g1e668cb44)
SWI-Prolog comes with ABSOLUTELY NO WARRANTY. This is free software.
Please run ?- license. for legal details.

For online help and background, visit https://www.swi-prolog.org
For built-in help, use ?- help(Topic). or ?- apropos(Word).


?- odbc_connect(testsqlite, C, []).
C = '$odbc_connection'(94607433530880).

?-
6

Hi. Just to clarify a few points here.
It is true that a snap has a different view of the HOME folder, to make the program strictly confined.
That in itself is not an issue, since the swipl snap has access to the real home (this is why you can access folders and files in the user home folder).
The problem is that the strict snap can only read libraries or programs that are included in the snap itself, for confinement purposes.
I knew this was a limitations when I built the snap originally, and I would like to repurpose it to be unconfined, without these limitations (and without the security they entail).
For something like swi-prolog the snap should serve more the purpose of easy installation of up-to-date versions, rather that change how the system works for developers.
In this way the snap would mostly have the same possibilities of the classic install.

7

I think unconstrained makes sense. Would that also allow to include (for example) the JPL interface without including Java itself? Can you make a PR?

8

It should, as long as there are no issues coming from things being built on different library versions. It needs further testing.
If you remember I wanted to try building an unconstrained version a while back, but I haven’t had much time unfortunately.
I will look at it in the next few weeks though.
Just to simplify things for me… are the build instructions on the website up-to-date?

2 Likes
9

Unfortunately there seem to be various versions of them floating around. I think most are in fairly good shape. The current snap instruction surely works.