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 unixodbc

$ sudo apt install unixodbc

2. Install PostgreSQL ODBC driver

$ sudo apt-get install odbc-postgresql

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

4. 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 (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. (That seems to be the first use of that expression!, specifically groot is not root) They are close in spelling but not the same.

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.

5. Start the PostgreSQL server

$ sudo service postgresql start

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

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

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

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

10. 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', .., '.'].

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