ClickHouse / ClickHouse/clickhouse-odbc
Linked server using ODBC - Syntax error: Not a valid integer: DC9
- Dominant language
- C
- Stars
- 285
- Forks
- 105
- Avg merge
- 3h 56m
- Merged PRs (30d)
- 3
Description
Clickhouse ODBC version: clickhouse-odbc-1.4.3-win64.msi
We are using this clickhouse ODBC driver to create Linked server from Microsoft SQL Server (version 2022).
We have a select query that imports data from clickhouse into sql server within a while loop using openquery. The date, col3_int and col4_int values change within the while loop. All columns referred in the query are integers except 1 date column.
And this script randomly fails with error like - [OLE DB provider "MSDASQL" for linked server "pm-clickhouse-etl" returned message "Syntax error: Not a valid integer: xxx".] The xxx varies for each query as shown in below samples. And the same queries works fine when i rerun.
**Samples**:
`select * from openquery([clikchouse-linked-server], 'select col1_int, fact_int from clickhouse_table1 where col1_int < 1000056831 and date=''2025-09-27'' and col3_int =133 and col4_int =191')`
_Failed with "Syntax error: Not a valid integer: DC9"._
`select * from openquery([clikchouse-linked-server], 'select col1_int, fact_int from clickhouse_table1 where col1_int < 1054100932 and date=''2025-10-02'' and col3_int =131 and col4_int =16')`
_Failed with "Syntax error: Not a valid integer: D6A"_
ODBC trace log:
```
1760375440336695 [3664:3944] C:\a\clickhouse-odbc\clickhouse-odbc\source\driver/driver.h:248 in operator (): HY000 (Syntax error: Not a valid integer: CDF)
1760375440338545 [3664:3944] C:\a\clickhouse-odbc\clickhouse-odbc\source\driver\api\odbc.cpp:74 in SQLFreeStmt: SQLFreeStmt option=0
1760375440340968 [3664:3944] C:\a\clickhouse-odbc\clickhouse-odbc\source\driver\api\odbc.cpp:59 in SQLFreeHandle: SQLFreeHandle handleType=3 handle=000001A7AA655BB0
1760375519623125 [3664:5556] C:\a\clickhouse-odbc\clickhouse-odbc\source\driver\api\odbc.cpp:790 in SQLDisconnect: SQLDisconnect
1760375519623172 [3664:5556] C:\a\clickhouse-odbc\clickhouse-odbc\source\driver\api\odbc.cpp:59 in SQLFreeHandle: SQLFreeHandle handleType=2 handle=000001ABB5ED81F0
1760375519623196 [3664:5556] C:\a\clickhouse-odbc\clickhouse-odbc\source\driver\api\odbc.cpp:59 in SQLFreeHandle: SQLFreeHandle handleType=1 handle=000001A7A7605FB0
```
Contributor guide
Research direction
Start with the driver trace locations in source/driver/driver.h and source/driver/api/odbc.cpp, then reproduce the supplied OPENQUERY statements through SQL Server 2022 and the clickhouse-odbc-1.4.3 driver. Compare successful and failing runs to determine where values such as DC9, D6A, and CDF are parsed; done means the intermittent invalid-integer failure is explained and no longer occurs for the reported queries.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100