ODBC problems since 18.6.0

[Jim Hague]

HI all,

We're currently importing dictionaries into a Clickhouse 18.5.1 server from a Postgres server via ODBC. Clickhouse is installed from the Yandex APT repo, onto Ubuntu Xenial and Bionic.

I've not been able to get ODBC working with the same configuration with any Clickhouse version from 18.6.0 onwards. A simple test like:

:) select * from odbc('DSN=wombat', 'node_text');

works fine on 18.5.1, but fails when the machine is upgraded to later versions with:

Received exception from server (version 19.11.3):

Code: 86. DB::Exception: Received from localhost:9000. DB::Exception: Received error from remote server /columns_info?connection_string=DSN%3Dwombat&table=node_text. HTTP status code: 500 Internal Server Error, body: Error getting columns from ODBC 'Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = Connection attempt failed: Connection:Not applicable

Server:wombat

===========================

ODBC Diagnostic record #1:

===========================

SQLSTATE = 01000

Native Error Code = 0

[unixODBC][Driver Manager]Can't open lib 'psqlodbcw.so' : file not found

Wireshark shows that no traffic is sent to the Postgres server. isql continues to work normally.

I think that 18.6.0 was when Clickhouse switched to using clickhouse-odbc-bridge. Is there some extra setup magic this needs that I've missed?

Thanks.

[Denis Zhuravlev]

Check that your  /etc/odbc.ini has DRIVER = /usr/local/lib/libmyodbc5w.so

https://clickhouse.yandex/docs/en/query_language/table_functions/odbc/#usage-example

[Jim Hague]

On Monday, 22 July 2019 21:54:18 UTC+1, Denis Zhuravlev wrote:

Check that your  /etc/odbc.ini has DRIVER = /usr/local/lib/libmyodbc5w.so

Thanks! That was the necessary clue.

I was using a setup with an indirect driver spec:

/etc/odbc.ini:

[wombat]

Description         = Connection to Wombat PostgreSQL

Driver              = PostgreSQL Unicode

Database            = wombat

Servername          = pg1

UserName            = wombat

Password            = wombat

Port                = 5432

Protocol            = 10.6

ReadOnly            = Yes

RowVersioning       = No

ShowSystemTables    = No

ConnSettings        =

/etc/odbcinst.ini:

[PostgreSQL Unicode]

Description=PostgreSQL ODBC driver (Unicode version)

Driver=psqlodbcw.so

Setup=libodbcpsqlS.so

Debug=0

Logdir=/tmp/ch

CommLog=0

UsageCount=1

This works fine with isql, and with 18.5.1 and previous. It does not work with 18.6.0 and later.

I've changed /etc/odbc.ini to:

[wombat]

Description         = Connection to Wombat PostgreSQL

Driver              = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so

Database            = wombat

Servername          = pg1

UserName            = wombat

Password            = wombat

Port                = 5432

Protocol            = 10.6

ReadOnly            = Yes

RowVersioning       = No

ShowSystemTables    = No

ConnSettings        =

The full path to psqlodbcw.so is necessary.

[Denis Zhuravlev]

good. And AFAIK this change came from poco (external) lib. with poco version update.