SWI-Prolog connecting to PostgreSQL via ODBC

Wiki · May 24, 2020, 11:28pm

Note: This is a topic for learning how to connect SWI-Prolog to PostgreSQL via ODBC.
If you have Trust level: Basic you can edit this by clicking on the edit icon in the lower right.

Note: Do not reply to this topic; questions, concerns, comments, etc. are to be handled in
Wiki Discussion: SWI-Prolog connecting to PostgreSQL via ODBC

SWI-Prolog connecting to PostgreSQL via ODBC

Ubuntu

Installation Instructions

Language agnostic steps

1. Install PostgreSQL

(If not currently installed)

$ sudo apt update
$ sudo apt install postgresql

2. Install net-tools

(If not currently installed)

$ sudo apt install net-tools

3. Install unixodbc

$ sudo apt install unixodbc

4. Install PostgreSQL ODBC driver

$ sudo apt-get install odbc-postgresql

5. Verify `/etc/odbcinst.ini` entries for PostgreSQL

/etc/odbcinst.ini - Configuration file containing all the database drivers specifications. (ref)

groot@galaxy:~$ cat /etc/odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

6. Create `odbc.ini` entry

odbc.ini needs to be in directory /etc, e.g. /etc/odbc.ini (ref 1, ref 2)
ini files have a specific syntax. (ref)
odbc.ini files have a specific format (semantics) (ref)
ini files are text files and can be edited with any text editor such as nano, vim, etc.
/etc/odbc.ini - is owned by root with only root having write access, e.g.

$ ls -la /etc/odbc.ini
-rw-r--r-- 1 root root 314 Jun  7 07:49 /etc/odbc.ini

With privileges elevated to root the file can be edited, e.g. $ sudo nano /etc/odbc.ini

Example of sudo in action.

groot@galaxy:~$ whoami
groot
groot@galaxy:~$ sudo whoami
root

Note: The user groot is not root.

odbc.ini contains user-specified access information for the psqlODBC driver.

[SWI-Prolog Discourse]
Description = SWI-Prolog Discourse example
Driver      = PostgreSQL Unicode
Servername  = localhost
Database    = swi_prolog
UserName    = groot
Password    = <password>
Port        = 5432

[SWI-Prolog Discourse]
[SWI-Prolog Discourse] is a user created Data Source Name (DSN). This example has a space and mixed case letters. It is also an ini section name.
Description = SWI-Prolog Discourse example
SWI-Prolog Discourse example is just free text used with some of the tools to display a meaningful description.
Driver = PostgreSQL Unicode
PostgreSQL Unicode is one of the section names from /etc/odbcinst.ini. The other section name from the example above is PostgreSQL ANSI.
Servername = localhost
The servername value uses the format [protocol:]server[,port] , e.g. localhost.
The servername property is optional but included here to ensure it is set correctly.
localhost is the server (hostname) that refers to the current computer. (ref)
Database = swi_prolog
swi_prolog is a database name in the PostgreSQL database
The database name is the name used with the createdb command,
e.g. createdb swi_prolog
or can be displayed using psql with the \l command.

groot@galaxy:~$ psql postgres
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \l
                                     List of databases
           Name           |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
--------------------------+----------+----------+---------+---------+-----------------------
 postgres                 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 swi_prolog               | groot    | UTF8     | C.UTF-8 | C.UTF-8 |

UserName = groot
groot is a user of the corresponding database swi_prolog, e.g.

postgres=# \c swi_prolog
You are now connected to database "swi_prolog" as user "groot".
swi_prolog=# SELECT usename FROM pg_user;
 usename
----------
 postgres
 groot
(2 rows)

Password = <password>
<password> is the password used by correspondening user (groot). Note: In creating this Wiki a password was created for user groot as the normal access via psql did not use a password.
Port = 5432
5432 is the default port for PostgreSQL. (ref)

For demonstration purposes here, the swi_prolog schema is from Discourse and the data is the posts from the SWI-Prolog Discourse forum.

7. Start the PostgreSQL server

$ sudo service postgresql start

8. Check that PostgreSQL is listening on port 5432

groot@galaxy:~$ netstat -nlp | grep postgresql
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -
unix  2      [ ACC ]     STREAM     LISTENING     15782    -                    /var/run/postgresql/.s.PGSQL.5432

9. Test ODBC settings using iusql

$ iusql -v "SWI-Prolog Discourse"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT COUNT(*) FROM posts;
+---------------------+
| count               |
+---------------------+
| 10082               |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit

Language specific steps (SWI-Prolog)

10. Install SWI-Prolog ODBC

$ sudo apt-get install swi-prolog-odbc

Note: For this wiki SWI-Prolog was installed using the PPA. If you build from the sources, the SWI-Prolog interface may already be installed. (I plan to check if the PPA does not install the SWI-Prolog ODBC libraries).

11. Create Prolog example

e.g.
~/projects/prolog/odbc_wiki/odbc_example.pl
<Absolute path> - ~/projects/prolog/odbc_wiki
<File name> - odbc_example.pl
Note: File name must have file type pl so that consult/1 can be used.

:- use_module(library(odbc)).

example :-
    odbc_connect('SWI-Prolog Discourse', Connection, []),
    odbc_query(Connection, 'SELECT COUNT(*) FROM posts',COUNT),
    format('posts rows: ~w~n',[COUNT]),
    odbc_disconnect(Connection).

12. Start SWI-Prolog

a. Using Ubuntu change directory to <Absolute path>, e.g.

groot@galaxy:~$ cd ~/projects/prolog/odbc_wiki
groot@galaxy:~/projects/prolog/odbc_wiki$

b. Start SWI-Prolog, e.g.

groot@galaxy:~/projects/prolog/odbc_wiki$ swipl
Welcome to SWI-Prolog (threaded, 64 bits, version 8.1.32)
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).

?-

c. Check access to <File name>, e.g.

?- working_directory(D,D),directory_files(D,Files).
D = '/home/groot/projects/prolog/odbc_wiki/',
Files = ['odbc_example.pl', .., '.'].

13. Run example

a. consult(<File name>), e.g.

?- consult('odbc_example.pl').
true.

b. Run example, e.g.

?- example.
posts rows: row(10082)
true.

Simple test cases

:- use_module(library(odbc)).

:- begin_tests('swi-prolog ODBC').

test('odbc data source') :-
        odbc_data_source('SWI-Prolog Discourse', Driver),

        assertion( Driver = 'PostgreSQL Unicode' ).

test(connections,
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
      cleanup(odbc_disconnect(Connection))
    ]) :-
        odbc_current_connection(Connection, DSN),

        assertion( DSN == 'SWI-Prolog Discourse' ).

test('connection property',
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
      cleanup(odbc_disconnect(Connection))
    ]) :-
        odbc_get_connection(Connection, database_name(Database_name)),
        odbc_get_connection(Connection, dbms_name(DBMS_name)),
        odbc_get_connection(Connection, dbms_version(DBMS_version)),
        odbc_get_connection(Connection, driver_name(Driver_name)),
        odbc_get_connection(Connection, driver_odbc_version(Driver_odbc_version)),
        odbc_get_connection(Connection, driver_version(Driver_version)),
        odbc_get_connection(Connection, active_statements(Active_statements)),

        assertion( Database_name == swi_prolog ),
        assertion( DBMS_name == 'PostgreSQL' ),
        assertion( DBMS_version == '10.0.12' ),
        assertion( Driver_name == 'psqlodbcw.so' ),
        assertion( Driver_odbc_version == '03.51' ),
        assertion( Driver_version == '12.01.0000' ),
        assertion( Active_statements == 0 ).

test('odbc_query - count',
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
        cleanup(odbc_disconnect(Connection))
    ]) :-
    odbc_query(Connection, 'SELECT COUNT(*) FROM posts', Row),

    assertion( Row == row(10082) ).

test('odbc_query - single row, single column',
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
      cleanup(odbc_disconnect(Connection))
    ]) :-
        odbc_query(Connection, 'SELECT raw FROM posts WHERE id = 1', Row),

        assertion( Row == row('A category exclusive to members with trust level 3 and higher.') ).

test('odbc_query - multiple rows, single column',
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
      cleanup(odbc_disconnect(Connection)),
      all(Rows ==
        [
            row('A category exclusive to members with trust level 3 and higher.'),
            row('Discussion about this site, its organization, how it works, and how we can improve it.')
        ]
      )
    ]) :-
        odbc_query(Connection, 'SELECT raw FROM posts WHERE id in (1,2)', Rows).

test('odbc_query - multiple rows, multiple columns',
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
        cleanup(odbc_disconnect(Connection)),
        all(Rows ==
        [
            row(1,'A category exclusive to members with trust level 3 and higher.'),
            row(2,'Discussion about this site, its organization, how it works, and how we can improve it.')
        ]
        )
    ]) :-
        odbc_query(Connection, 'SELECT id,raw FROM posts WHERE id in (1,2)', Rows).

fetch_result(row(1,'A category exclusive to members with trust level 3 and higher.')).
fetch_result(row(2,'Discussion about this site, its organization, how it works, and how we can improve it.')).
fetch_result(row(3,'Private category for staff discussions. Topics are only visible to admins and moderators.')).
fetch_result(row(5,'Edit the first post in this topic to change the contents of the Terms of Service page.')).

test('odbc_fetch',
    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
      cleanup(odbc_disconnect(Connection))
    ]) :-
    odbc_set_connection(Connection, cursor_type(static)),
    odbc_prepare(Connection,
        'SELECT id,raw FROM posts WHERE id BETWEEN 1 AND 5 AND LENGTH(raw) < 100 ORDER BY id',
        [],
        Statement,
        [ fetch(fetch) ]),
    odbc_execute(Statement, []),
    fetch(Statement, next).

fetch(Statement, Options) :-
    odbc_fetch(Statement, Row, Options),
    (   Row == end_of_file
    ->  true
    ;   assertion(fetch_result(Row)),
        fetch(Statement, Options)
    ).

:- end_tests('swi-prolog ODBC').

System test cases

See: GitHub SWI-Prolog - packages-odbc / test_odbc.pl

Using odbcinst

To print config info

groot@galaxy:~$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/groot/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

To list Drivers

groot@galaxy:~$ odbcinst -q -d
[PostgreSQL ANSI]
[PostgreSQL Unicode]

To list DSNs

groot@galaxy:~$ odbcinst -q -s
[SWI-Prolog Discourse]

Windows

Note: If there is enough demand a Windows version of this can be created.

Topic		Replies	Views
Wiki Discussion: SWI-Prolog connecting to PostgreSQL via ODBC Wiki Discussion	23	2075	June 11, 2020
SWI-Prolog (snap version) connecting to sqlite3 database via ODBC DSN Predicate	8	673	June 27, 2021
How work with socket? Discussion discussion	5	544	June 3, 2022
Help - MYSQL and Swi Prolog Help! how-to	4	1389	July 10, 2020
Connecting to sqlite3 with ODBC: choosing a database file? Tools how-to	3	1299	March 19, 2023

SWI-Prolog connecting to PostgreSQL via ODBC

SWI-Prolog connecting to PostgreSQL via ODBC

Ubuntu

Installation Instructions

Language agnostic steps

1. Install PostgreSQL

2. Install net-tools

3. Install unixodbc

4. Install PostgreSQL ODBC driver

5. Verify /etc/odbcinst.ini entries for PostgreSQL

6. Create odbc.ini entry

7. Start the PostgreSQL server

8. Check that PostgreSQL is listening on port 5432

9. Test ODBC settings using iusql

Language specific steps (SWI-Prolog)

10. Install SWI-Prolog ODBC

11. Create Prolog example

12. Start SWI-Prolog

13. Run example

Simple test cases

System test cases

Using odbcinst

To print config info

To list Drivers

To list DSNs

Windows

Related topics

5. Verify `/etc/odbcinst.ini` entries for PostgreSQL

6. Create `odbc.ini` entry