PostgreSQL ODBC connection

Dear all,

I’m trying to set up a connection to a PostgreSQL database as external data source. I’m using the odbc-postgresql driver and I have two DSNs configured, one for ANSI and the other one for Unicode.

So far I’m able to connect to the two data sources via isql and I’m also able to connect to the ANSI DSN via the Virtuoso Conductor. Unfortunately the Unicode DSN returns me a not easy to understand error:

My odbcinst.ini is as follows:

[ODBC Drivers]
OpenLink Virtuoso=Installed
OpenLink Virtuoso (Unicode)=Installed
PostgresSQL ANSI=Installed
PostgresSQL Unicode=Installed

[OpenLink Virtuoso]
Driver=/opt/virtuoso/lib/virtodbc_r.so

[OpenLink Virtuoso (Unicode)]
Driver=/opt/virtuoso/lib/virtodbcu_r.so

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=/usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so

My odbc.ini is as follows:

[ODBC Data Sources]
Local Virtuoso = OpenLink Virtuoso
Local Virtuoso (Unicode) = OpenLink Virtuoso
PostgreSQLdevANSI = PostgreSQL
PostgreSQLdevUnicode = PostgreSQL

[Local Virtuoso]
Driver = OpenLink Virtuoso
Address = localhost:1111

[Local Virtuoso (Unicode)]
Driver = OpenLink Virtuoso
Address = localhost:1111

[PostgreSQLdevANSI]
Description=PostgreSQL ANSI dev
Driver=PostgreSQL ANSI
Database=Test 
Servername=localhost
UserName=www-data
Password=www-data
Port=5432

[PostgreSQLdevUnicode]
Description=PostgreSQL Unicode dev
Driver=PostgreSQL Unicode
Database=Test
Servername=localhost
UserName=www-data
Password=www-data
Port=5432

Could you please help me debug this?
Many thanks

Pasquale

If as I would assume the Postgres ODBC drivers (ANSI & Unicode) were build against the unixODBC driver manager, which is known to have unicode differences compared to iODBC, which Virtuoso is built against, then suspect that would be the problem you are encountering.

Note OpenLink provide a suite of ODBC drivers built against iODBC which it maintains, including for Postgres (ANSI & unicode) and have been tested and known to work against Virtuoso. These drivers are available for free 30 day evaluation

Many thanks for the input. I’ll try to compile against iodbc.

Could you please help decipher this error:

VD052: Remote DSN PostgreSQL (dev) Unicode: in vdd_dsn_info:(BIF), DB.DBA.vd_remote_data_source

Has your compilation of the unicode Postgres ODBC driver against iODBC not resolved the problem then ?

You can enable ODBC tracing and possibly Virtuoso Tracing to obtain more information on the cause of the failure to connect …

So far I’ve been able to have a working setup on my personal linux box. I’ll now try to reproduce this in the AMI.
Thanks for the support