Setup unixODBC to access PostgreSQL

[ONEEYEMAN]

H, ALL,

Code: Select all

IgorsGentoo /usr/share/postgresql-13 # cat /etc/unixODBC/odbcinst.ini 
Trace=yes
TraceFile=/tmp/sql.log
[PostgreSQL]
Description=PostgreSQL drver
Driver=/usr/lib64/psqlodbcw.so

IgorsGentoo /usr/share/postgresql-13 # cat /etc/unixODBC/odbc.ini 
[PostgreSQL]
Driver=PostgreSQL
Server=localhost
PORT=5432
DATABASE=draft
OOPTION=3
USER=postgres
PASSWORD=wasqra
IgorsGentoo /usr/share/postgresql-13 # 

Code: Select all

igor@IgorsGentoo ~/dbhandler_new/Debug/dbhandler $ isql -v PostgreSQL postgres wasqra
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLConnect
igor@IgorsGentoo ~/dbhandler_new/Debug/dbhandler $ 

Code: Select all

igor@IgorsGentoo ~/dbhandler_new/Debug/dbhandler $ ls -la /tmp/
total 54892
drwxrwxrwt  9 root root     4096 Feb 26 02:32 .
drwxr-xr-x 21 root root     4096 Nov 23 16:15 ..
drwxrwxrwt  2 root root     4096 Nov 23 04:55 .ICE-unix
-r--------  1 igor igor 56154080 Dec  8 03:26 mysql-5.7.32.tar.gz
drwx------  2 igor igor     4096 Nov 23 04:55 plasma-csd-generator.lzhjPj
-rw-------  1 igor igor      168 Nov 23 04:55 serverauth.YmVP5sLD7a
drwx------  3 igor igor     4096 Dec  1 00:09 Temp-617a0e2d-a1d0-4a41-9170-a648fbea276e
drwx------  2 igor igor     4096 Dec 27 14:59 tmp.mXhrbHkLYs
drwx------  2 igor igor     4096 Dec  6 14:38 tmp.oYuHPjV2MY
drwx------  2 igor igor     4096 Nov 23 21:00 tmp.UPGWkilxF6
-rw-------  1 igor igor      978 Nov 23 04:55 .touchpaddefaults
-r--r--r--  1 igor igor       11 Nov 23 04:55 .X0-lock
drwxrwxrwt  2 root root     4096 Nov 23 04:55 .X11-unix
-rw-------  1 igor igor       56 Nov 23 04:55 xauth-1000-_0
igor@IgorsGentoo ~/dbhandler_new/Debug/dbhandler $ 

2 questions:
1. Why I can't connect?
2. Why no log file is created?

Thank you.

[logrusx]

This is your previous thread on the issue:

viewtopic-p-8849564.html

I posted a replay there, you never followed up.

Let me remind you. The default port is 5432. I see you've corrected that, but:

Did you disable minimal use flag?

I happen to have it pulled in by libreoffice, but it does not require any drivers, so minimal use flag is enabled by default. When I disabled it, new files appeared, namely:

Code: Select all

# equery f unixODBC
...
/usr/lib64/libodbcpsql.so -> libodbcpsql.so.2.0.0
/usr/lib64/libodbcpsql.so.2 -> libodbcpsql.so.2.0.0
...

Best Regards,
Georgi

[logrusx]

Disregard the minimal use flag. With it I hit the version limitation of the driver included in unixODBC which is 2 and the server supports only 3.

By installing dev-db/psqlodbc-16.00.0000, maybe 15 will also work but 11 does not compile, and reducing your configs to the following:

Code: Select all

[PostgreSQL]
Description=PostgreSQL drver
Driver=/usr/lib64/psqlodbcw.so

and

Code: Select all

[PostgreSQL]
Description         = Test to Postgres
Driver              = PostgreSQL
Trace               = Yes
TraceFile           = sql.log
Database            = 
Servername          = localhost
UserName            = 
Password            = 
Port                = 5432

in my regular user's home directory I was able to do that:

Code: Select all

$ isql -v PostgreSQL
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

Adjust DB, user and password or pass them on the command line. I wouldn't suggest passing them on the command line as they would be logged into your bash history.

So, did you miss dev-db/psqlodbc? If not, this might be a completely different issue.

p.s. the log file is for your sql commands, not for the isql tool. Or rather for the ODBC driver itself, not a substitute for the system log. The error you're expecting to find there is printed on the command line and it pretty clearly states the driver cannot be found.

Best Regards,
Georgi

[ONEEYEMAN]

Hi,

Code: Select all

IgorsGentoo /home/igor/dbhandler_new/Debug # emerge -pv psqlodbc
 * Last emerge --sync was 3y 20d 20h 16m 32s ago.

These are the packages that would be merged, in order:

Calculating dependencies... done!
[ebuild   R    ] dev-db/psqlodbc-11.01.0000::gentoo  USE="ssl -doc -iodbc -threads" 0 KiB

Total: 1 package (1 reinstall), Size of downloads: 0 KiB

 * IMPORTANT: 20 news items need reading for repository 'gentoo'.
 * Use eselect news read to view new items.

IgorsGentoo /home/igor/dbhandler_new/Debug # 

I do have a driver installed...

Than you.

[logrusx]

Hi,

Code: Select all

IgorsGentoo /home/igor/dbhandler_new/Debug # emerge -pv psqlodbc
 * Last emerge --sync was 3y 20d 20h 16m 32s ago.

These are the packages that would be merged, in order:

Calculating dependencies... done!
[ebuild   R    ] dev-db/psqlodbc-11.01.0000::gentoo  USE="ssl -doc -iodbc -threads" 0 KiB

Total: 1 package (1 reinstall), Size of downloads: 0 KiB

 * IMPORTANT: 20 news items need reading for repository 'gentoo'.
 * Use eselect news read to view new items.

IgorsGentoo /home/igor/dbhandler_new/Debug # 

I do have a driver installed...

Than you.

Well, I suspect that version is not gonna cut it. Try at least the next version. Or try previous version of unixODBC although I suspect the server will reject it for too low version of back end protocol.

Also I remember something about the drivers being renamed between versions. Did you check it contains that file you specified in the ini file? What does

Code: Select all

equery f psqlodbc

say? Does it contain the file or is it something that starts with lib?

Anyway, version 11 is way too old. I don't know how you even compiled it. Is this another stale installation?

EDIT: actually there isn't anything psql-11 in the tree so you're stepping on the wrong foot here just like with mysql. The oldest psql in tree is 13, so you better go with at least psqlodbc-13. Ideally it'll correspond to your psql instance version.

Best Regards,
Georgi

[ONEEYEMAN]

H,
Driver is defnitely here:

Code: Select all

IgorsGentoo /home/igor/dbhandler_new/Debug # ls -la /usr/lib64/psq*
-rw-r--r-- 1 root root    969 Dec 23 04:09 /usr/lib64/psqlodbca.la
-rwxr-xr-x 1 root root 514712 Dec 23 04:09 /usr/lib64/psqlodbca.so
-rw-r--r-- 1 root root    969 Dec 23 04:09 /usr/lib64/psqlodbcw.la
-rwxr-xr-x 1 root root 551616 Dec 23 04:09 /usr/lib64/psqlodbcw.so
IgorsGentoo /home/igor/dbhandler_new/Debug # 

Ad yes, it does contain the file:

Code: Select all

IgorsGentoo /home/igor/dbhandler_new/Debug # equery f psqlodbc
 * Searching for psqlodbc ...
 * Contents of dev-db/psqlodbc-11.01.0000:
/usr
/usr/lib64
/usr/lib64/psqlodbca.la
/usr/lib64/psqlodbca.so
/usr/lib64/psqlodbcw.la
/usr/lib64/psqlodbcw.so
/usr/share
/usr/share/doc
/usr/share/doc/psqlodbc-11.01.0000
/usr/share/doc/psqlodbc-11.01.0000/readme.txt.bz2
IgorsGentoo /home/igor/dbhandler_new/Debug # 

In regards to your edit - it probably gve a different error message. But I'll check with PostgreSQL devs just iin cast...

Thank you.

[Hu]

Anyway, version 11 is way too old. I don't know how you even compiled it. Is this another stale installation?

Yes:

Code: Select all

IgorsGentoo /home/igor/dbhandler_new/Debug # emerge -pv psqlodbc
 * Last emerge --sync was 3y 20d 20h 16m 32s ago.

[logrusx]

Anyway, version 11 is way too old. I don't know how you even compiled it. Is this another stale installation?

Yes:

Code: Select all

IgorsGentoo /home/igor/dbhandler_new/Debug # emerge -pv psqlodbc
 * Last emerge --sync was 3y 20d 20h 16m 32s ago.

I missed that.

Igor, nobody can help you. It's pointless to ask for help in this situation. Even if I wanted to go back and recreate an installation so old, I doubt I would be able to find all necessary distfiles. But I doubt anybody would either.

Best Regards,
Georgi

[sam_]

It's one of these things which is in a sense disrespectful, too. Both not to make it very clear up front, and also as it implies that surely nothing could have possibly fixed it in the intervening period - and that people are expected to remember every possible fix in that time and direct you to it.

[logrusx]

Sam, since you entered in the picture, maybe psqlodbc-11 should be dropped. I'm not very knowledgeable in Postgre outside of just using it, but I think psqlodbc version 11 pertains to postgreqsl version 11 but there's no such version in tree. In the same time psqlodbc-11 is stable in tree but it can't even be compiled.

Best Regards,
Georgi

[sam_]

Let me take a look.

[logrusx]

Actually they all break up to 15 with the same error which I believe is related to GCC or glibc - something related to the definition of bool. Once upon a time when I knew some C/C++ it was an integrated type which I did not have to include.

But that has probably something to do with the header files installed by my version of postgresql - 17:

Code: Select all

/usr/include/postgresql-17/internal/pqexpbuffer.h:1:1: note: 'bool' is defined in header '<stdbool.h>'; this is probably fixable by adding '#include <stdbool.h>'
  +++ |+#include <stdbool.h>

Best Regards,
Georgi

[sam_]

Indeed -- just hit the same. I've stabled 16 (hit the same error as you when testing 15), cleaned up, and added new upstream location. I don't plan on looking at adding 17.