Wiki Discussion: SWI-Prolog connecting to PostgreSQL via ODBC

Wiki Discussion
1

This is a topic to discuss the wiki

SWI-Prolog connecting to PostgreSQL via ODBC

2

Some points of note related to odbc.ini entries, e.g.

[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    = <user name>
Password    = <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.

    Update: It seems I should not be putting the specific drivers in odbc.ini, e.g.

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)

  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 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 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 to generate such visuals and leave the dot 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.

1 Like
3

@EricGT It’s unclear to me where to put the odbc.ini file ?

1 Like
4

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

1 Like
5

On the same machine for which I created the Wiki, .odbc.ini is in ~ or /home/<your login> if you prefer.

Thanks for asking. :grinning:

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)

odbc.ini - /etc/odbc.ini
.odbc.ini - /home/eric/.odbc.ini

1 Like
6

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"
1 Like
7

I know when I do a Windows install (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 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?

8

I built 8.0.3 from sources. PPAs are not a recommended install path.

9

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.

1 Like
10

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. :grinning:

11

That’s great - fixed it and isn’t the ugly locale setting hack. suggest updating the wiki, no?

12

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.

2 Likes
13

done, just wanted consensus

14

Thanks. :grinning:

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.

1 Like
15

I just wanted to connect to PostgreSQL

1 Like
16

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.

1 Like
17

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 ?
I didn’t know that existed.

18

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.

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.

2 Likes
19

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

1 Like
20

Here’s mine if it helps. I used the command odbc inst:
https://www.systutorials.com/docs/linux/man/1-odbcinst/

to append it after creating a local template file.

╰─$ 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=
1 Like