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

On 17/07/15 15:48, Stefan Viljoen wrote:
> Hi Nick
>
>> Getting confused now. The above leads me to believe you should have
>> Database = asteriskcdrcdrdb
>> in the odbc.ini entry and connect as
>> isql -v pgdb-cdr asteriskcdruser whateverthepasswordis
>> I was assuming you were replacing dbuser, dbname and password with the real
> ones for your situation.
>
> That is a typeo I made. You are correct, I'm replacing with the real ones -
> made a typo.
>
> My odbc.ini actually is
>
> --
> [root(at)jhbasterisk pg_log]# vim /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=dbuser
> Password=dbpassword
> Database=dbname
> ReadOnly=No
> Protocol=9.4
> Trace=Yes
> TraceFile=/tmp/sql.log
> ConnSettings=
> DSN=pgdb
> ServerType=Postgres
> Debug=1
> DebugFile=/var/log/postgresql_debug.log
> --
>
> e. g. I have now checked that the symantic
>
> dbname
>
> is NOT
>
> dbnamedname
>
> If I do
>
> [root(at)jhbasterisk pg_log]# isql -v pgdb-cdr dbuser dbpassword
> [S1000][unixODBC]The database does not exist on the server
> or user authentication failed.
> [ISQL]ERROR: Could not SQLConnect
> [root(at)jhbasterisk pg_log]#
>
> The interesting thing is if I change
>
> Database=dbname
>
> to symantic
>
> Database=dnam
>
> (e. g. leave out the last char)
>
> isql does this:
>
> [root(at)jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc#
> [08S01][unixODBC]Could not send Query(connection dead)
> [ISQL]ERROR: Could not SQLConnect
> [root(at)jhbasterisk pg_log]#
>
> So, my theory that unixODBC is not trying to connect or get far enough to
> even touch Postgres is incorrect... therefore is DOES appear that it IS
> postgress that is rejecting the ODBC connections?
>
> Kind regards
>
> Stefan
>

Its getting past the driver manager to the driver in all cases, those
errors are from postgres driver or server.

So when you do

psql -U asteriskcdruser -d asteriskcdrdb

That does connect you are connecting to a different database, dont you need to change the odbc.ini to have

Database=asteriskcdrdb

instead of the

Database=dbname

That you have shown to be in the ini file.

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

--
Nick