# Wiki Discussion: SWI-Prolog connecting to PostgreSQL via ODBC **URL:** \ **Category:** Wiki Discussion\ **Created:** [May 24, 2020, 11:30pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405 "2020-05-24T23:30:36Z")\ **Posts on this page:** 20\ **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:30pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/1 "2020-05-24T23:30:36Z")
This is a topic to discuss the wiki [SWI-Prolog connecting to PostgreSQL via ODBC](https://swi-prolog.discourse.group/t/swi-prolog-connecting-to-postgresql-via-odbc/2404) ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [May 26, 2020, 10:51am UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/2 "2020-05-26T10:51:22Z")
Some points of note related to `odbc.ini` entries, e.g. ```prolog [swi_prolog] Description = SWI-Prolog Discourse Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so Servername = localhost UserName = Password = Port = 5432 Database = swi_prolog ``` 1. ~~The entry name, `[swi_prolog]`, and Database value are currently the same: `swi_prolog` My understanding is that this is not required, the entry name and the Database name can be different. This is something I plan to verify and possibly use the results to update the Wiki.~~ 2. ~~The Driver, `/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so`, and Setup, `/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so`, values do not require the full path. I don’t know which means the system uses to find the `so` files if the directories are removed. Once I know that I can enhance the Wiki.~~ 3. ~~The Driver, `/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so`, and Setup, `/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so`, values might be able to use the odbcinst.ini entries, e.g. `PostgreSQL ANSI` and `PostgreSQL Unicode`. I have seen this done in some examples but have not tested it.~~ ~~ ```prolog Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so ``` ~~ ~~they should be in `/etc/odbcinst.ini` and `/etc/odbc.ini` should use `Driver = PostgreSQL ANSI` ([ref](http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/installing_configuring_odbc.html))~~ 1. The `UserName` and `Password` values are currently hard coded into file and visible as plain text. This needs to be removed as these values should be options of the connect. However once they are removed from `odbc.ini` then any connect means using the `odbc.ini` entry needs to pass the user name and password along. Currently the wiki demonstrates two different ways to connect via ODBC, `isql` and `SWI-Prolog ODCBC`. So both of those ways need to be changed to demonstrate how to pass the values. 2. ~~I currently do not understand why there are two `.so` files used, I was expecting just one.~~ 3. This should add a demonstration of when the values for `Servername` is something other than `localhost`. * * * In researching for the wiki I saw way to many examples that just gave values and really did not explain why they were what they were, or used a specific subset of the `name=value` settings without showing alternatives which IMHO is what makes using ODBC so hard. If you can find entries that work you are lucky but if you have do it from scratch, with so many variations, it becomes many hours of trial and error. Thus this wiki needs to explain what the options are and the pros and cons of using it. Also it should demonstrate that their are several links in the chain and that if anyone of them is broken the chain is broken. That is why I added the use of `isql` before installing the SWI-Prolog ODBC package, it shows that you can check part of your work before adding more links, e.g. SWI-Prolog ODBC, to the chain. Also for each link (part in the sequence) it should explain what that link does, how it connects to its neighbors in the sequence, choices for that link and tools to work with that link. Another thing that was confusing is that ODBC started with Microsoft and thus the use of `*.ini` files. Then in Linux they did it like Microsoft for many years and now using [odbcinst](http://www.unixodbc.org/odbcinst.html) seems to be the norm. Explaining how to use `odbcinst` needs to be added to the wiki. Update: Added some query examples but still many more of the odbcinst commands are not demonstrated. The wiki also needs a companion wiki or additional section that explains how to use `isql` for those really quick queries. The wiki needs a lot more details of how to use SWI-Prolog ODBC with examples. 1. The result from the example query using SWI-Prolog was `row(10082)` which was displayed as such. What would be better is to parse out the result then use just the needed parts as necessary. At present because I have not done enough examples it may be just a few simple unification patterns done as as clauses for a result predicate that gets the needed values, or it might require DCGs. Update: Working on example that parses the words in the `posts` table `raw` field into words then use [WordNet](https://wordnet.princeton.edu/) to check spelling mistakes at first and then expand on higher abstractions. The wiki should show examples of error messages and how to correct them, e.g. what if you skip installing SWI-Prolog ODBC, what if you spell an entryname in `odbc.ini` incorrectly, what if the `*.so` files can not be found, etc. In writing the wiki I used WSL2 and the command line only, this wiki should also note the use of GUI tools. The main reason I wrote the wiki is because if we get Discourse running at OSU OSL then we can add plugins and such that will allow others access, initially read only, to the data for the forum stored in a PostgreSQL database and this is a crucial step that is needed. In doing a proof of concept I needed to understand this and so shared what I learned via the wiki. The wiki should also try to add graphic representations to help explain things, e.g. the links in the chain. I plan to use [GraphViz](https://www.graphviz.org/) to generate such visuals and leave the [dot](https://www.graphviz.org/pdf/dotguide.pdf) files hidden in the post as HTML comments if possible or as link to another topic, possibly even a Discourse category holding just the Dot files. Need to explain with values in `odbc.ini` are optional. ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 7, 2020, 7:50am UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/3 "2020-06-07T07:50:17Z")
@EricGT It’s unclear to me where to put the odbc.ini file ? ---
**Author:** ![joeblog](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/joeblog/32/122_2.png) [@joeblog](https://swi-prolog.discourse.group/u/joeblog)\ **Post date:** [June 7, 2020, 8:55am UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/4 "2020-06-07T08:55:18Z")
The default file is ~/.odbc.ini ie it goes into the home directory (using Linux directory notation, mercifully I never have to use Microsoft) with a preceding dot to keep it hidden. The little documentation I’ve found is at [http://www.unixodbc.org/odbcinst.html](http://www.unixodbc.org/odbcinst.html) and I’ve made some notes on how I got it to work with SWI-Prolog at [https://github.com/roblaing/swipl-webapp-howto/tree/master/unit3](https://github.com/roblaing/swipl-webapp-howto/tree/master/unit3) ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 7, 2020, 10:25am UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/5 "2020-06-07T10:25:12Z")
> [@anniepoo](#): > > It’s unclear to me where to put the odbc.ini file ? ~~On the same machine for which I created the [Wiki](https://swi-prolog.discourse.group/t/swi-prolog-connecting-to-postgresql-via-odbc/2404), `.odbc.ini` is in `~` or `/home/` if you prefer.~~ Thanks for asking. 😀 I will take some time to review and probably enhance the wiki in the next few hours, so you might see some updates there and/or an edit here. **EDIT** Oops, I gave an answer for `.odbc.ini` and not `odbc.ini`, (The first starts with a period and is a different file). ([ref](http://manpages.ubuntu.com/manpages/cosmic/man1/odbcinst.1.html#files)) `odbc.ini` - `/etc/odbc.ini` `.odbc.ini` - `/home/eric/.odbc.ini` ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 7, 2020, 7:24pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/6 "2020-06-07T19:24:00Z")
So, at one point it suggests installing swi-prolog-odbc I didn’t do that, the odbc comes with standard install. Should that be in there? Also, I had issues with locale - forgive me, i’m going to copypasta my readme’s section on this ``` Run the odbctest.pl cd identiry/prolog/identity/store/sql/ swipl odbctest.pl ?- example. posts rows: row(0) posts rows: row(0) true. ``` If it instead gives you ``` ?- example. encoding name too long posts rows: row(0) posts rows: row(0) true. ``` You need to set locales # Set the locales Stupidly ODBC doesn’t seem to reasonably default on locales. Adding this to my .bashrc fixed it ``` export LANG="en_US.UTF-8" export LANGUAGE="en_US.UTF-8" export LC_CTYPE="en_US.UTF-8" export LC_NUMERIC="en_US.UTF-8" export LC_TIME="en_US.UTF-8" export LC_COLLATE="en_US.UTF-8" export LC_MONETARY="en_US.UTF-8" export LC_MESSAGES="en_US.UTF-8" export LC_PAPER="en_US.UTF-8" export LC_NAME="en_US.UTF-8" export LC_ADDRESS="en_US.UTF-8" export LC_TELEPHONE="en_US.UTF-8" export LC_MEASUREMENT="en_US.UTF-8" export LC_IDENTIFICATION="en_US.UTF-8" ``` ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 7, 2020, 7:33pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/7 "2020-06-07T19:33:39Z")
> [@anniepoo](#): > > the odbc comes with standard install. I know when I do a [Windows install](https://swi-prolog.discourse.group/t/install-swi-prolog-development-version-on-windows-10/1131) (Step 17) I see `ODBC interface` listed as a component that is chosen by default and I take it that you are correct about an Ubuntu install but the question I have is that I did the [Ubuntu install using the PPA](https://swi-prolog.discourse.group/t/install-swi-prolog-development-version-on-ubuntu-using-ppa/2402) and remember that I had to do the ODBC install step to get this to work because I could not get it to work as I expected it to work. But then again I was so lost when I started the ODBC wiki that if I start with a fresh machine I would now know what to look for to see if the ODBC is included with the PPA. How did you install on Ubuntu or did you not use Ubuntu? ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 7, 2020, 7:35pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/8 "2020-06-07T19:35:32Z")
I built 8.0.3 from sources. PPAs are not a recommended install path. ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 7, 2020, 7:37pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/9 "2020-06-07T19:37:27Z")
> [@anniepoo](#): > > Stupidly ODBC doesn’t seem to reasonably default on locales. While I did not elaborate on it in the Wiki because I tried to keep it to what was just needed, if you look in the example `odbc.ini` you will see `Driver = PostgreSQL ANSI` but if you look at `/etc/odbcinst.ini` you will see there is a choice of two 1. [PostgreSQL ANSI] 2. [PostgreSQL Unicode] I would try changing `odbc.ini` to use `Driver = PostgreSQL Unicode` instead. Sorry for all of the changes today if anyone was trying to read this, but little mistakes in something I intend to be a reference drive me nuts. ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 7, 2020, 7:42pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/10 "2020-06-07T19:42:48Z")
> [@anniepoo](#): > > I built 8.0.3 from sources. PPAs are not a recommended install path. I have not reached the point where I am building from sources for either Windows or Ubuntu and sadly it has been on my list for years but I think only when I need to do some C++ coding with SWI-Prolog will that finally happen. Based on your answer I need to create a new machine and do a PPA install to confirm. If this is correct I will update. Also the Wiki is editable by all so feel free to edit it. 😀 ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 7, 2020, 7:44pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/11 "2020-06-07T19:44:47Z")
That’s great - fixed it and isn’t the ugly locale setting hack. suggest updating the wiki, no? ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 7, 2020, 7:48pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/12 "2020-06-07T19:48:12Z")
Why does everyone consider it taboo to touch the wikis? I’ll do it for this one because my guess is you are busy preparing for a class. ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 7, 2020, 7:57pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/13 "2020-06-07T19:57:06Z")
done, just wanted consensus ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 7, 2020, 7:58pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/14 "2020-06-07T19:58:43Z")
Thanks. 😀 I still have to make changes to a few of the results in the test cases. I ran the tests and two of them need changing. ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 7, 2020, 7:59pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/15 "2020-06-07T19:59:53Z")
I just wanted to connect to PostgreSQL ---
**Author:** ![jan](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/jan/32/4_2.png) [@jan](https://swi-prolog.discourse.group/u/jan)\ **Post date:** [June 8, 2020, 6:49am UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/16 "2020-06-08T06:49:07Z")
> [@anniepoo](#): > > PPAs are not a recommended install path. Why not? If you want to foreign code development with SWI-Prolog or if you want to make changes or be able to apply the latest patch, the (git) source is best, but if you just follow the releases PPAs are just fine. ---
**Author:** ![anniepoo](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/anniepoo/32/12_2.png) [@anniepoo](https://swi-prolog.discourse.group/u/anniepoo)\ **Post date:** [June 8, 2020, 1:24pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/17 "2020-06-08T13:24:41Z")
Basically because you once said so. I hereby stand corrected. What’s the scoop on the swi-prolog-odbc in step 8 of [SWI-Prolog connecting to PostgreSQL via ODBC](https://swi-prolog.discourse.group/t/swi-prolog-connecting-to-postgresql-via-odbc/2404) ? I didn’t know that existed. ---
**Author:** ![jan](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/jan/32/4_2.png) [@jan](https://swi-prolog.discourse.group/u/jan)\ **Post date:** [June 8, 2020, 2:02pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/18 "2020-06-08T14:02:33Z")
> [@anniepoo](#): > > you once said so Who knows. I don’t recall. I also wouldn’t really know why I might have claimed that. The PPA is fine if you run Ubuntu Linux and just need Prolog with all the stuff that comes with it. > [@anniepoo](#): > > swi-prolog-odbc Debian likes small packages to allow setting up on small devices and docker containers with a small footprint. So, SWI-Prolog consists of a number of packages. The ODBC connector sits in its own package. That makes sense as it also avoids dependency on unixodbc. ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [June 9, 2020, 10:07am UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/19 "2020-06-09T10:07:29Z")
I made a Visio diagram related to the example. Since many users do not have Visio it was originally saved as an SVG but sadly Discourse still will not allow the upload of large SVG files so this is a PDF instead. Hopefully in the future when the OSU OSL site is working I can just add the SVG file to the web server and add a link bypassing Discourse SVG upload. [SWI-Prolog Discourse Wiki example.pdf](https://swi-prolog.discourse.group/uploads/short-url/fykPG4gjg1CsA9hSXfebJHJ0OL6.pdf) (88.8 KB) The reason I have not added the image to the Wiki is I want to add other Databases such as SQLite, add examples for Windows, add some more data sources possibly a text file and Excel file, use the different data source files that control visibility of the data source, e.g. System, user, possibly even add a different programming languages such as Python, maybe show how JDBC connects through ODBC, add a DSN-less connection and remove the user and password from the Data Source settings. ---
**Author:** ![emacstheviking](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/emacstheviking/32/228_2.png) [@emacstheviking](https://swi-prolog.discourse.group/u/emacstheviking)\ **Post date:** [June 9, 2020, 4:18pm UTC](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405/20 "2020-06-09T16:18:36Z")
Here’s mine if it helps. I used the command odbc inst: [https://www.systutorials.com/docs/linux/man/1-odbcinst/](https://www.systutorials.com/docs/linux/man/1-odbcinst/) to append it after creating a local template file. ```prolog ╰─$ cat ~/.odbc.ini [swi-pagila] Description=PostgreSQL Unicode Driver=PostgreSQL Unicode Trace=No TraceFile= Database=pagila Servername=localhost Username=sean Password=NOT HERE Port=5432 Protocol=6.4 ReadOnly=No RowVersioning=No ShowSystemTables=No ShowOidColumn=No FakeOidIndex=No ConnSettings= ``` [Next page](https://swi-prolog.discourse.group/t/wiki-discussion-swi-prolog-connecting-to-postgresql-via-odbc/2405.md?page=2)