Can't fetch strings over ClickHouse odbc driver

[leotausanovic]

I'm trying to fetch data from Clickhouse over ClickHouse ODBC driver using MSSQL OPENROWSET command.
I successfully fetch numeric types, but not strings - here are examples:

• ANSI driver

Numeric -> success

String -> error

• UNICODE driver

Numeric -> success

String -> error

ClickHouse version = 20.3.15
ODBC driver version = 1.1.8.20200710 (Windows 64 bit)
SQL.LOG file couldn't be generated (tried all the tricks - searching in different temp folders, machine trace, tampering with registry, but with no avail )

Thank you in advance

[filimonov]

SQL.LOG file couldn't be generated

Did you try this https://github.com/ClickHouse/clickhouse-odbc#enabling-driver-manager-tracing-mdacwdac-microsoftwindows-data-access-components ?

[leotausanovic]

Hi,

forgot to restart sql server svc after trace had started. :)

Here it is.

SQL.LOG

[traceon]

Thanks, @leotausanovic.

This PR should address the issue: #316.

I haven't tested it in your specific environment, so if you can, please build the driver from that branch and tell me if it actually fixes the problem for you. If you can't build the driver yourself, let me know, I'll prepare a binary for you.

[leotausanovic]

Hi @traceon ,
could you be so kind to provide a binary?
Thank you in advance!

[traceon]

@leotausanovic sure, 32 or 64 bit?

[leotausanovic]

@traceon 64bit, pretty please :)

[traceon]

@leotausanovic Here you go: clickhouse-odbc-1.1.8-win64.msi.zip

[leotausanovic]

@traceon thx a lot, will revert you with test results during next week.

[leotausanovic]

Hi, here is 2nd round of testing, using version of ODBC driver provided last week.

Now we have elaborate error, and due to that I modifed my set of so-called "test cases" by adding fixed-length string example.

Here are results of this round:

• ANSI

String -> error

Fixed Length String -> success

Numeric -> success

• UNICODE

String -> error

Fixed Length String -> success

Numeric -> success

Note- fixed length string is translated on SQL Server side to varchar(length) where length is same as fixed length parameter used in the call of ClickHouse function.
Destination datatype is same (varchar) regardless of driver version used (ANSI/UNICODE).
Of course, data in SQL Server destination is right padded with empty spaces.
Using such solution isn't always feasible in that way.

Here is SQL.LOG file

SQL.LOG

So, we can say there is workaround. But it isn't always feasible - due to right padding of destination result set.
It also complicates query building for our clients, especially if they just want to preliminary investigate data.

It would be really nice if we could execute query like this one over odbc without errors:

Nevertheless - thank you very much for your effort and kind assistance!

[traceon]

That SQL.LOG doesn't contain the part where the driver returns "Requested conversion is not supported." So it is impossible to tell what kind of call caused this. Meanwhile, the original issue seems to be resolved, and this one is separate.

[leotausanovic]

Hi @traceon , I saw there is possibility of internal ClickHouse tracing (not standard SQL.LOG trace), maybe this would be helpful?

[traceon]

No, and this "Requested conversion is not supported." actually not from the driver. You probably need to align column types.

[traceon]

https://stackoverflow.com/questions/10122693/wide-varchar-field-causes-requested-conversion-is-not-supported-error-using-op

[leotausanovic]

Yes, will do - thanks for your help!

[dashtimehran]

I have similar issue in retrieving String data type in php using odbc. I get Out of memory (allocated 8388608) (tried to allocate 18446744073709551613 bytes)

[traceon]

@dashtimehran what kind of issue exactly?

[gfody]

this issue is resolved but I haven't been able to fetch strings via openrowset, what's the workaround?

edit: the workaround is to use toFixedString() in the clickhouse query, also these linked server settings seem important:
use remote collation = true
collation name = Latin1_General_100_BIN2_UTF8