Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi again Nick

>Another thing to check is that the driver is reading the same ini file as
the driver manager.

>What error do you get from

>isql -v pgdb-cdr dbuser password

I get

---
[root(at)jhbasterisk pg_log]# isql -v pgdb-cdr dbuser password
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
[root(at)jhbasterisk pg_log]#
---

>It may be the driver (the folks on this list will know better that I do) is
reading the ini file itself, so it may be looking elsewhere (instead of
using the libodbcinst.so helper lib).

>Try setting

>export ODBCINI=/path/to/odbc.ini

Ok, I tried that

---
[root(at)jhbasterisk pg_log]# export ODBCINI=/etc/odbc.ini
[root(at)jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc#
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
[root(at)jhbasterisk pg_log]# set | grep ODBCINI
ODBCINI=/etc/odbc.ini
[root(at)jhbasterisk pg_log]#
---

so I get the same error , and ODBCINI is set in the environment after
exporting the symbol ODBCINI - odbc.ini IS in /etc:

---
[root(at)jhbasterisk pg_log]# ls /etc/odbc.ini -l
-rw-r--r-- 1 root root 646 Jul 17 16:00 /etc/odbc.ini
[root(at)jhbasterisk pg_log]#
---

>That may help. also try removing the white space before and after the =

Ok, I removed whitespace in the postgress - relevant lines in odbcinst.ini
and odbc.ini - the now look like this:

/etc/odbcinst.ini:
---
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description=ODBC for PostgreSQL
;Driver = /usr/lib/psqlodbc.so
Driver=/usr/pgsql-9.4/lib/psqlodbc.so
Setup=/usr/lib/libodbcpsqlS.so
;Driver64 = /usr/lib64/psqlodbc.so
Driver64=/usr/pgsql-9.4/lib/psqlodbc.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
---

/etc/odbc.ini:
---
[ODBC Data Sources]
pgdb=PostgreSQL

[asterisk-cdr]
Description=MySQL connector for Asterisk
Driver=MySQL
Database=asteriskcdrdb
Socket=/var/lib/mysql/mysql.sock

[pgdb-cdr]
;Driver = /usr/pgsql-9.4/lib/psqlodbc.so
Driver=PostgreSQL
ServerName=localhost
Port=5432
UserName=asteriskcdruser
Password=1ndestruc#
Database=asteriskcdrdb
ReadOnly=No
Protocol=9.4
Trace=Yes
TraceFile=/tmp/sql.log
ConnSettings=
DSN=pgdb
ServerType=Postgres
Debug=1
DebugFile=/var/log/postgresql_debug.log
---

The error remains the same, after restarting postgress (which should not
have any effect nonetheless):

[root(at)jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc#
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
[root(at)jhbasterisk pg_log]#

psql still works:

[root(at)jhbasterisk pg_log]# psql -U asteriskcdruser -d asteriskcdrdb
psql (9.4.4)
Type "help" for help.

asteriskcdrdb=>

but that is because it does not try to use ODBC.

Thanks a lot for the help.

Kind regards

Stefan