SWI-Prolog connecting to PostgreSQL via ODBC

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. Capture

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


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)

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)

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@galaxy:~$ sudo whoami

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;
(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*               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                                  |
|                                       |
| 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

<Absolute path>/<File name>

<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]),

12. Start SWI-Prolog

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

groot@galaxy:~$ cd ~/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').

b. Run example, e.g.

?- example.
posts rows: row(10082)

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' ).

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

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

test('connection property',
    [ setup(odbc_connect('SWI-Prolog Discourse', 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, [])),
    ]) :-
    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, [])),
    ]) :-
        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, [])),
      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, [])),
        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.')).

    [ setup(odbc_connect('SWI-Prolog Discourse', Connection, [])),
    ]) :-
    odbc_set_connection(Connection, cursor_type(static)),
        'SELECT id,raw FROM posts WHERE id BETWEEN 1 AND 5 AND LENGTH(raw) < 100 ORDER BY id',
        [ 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
USER DATA SOURCES..: /home/groot/.odbc.ini
SQLULEN Size.......: 8
SQLLEN 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]


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