# SWI-Prolog connecting to PostgreSQL via ODBC **URL:** \ **Category:** Wiki\ **Tags:** how-to\ **Created:** [May 24, 2020, 11:28pm UTC](https://swi-prolog.discourse.group/t/swi-prolog-connecting-to-postgresql-via-odbc/2404 "2020-05-24T23:28:27Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![Wiki](https://avatars.discourse-cdn.com/v4/letter/w/e8c25b/32.png) [@Wiki](https://swi-prolog.discourse.group/u/Wiki)\ **Post date:** [May 24, 2020, 11:28pm UTC](https://swi-prolog.discourse.group/t/swi-prolog-connecting-to-postgresql-via-odbc/2404/1 "2020-05-24T23:28:27Z")
> Note: This is a topic for learning how to connect SWI-Prolog to PostgreSQL via ODBC. > If you have [Trust level](https://blog.discourse.org/2018/06/understanding-discourse-trust-levels/): [Basic](https://swi-prolog.discourse.group/badges/1/basic) you can edit this by clicking on the edit icon in the lower right. ![Capture](https://global.discourse-cdn.com/free1/uploads/swiprolog/original/1X/bfd4c3b77dc7f3f98ece3d5ae3202fcccb0fb8b3.png) > 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](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405) # SWI-Prolog connecting to [PostgreSQL](https://www.postgresql.org/) via [ODBC](https://en.wikipedia.org/wiki/Open_Database_Connectivity) ## Ubuntu ### Installation Instructions ### Language agnostic steps #### 1. Install PostgreSQL (If not currently installed) ```bash $ sudo apt update $ sudo apt install postgresql ``` #### 2. Install net-tools (If not currently installed) ```bash $ sudo apt install net-tools ``` #### 3. Install [unixodbc](http://www.unixodbc.org/) ```bash $ sudo apt install unixodbc ``` #### 4. Install PostgreSQL ODBC driver ```bash $ 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](http://manpages.ubuntu.com/manpages/cosmic/man1/odbcinst.1.html#files)) ```ini 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](http://manpages.ubuntu.com/manpages/cosmic/man1/odbcinst.1.html#files), [ref 2](http://manpages.ubuntu.com/manpages/cosmic/man5/odbc.ini.5.html)) `ini` files have a specific [syntax](https://en.wikipedia.org/wiki/Syntax). ([ref](https://en.wikipedia.org/wiki/INI_file)) `odbc.ini` files have a specific format ([semantics](https://en.wikipedia.org/wiki/Semantics)) ([ref](http://www.unixodbc.org/odbcinst.html)) `ini` files are text files and can be edited with any text editor such as [nano](https://help.ubuntu.com/community/Nano#:~:text=Introduction,the%20non%2Dfree%20Pico%20editor.), [vim](https://help.ubuntu.com/community/VimHowto), etc. `/etc/odbc.ini` - is [owned](https://help.ubuntu.com/community/FilePermissions) by [root](https://askubuntu.com/questions/608495/what-is-root-and-how-can-i-become-it) with only `root` having write access, e.g. ```prolog $ ls -la /etc/odbc.ini -rw-r--r-- 1 root root 314 Jun 7 07:49 /etc/odbc.ini ``` With privileges [elevated](http://manpages.ubuntu.com/manpages/trusty/man8/sudo_root.8.html) to `root` the file can be edited, e.g. `$ sudo nano /etc/odbc.ini` Example of sudo in action. ```prolog groot@galaxy:~$ whoami groot groot@galaxy:~$ sudo whoami root ``` Note: The user [groot](https://en.wikipedia.org/wiki/Groot) is not `root`. `odbc.ini` contains user-specified access information for the psqlODBC driver. ```ini [SWI-Prolog Discourse] Description = SWI-Prolog Discourse example Driver = PostgreSQL Unicode Servername = localhost Database = swi_prolog UserName = groot Password = Port = 5432 ``` - [SWI-Prolog Discourse] `[SWI-Prolog Discourse]` is a user created Data Source Name ([DSN](https://support.microsoft.com/en-us/help/966849/what-is-a-dsn-data-source-name)). 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](https://en.wikipedia.org/wiki/Hostname)) that refers to the current computer. ([ref](https://en.wikipedia.org/wiki/Localhost)) - Database = swi\_prolog `swi_prolog` is a database name in the PostgreSQL database The database name is the name used with the [createdb](https://www.postgresql.org/docs/current/app-createdb.html) command, e.g. `createdb swi_prolog` or can be displayed using `psql` with the [\l](https://www.postgresqltutorial.com/postgresql-show-databases/) command. ```bash 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. ```prolog 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 = \ `` is the password used by correspondening user (`groot`). Note: In creating this Wiki a [password was created](https://www.postgresqltutorial.com/postgresql-change-password/) for user `groot` as the normal access via [psql](https://www.postgresql.org/docs/12/app-psql.html) did not use a password. - Port = 5432 `5432` is the default port for PostgreSQL. ([ref](https://www.postgresql.org/docs/current/app-postgres.html)) 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 ```bash $ 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](http://manpages.ubuntu.com/manpages/trusty/man1/isql.1.html) ```bash $ 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](https://github.com/SWI-Prolog/packages-odbc) ```bash $ sudo apt-get install swi-prolog-odbc ``` Note: For this wiki SWI-Prolog was installed using the [PPA](https://www.swi-prolog.org/build/PPA.html). 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` \ - `~/projects/prolog/odbc_wiki` \ - `odbc_example.pl` Note: File name must have file type `pl` so that consult/1 can be used. ```prolog :- 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 \, e.g. ```prolog groot@galaxy:~$ cd ~/projects/prolog/odbc_wiki groot@galaxy:~/projects/prolog/odbc_wiki$ ``` b. Start SWI-Prolog, e.g. ```prolog 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 \, e.g. ```prolog ?- working_directory(D,D),directory_files(D,Files). D = '/home/groot/projects/prolog/odbc_wiki/', Files = ['odbc_example.pl', .., '.']. ``` #### 13. Run example a. consult(\), e.g. ```prolog ?- consult('odbc_example.pl'). true. ``` b. Run example, e.g. ```prolog ?- example. posts rows: row(10082) true. ``` * * * ### Simple test cases ```prolog :- 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](https://github.com/SWI-Prolog) - [packages-odbc](https://github.com/SWI-Prolog/packages-odbc) / [test\_odbc.pl](https://github.com/SWI-Prolog/packages-odbc/blob/master/test_odbc.pl) ### Using [odbcinst](http://manpages.ubuntu.com/manpages/trusty/man1/odbcinst.1.html) #### To print config info ```plaintext 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 ```plaintext groot@galaxy:~$ odbcinst -q -d [PostgreSQL ANSI] [PostgreSQL Unicode] ``` #### To list DSNs ```plaintext groot@galaxy:~$ odbcinst -q -s [SWI-Prolog Discourse] ``` ## Windows Note: If there is enough demand a Windows version of this can be created.