Wiki Discussion: SWI-Prolog connecting to PostgreSQL via ODBC

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

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

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?

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

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

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:

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

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

done, just wanted consensus

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

I just wanted to connect to PostgreSQL

1 Like

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

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.

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

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

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

I did read your blog page a few days ago when you posted it and it seems you have since modified your Data Source entry. The part I liked/learned from you blog was about using the database in the real world with multiple users. That is something I have not even broached with what I am doing.

Feel free to add to the Wiki but I will check any change in excruciating detail.

1 Like

wow, love this

1 Like

After I updated the Wiki I was trying to figure out why the page view count was going up so fast.
Then I found

Thanks.


Looking at the Twitter OneBox (ref) it looks like another task for me to figure out why it does not look right.

1 Like

OK - I control that account, so let me know if you want more stuff like that tweeted.

1 Like