MinimaJack commented 3 years ago

Info:

uname -a
Linux dev-ubt 5.4.0-80-generic #90-Ubuntu SMP Fri Jul 9 22:49:44 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

Steps:

git clone --recursive --branch v1.1.9.20201226 https://github.com/ClickHouse/clickhouse-odbc
cmake -DCH_ODBC_RUNTIME_LINK_STATIC=On -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
cmake --build . --config RelWithDebInfo --target package

SELECT CAST(database, 'Nullable(String)') AS TABLE_CAT, CAST(NULL, 'Nullable(String)') AS TABLE_SCHEM, CAST(name, 'Nullable(String)') AS TABLE_NAME, CAST('TABLE', 'Nullable(String)') AS TABLE_TYPE, CAST(NULL, 'Nullable(String)') AS REMARKS FROM system.tables WHERE (1 == 1) AND isNotNull(TABLE_CAT) AND coalesce(TABLE_CAT, '') LIKE 'system' AND isNotNull(TABLE_TYPE) AND (1 == 0 OR coalesce(TABLE_TYPE, '') LIKE 'TABLE' OR coalesce(TABLE_TYPE, '') LIKE 'VIEW') ORDER BY TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME

while use ANSI - driver make real request to server, but drops with error HY090 while use UNICODE - driver fail fast and no one request to clickhouse-server on Windows (by odbc-driver) and on web console http://ui.tabix.io/ - all works fine

wireshark screen with log:

driver log:

1629205636396129 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/statement.cpp:155 in requestNextPackOfResultSets: POST 172.30.98.3/query?default_format=ODBCDriver2&database=system body=SELECT CAST(database, 'Nullable(String)') AS TABLE_CAT, CAST(NULL, 'Nullable(String)') AS TABLE_SCHEM, CAST(name, 'Nu>
1629205636868035 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:684 in operator(): operator() column_number=1name=TABLE_CAT type=12 size=16777215 nullable=1
1629205636868080 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:684 in operator(): operator() column_number=2name=TABLE_SCHEM type=12 size=16777215 nullable=1
1629205636868088 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:684 in operator(): operator() column_number=3name=TABLE_NAME type=12 size=16777215 nullable=1
1629205636868094 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:684 in operator(): operator() column_number=4name=TABLE_TYPE type=12 size=16777215 nullable=1
1629205636868101 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:684 in operator(): operator() column_number=5name=REMARKS type=12 size=16777215 nullable=1
1629205636868113 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:706 in SQLFetch: SQLFetch
1629205636868153 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:733 in SQLGetData: SQLGetData
1629205636868184 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/driver.h:238 in operator(): HY090 (Invalid string or buffer length)[rc: -1]
1629205636868317 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:1221 in SQLCloseCursor: SQLCloseCursor
1629205636868431 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:56 in SQLFreeHandle: SQLFreeHandle handleType=3 handle=0x38cbc90
1629205636869987 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:784 in SQLDisconnect: SQLDisconnect
1629205636870003 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:56 in SQLFreeHandle: SQLFreeHandle handleType=2 handle=0x3843b10
1629205636870031 [2870709:139674593548032] /home/root/clickhouse-odbc/driver/api/odbc.cpp:56 in SQLFreeHandle: SQLFreeHandle handleType=1 handle=0x3976d10

MinimaJack commented 3 years ago

ldd libclickhouseodbcw.so
        linux-vdso.so.1 (0x00007ffe39597000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fcfbe220000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fcfbe02e000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fcfbe80b000)
        libiodbcinst.so.2 => /lib/x86_64-linux-gnu/libiodbcinst.so.2 (0x00007fcfbde1a000)
        libicuuc.so.66 => /lib/x86_64-linux-gnu/libicuuc.so.66 (0x00007fcfbdc34000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fcfbdc2e000)
        libicudata.so.66 => /lib/x86_64-linux-gnu/libicudata.so.66 (0x00007fcfbc16b000)
        libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fcfbbf89000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fcfbbe3a000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fcfbbe1f000)

 ldd libclickhouseodbc.so
        linux-vdso.so.1 (0x00007ffd7adb6000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fd01239d000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fd0121ab000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fd012989000)
        libiodbcinst.so.2 => /lib/x86_64-linux-gnu/libiodbcinst.so.2 (0x00007fd011f97000)
        libicuuc.so.66 => /lib/x86_64-linux-gnu/libicuuc.so.66 (0x00007fd011db1000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fd011dab000)
        libicudata.so.66 => /lib/x86_64-linux-gnu/libicudata.so.66 (0x00007fd0102e8000)
        libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fd010106000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fd00ffb7000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fd00ff9c000)

 isql -v "ClickHouse DSN (Unicode)"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
[H]C
[ISQL]ERROR: Could not SQLExecute

traceon commented 3 years ago

Please, try with branch master and let me known if it works there.

MinimaJack commented 3 years ago

@traceon nothing has changed Ничего не поменялось...

traceon commented 3 years ago

What is the version of clickhouse server?

Can you try specifying playground server and try with it? https://clickhouse.tech/docs/en/getting-started/playground/

MinimaJack commented 3 years ago

docker image yandex/clickhouse-server:20.12.6.29-alpine, can install any. ClickHouse not in product state now(

i sink issue with iculib...on 1.6 odbc i can access to server and get some info and ClickHouseServer the same all time.

ubuntu odbc 1.6: windows odbc1.9:

application is 1C, and they already use iculib

playground not connected...or i can't prepere it

  isql -v "ClickHouse DSN play"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
[H]C
[ISQL]ERROR: Could not SQLExecute

odbc.ini

[ClickHouse DSN play]
Driver=ClickHouse ODBC Driver (UNICODE)
Server = play-api.clickhouse.tech
Database = datasets
UID = playground
PWD = clickhouse
Port = 8443
Proto = https
Timeout=60
SSLMode = allow

MinimaJack commented 3 years ago

First query from to clickhouse doing with bad query SELECT N'A' - for test or someother...can't control this(

odbc trace log

[ODBC][2918326][1629263037.372148][SQLAllocHandle.c][377]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x3572600
[ODBC][2918326][1629263037.372188][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x3b6d000
[ODBC][2918326][1629263037.372197][SQLDriverConnect.c][748]
                Entry:
                        Connection = 0x3b6d000
                        Window Hdl = (nil)
                        Str In = [Driver=ClickHouse ODBC Driver (UNICODE);
Host=172.30.98.3;
Port=8123;
User=default;
Database=system;
DriverLog=yes;
DriverLogFi...][length = 164 (SQL_NTS)]
                        Str Out = 0x7f9955d1fb40
                        Str Out Max = 1024
                        Str Out Ptr = 0x7f9955d1ff5e
                        Completion = 0
                UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

                DIAG [H] '

[ODBC][2918326][1629263037.372679][SQLDriverConnect.c][1639]
                Exit:[SQL_ERROR]
[ODBC][2918326][1629263037.372686][SQLGetDiagRec.c][687]
                Entry:
                        Connection = 0x3b6d000
                        Rec Number = 1
                        SQLState = 0x7f9955d1fbf2
                        Native = 0x7f9955d1fbf8
                        Message Text = 0x7f9955d1fc40
                        Buffer Length = 513
                        Text Len Ptr = 0x7f9955d1fbf0
[ODBC][2918326][1629263037.372691][SQLGetDiagRec.c][724]
                Exit:[SQL_SUCCESS]
                        SQLState = H
                        Native = 0x7f9955d1fbf8 -> 1
                        Message Text = [']
[ODBC][2918326][1629263037.372704][SQLGetDiagRec.c][687]
                Entry:
                        Connection = 0x3b6d000
                        Rec Number = 2
                        SQLState = 0x7f9955d1fbf2
                        Native = 0x7f9955d1fbf8
                        Message Text = 0x7f9955d1fc40
                        Buffer Length = 513
                        Text Len Ptr = 0x7f9955d1fbf0
[ODBC][2918326][1629263037.372708][SQLGetDiagRec.c][724]
                Exit:[SQL_NO_DATA]

MinimaJack commented 3 years ago

@traceon

reproduce same behavior with playground:

Variant 1

Add VerifyConnectionEarly=On - for cleanup variant

Driver=ClickHouse ODBC Driver (UNICODE);
Url=https://play-api.clickhouse.tech:8443/?user=playground&password=clickhouse&database=system;

~1C send multiple query for data, and first with wrong syntax.~

Variant 2

its truncation data: state drops all NYxxx except first symbol message drop all

Variant 3

not nullable buffer with trash info...DIAG [H] ' trace log with ANSI not contain DIAG data

[ODBC][3191801][1629276382.446069][SQLDriverConnect.c][748]
                Entry:
                        Connection = 0x373c000
                        Window Hdl = (nil)
                        Str In = [Driver=/usr/lib64/libclickhouseodbc.so;Url=https://play-api.clickhouse.tech:8443/?user=playground&password=clickhouse&database=s...][length = 195 (SQL_NTS)]
                        Str Out = 0x7f656a5a5b40
                        Str Out Max = 1024
                        Str Out Ptr = 0x7f656a5a5f5e
                        Completion = 0
                UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

[ODBC][3191801][1629276382.449391][SQLDriverConnect.c][1727]
                Exit:[SQL_SUCCESS]
                        Connection Out [Driver=/usr/lib64/libclickhouseodbc.so;Url=https://play-api.clic...]

traceon commented 3 years ago

reproduce same behavior with playground

Are you trying with isql/iusql or your application?

traceon commented 3 years ago

Let's make sure we reproduce things with isql so I can reproduce them myself too.

MinimaJack commented 3 years ago

same with unicode

 iusql -v "ClickHouse DSN play"
'
[ISQL]ERROR: Could not SQLDriverConnect

/etc/odbc.ini

[ODBC]
Trace     = 1
TraceFile = /tmp/odbc-driver-manager-trace.log
Debug     = 1
DebugFile = /tmp/odbc-driver-manager-debug.log

[ODBC Data Sources]
ClickHouse DSN (ANSI)=ClickHouse ODBC Driver (ANSI)
ClickHouse DSN (Unicode)=ClickHouse ODBC Driver (Unicode)
ClickHouse DSN play=ClickHouse DSN play

[ClickHouse DSN (ANSI)]
.....

[ClickHouse DSN (Unicode)]
....

[ClickHouse DSN play]
Driver=ClickHouse ODBC Driver (UNICODE)
Url=https://play-api.clickhouse.tech:8443/?user=playground&password=clickhouse&database=system
DriverLog=yes
DriverLogFile=/tmp/clickhouse-driver-log.log

traceon commented 3 years ago

ClickHouse DSN play=ClickHouse DSN play

Shouldn't this be ClickHouse DSN play=ClickHouse ODBC Driver (Unicode)?

Driver=ClickHouse ODBC Driver (UNICODE)

Could you also change this to Driver=ClickHouse ODBC Driver (Unicode) (the case).

Let me know of the results, and the ODBC trace, if possible. (Driver works for me locally in this configuration with the playground server.)

MinimaJack commented 3 years ago

  iusql -v "ClickHouse DSN (Unicode)"
'
[ISQL]ERROR: Could not SQLDriverConnect

odbc.ini

[ODBC]
Trace     = 1
TraceFile = /tmp/odbc-driver-manager-trace.log
Debug     = 1
DebugFile = /tmp/odbc-driver-manager-debug.log

[ODBC Data Sources]
ClickHouse DSN (ANSI)=ClickHouse ODBC Driver (ANSI)
ClickHouse DSN (Unicode)=ClickHouse ODBC Driver (Unicode)

[ClickHouse DSN (ANSI)]
Driver=ClickHouse ODBC Driver (ANSI)
Description=DSN (localhost) for ClickHouse ODBC Driver (ANSI)

[ClickHouse DSN (Unicode)]
Driver=ClickHouse ODBC Driver (UNICODE)
Url=https://play-api.clickhouse.tech:8443/?user=playground&password=clickhouse&database=system
DriverLog=no
DriverLogFile=/tmp/clickhouse-driver-log.log

trace.log

[ODBC][3577985][1629372592.184662][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x5637f4f77820
[ODBC][3577985][1629372592.184692][SQLAllocHandle.c][377]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x5637f4f77820
[ODBC][3577985][1629372592.184697][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x5637f4f78960
[ODBC][3577985][1629372592.184709][SQLDriverConnectW.c][290]
                Entry:
                        Connection = 0x5637f4f78960
                        Window Hdl = (nil)
                        Str In = [DSN=ClickHouse DSN (Unicode)][length = 28 (SQL_NTS)]
                        Str Out = (nil)
                        Str Out Max = 0
                        Str Out Ptr = (nil)
                        Completion = 0
                UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][3577985][1629372592.187888][SQLDriverConnectW.c][699]
                Exit:[SQL_ERROR]
[ODBC][3577985][1629372592.187911][SQLErrorW.c][397]
                Entry:
                        Connection = 0x5637f4f78960
                        SQLState = 0x7ffc5d4f3360
                        Native = 0x7ffc5d4f335c
                        Message Text = 0x7ffc5d4f3380
                        Buffer Length = 500
                        Text Len Ptr = 0x7ffc5d4f335a
[ODBC][3577985][1629372592.187921][SQLErrorW.c][439]
                Exit:[SQL_SUCCESS]
                        SQLState = [H]
                        Native = 0x7ffc5d4f335c -> 1
                        Message Text = [']
[ODBC][3577985][1629372592.187942][SQLErrorW.c][397]
                Entry:
                        Connection = 0x5637f4f78960
                        SQLState = 0x7ffc5d4f3360
                        Native = 0x7ffc5d4f335c
                        Message Text = 0x7ffc5d4f3380
                        Buffer Length = 500
                        Text Len Ptr = 0x7ffc5d4f335a
[ODBC][3577985][1629372592.187947][SQLErrorW.c][439]
                Exit:[SQL_NO_DATA]
[ODBC][3577985][1629372592.187952][SQLErrorW.c][482]
                Entry:
                        Environment = 0x5637f4f77820
                        SQLState = 0x7ffc5d4f3360
                        Native = 0x7ffc5d4f335c
                        Message Text = 0x7ffc5d4f3380
                        Buffer Length = 500
                        Text Len Ptr = 0x7ffc5d4f335a
[ODBC][3577985][1629372592.187957][SQLErrorW.c][524]
                Exit:[SQL_NO_DATA]
[ODBC][3577985][1629372592.187970][SQLFreeHandle.c][290]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x5637f4f78960
[ODBC][3577985][1629372592.187976][SQLFreeHandle.c][339]
                Exit:[SQL_SUCCESS]
[ODBC][3577985][1629372592.187981][SQLFreeHandle.c][220]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x5637f4f77820

traceon commented 3 years ago

You still have Driver=ClickHouse ODBC Driver (UNICODE). UNICODE -> Unicode.

traceon commented 3 years ago

Can you please send the content of ~/.odbc.ini, /etc/odbc.ini, ~/.odbcinst.ini, /etc/odbcinst.ini separately?

MinimaJack commented 3 years ago

UNICODE -> Unicode - no change.

Telegram, Zoom or AnyDesc - to show

in homedir files not exists

cat ~/.odbc.ini

cat ~/.odbcinst.ini
cat: /root/.odbcinst.ini: No such file or directory

  cat /etc/odbc.ini
[ODBC]
Trace     = 1
TraceFile = /tmp/odbc-driver-manager-trace.log
Debug     = 1
DebugFile = /tmp/odbc-driver-manager-debug.log

[ODBC Data Sources]
ClickHouse DSN (ANSI)=ClickHouse ODBC Driver (ANSI)
ClickHouse DSN (Unicode)=ClickHouse ODBC Driver (Unicode)

[ClickHouse DSN (ANSI)]
Driver=ClickHouse ODBC Driver (ANSI)
Description=DSN (localhost) for ClickHouse ODBC Driver (ANSI)

[ClickHouse DSN (Unicode)]
Driver=ClickHouse ODBC Driver (Unicode)
Url=https://play-api.clickhouse.tech:8443/?user=playground&password=clickhouse&database=system
DriverLog=no
DriverLogFile=/tmp/clickhouse-driver-log.log

 cat /etc/odbcinst.ini
[ODBC]
Trace=yes
TraceFile=/tmp/clickhouse-odbc-trace.log

[ODBC Drivers]
ClickHouse ODBC Driver (ANSI)=Installed
ClickHouse ODBC Driver (Unicode)=Installed
UsageCount=1

[ClickHouse ODBC Driver (ANSI)]
Description=ODBC Driver (ANSI) for ClickHouse
Driver=/usr/lib64/libclickhouseodbc.so
Setup=/usr/lib64/libclickhouseodbc.so
UsageCount=1

[ClickHouse ODBC Driver (Unicode)]
Description=ODBC Driver (Unicode) for ClickHouse
Driver=/usr/lib64/libclickhouseodbcw.so
Setup=/usr/lib64/libclickhouseodbcw.so
UsageCount=1

MinimaJack commented 3 years ago

Configuration is ok. change library to version 1.6 from tags and error with ' has gone

 iusql -v "ClickHouse DSN (Unicode)"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
SSL Exception: error:14007086:SSL routines:CONNECT_CR_CERT:certificate verify failed
[ISQL]ERROR: Could not SQLExecute

traceon commented 3 years ago

cat ~/.odbc.ini

If the file is empty, just remove it.

Add SSLMode=allow under [ClickHouse DSN (Unicode)] and try again.

What is the output of ldd /usr/lib64/libclickhouseodbc.so and ldd /usr/lib64/libclickhouseodbcw.so again?

MinimaJack commented 3 years ago

ldd

 ldd libclickhouseodbcw.so
        linux-vdso.so.1 (0x00007ffc35da2000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f8989e5a000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f8989c68000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f898a44c000)
        libiodbcinst.so.2 => /lib/x86_64-linux-gnu/libiodbcinst.so.2 (0x00007f8989a54000)
        libicuuc.so.66 => /lib/x86_64-linux-gnu/libicuuc.so.66 (0x00007f898986e000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f8989868000)
        libicudata.so.66 => /lib/x86_64-linux-gnu/libicudata.so.66 (0x00007f8987da5000)
        libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f8987bc3000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f8987a74000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f8987a59000)

 ldd libclickhouseodbc.so
        linux-vdso.so.1 (0x00007fff76fe1000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f0e8f1a6000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f0e8efb4000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f0e8f79a000)
        libiodbcinst.so.2 => /lib/x86_64-linux-gnu/libiodbcinst.so.2 (0x00007f0e8eda0000)
        libicuuc.so.66 => /lib/x86_64-linux-gnu/libicuuc.so.66 (0x00007f0e8ebba000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f0e8ebb4000)
        libicudata.so.66 => /lib/x86_64-linux-gnu/libicudata.so.66 (0x00007f0e8d0f1000)
        libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f0e8cf0f000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f0e8cdc0000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f0e8cda5000)

SSLMode=allow

1.9 - builded

 iusql -v "ClickHouse DSN (Unicode)"
'
[ISQL]ERROR: Could not SQLDriverConnect

1.6

 iusql -v "ClickHouse DSN (Unicode)"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1;
HTTP status code: 500
Received error:
Code: 516, e.displayText() = DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name (version 21.3.15.4 (official build))

MinimaJack commented 3 years ago

file ~/.odbc.ini - recreated

$  cat ~/.odbc.ini
$  rm ~/.odbc.ini
$  cat ~/.odbc.ini
cat: /home/xxxx/.odbc.ini: No such file or directory
$  iusql -v "ClickHouse DSN (Unicode)"
'
[ISQL]ERROR: Could not SQLDriverConnect
$  cat ~/.odbc.ini

traceon commented 3 years ago

Hm, I think I know what is happening.

/lib/x86_64-linux-gnu/libiodbcinst.so.2 is from libiodbc2. Are you sure your application is using iODBC? This is very unlikely in Linux, the de-facto standard there is UnixODBC. isql is UnixODBC tool, the iODBC and UnixODBC are not ABI-compatible.

Assuming your application is using UnixODBC, and nothing in your system depends on iODBC, please do this:

sudo apt-get remove libiodbc2 libiodbc2-dev
sudo apt-get install unixodbc unixodbc-dev

Remove any ClickHouse ODBC driver's binary from the system.
Remove the source repo folder of clickhouse-odbc.
Remove the build folder of clickhouse-odbc.
Clone clickhouse-odbc's master branch afresh (recursively).
Confugure using: cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo .., make sure the UnixODBC is detected and used as an ODBC provider.
Build.
Copy binaries according to drivers config from odbcinst.ini.
Try again with isql and let me know of results.

MinimaJack commented 3 years ago

work with iusql, but not with 1C ...

iusql -v "ClickHouse DSN (Unicode)"
[unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLDriverConnect
root@1cdev-ubt:/home/xxx/clickhouse-odbc/build/driver#  sudo nano /etc/odbc.ini
root@1cdev-ubt:/home/xxx/clickhouse-odbc/build/driver#   iusql -v "ClickHouse DSN (Unicode)"
==================== ODBC Driver logging session started (2021-08-19 16:35:29 MSK) ====================
ClickHouse ODBC Driver VERSION=1.1.9.20201226 SYSTEM=Linux-5.4.0-80-generic UnixODBC UNICODE ODBCVER=380 sizeof(void *)=8 sizeof(SQLTCHAR)=2 sizeof(wchar_t)=4
==================== ODBC Driver logging session started (2021-08-19 16:35:29 MSK) ====================
ClickHouse ODBC Driver VERSION=1.1.9.20201226 SYSTEM=Linux-5.4.0-80-generic UnixODBC UNICODE ODBCVER=380 sizeof(void *)=8 sizeof(SQLTCHAR)=2 sizeof(wchar_t)=4
1629380129429423 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/connection.cpp:127 in connect: Creating session with http://
1629380129429457 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/odbc.cpp:1258 in SQLGetFunctions: SQLGetFunctions:1258  id=999 ptr=0x7ffc68a1c7a0
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
1629380140967081 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/odbc.cpp:39 in SQLAllocHandle: SQLAllocHandle handle_type=3 input_handle=0x55fb03d601c0
1629380140967137 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/impl/impl.cpp:439 in operator(): GetStmtAttr: 10010 out_value=0x7ffc68a044e8 out_value_max_length=8
1629380140967180 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/impl/impl.cpp:439 in operator(): GetStmtAttr: 10011 out_value=0x7ffc68a044e8 out_value_max_length=8
1629380140967200 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/impl/impl.cpp:439 in operator(): GetStmtAttr: 10012 out_value=0x7ffc68a044e8 out_value_max_length=8
1629380140967247 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/impl/impl.cpp:439 in operator(): GetStmtAttr: 10013 out_value=0x7ffc68a044e8 out_value_max_length=8
1629380140967281 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/odbc.cpp:493 in SQLPrepareW: SQLPrepareW statement_text_size=-3 statement_text=0x7ffc68a13ff0
1629380140967345 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/odbc.cpp:503 in SQLExecute: SQLExecute
1629380140967381 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/statement.cpp:156 in requestNextPackOfResultSets: POST 172.29.161.4/query?default_format=ODBCDriver2&database=system body=select 1 UA=clickhouse-odbc/1.1.9.20201226 (Linux-5.4.0-80-generic) UNICODE
1629380140973498 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/odbc.cpp:556 in SQLColAttributeW: SQLColAttributeW(col=1, field=6)
1629380140973539 [3631875:139874429187904] /home/xxx/clickhouse-odbc/driver/api/odbc.cpp:556 in SQLColAttributeW: SQLColAttributeW(col=1, field=18)
+----+

| 1  |

+----+

MinimaJack commented 3 years ago

Any chance to force driver use custom encoding 'ANSI_X3.4-1968' - to 'UTF-8'

1C UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE' iusql UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

traceon commented 3 years ago

work with iusql, but not with 1C ...

That's a progress. And there was an obvious issue...

What exactly doesn't work in 1C right now? Have you made sure 1C is also using UnixODBC?

Any chance to force driver use custom encoding

That's the encoding for narrow-char strings, obviously. If you use a DSN that points to a Unicode version of the driver (libclickhouseodbcw.so), then by default the string types are reported as wide-char strings, unless the client app is requesting narrow-char strings explicitly. This may help with the proper handling of encoding between the driver and the app. In any case, it is usually a configuration at ODBC client app side on how to interpret narrow-char strings from the driver. Unfortunately, currently the driver only supports and always assumes that the strings that come from the ClickHouse server are in UTF-8, and it treats narrow-char strings that it passes to or gets from the ODBC client app as UTF-8.

MinimaJack commented 3 years ago

I check 1C to used libs I delete library with ANSI and all links to it - so they use with Unicode 100% Unfortunately i can't access to code of 1C. Has any chance to proper work? This will be great!

traceon commented 3 years ago

If some obvious lib usage issues are sorted out, please describe the problem you are seeing with 1C right now?

MinimaJack commented 3 years ago

https://user-images.githubusercontent.com/495246/130080891-089a358b-6201-4fe0-b73d-75b432029a5b.png Problem one - proper encoding in linux. 1C use UTF-16 in internal representation - and on screenshot drop every 2 symbol

On windows all ok

MinimaJack commented 3 years ago

open a new issue? This (build & work in ubuntu) looks finished

traceon commented 3 years ago

It's fine, we can use this issue.

1C use UTF-16 in internal representation - and on screenshot drop every 2 symbol

Looks like a typical wide-char issue between UnixODBC and iODBC (16-bit vs 32-bit.) Can you please make sure that 1C uses UnixODBC? Like lld-ing its binaries, etc.

MinimaJack commented 3 years ago

I check all *.so and binary and then write

MinimaJack commented 3 years ago

In official instruction 1C has dependency: apt get install libfreetype6 libgsf-1-common unixodbc glib2.0 so must be unixodbc

traceon commented 3 years ago

OK, then try to use ANSI driver in DSN.

MinimaJack commented 3 years ago

For ANSI AND Unicode the same string in trace file and same result UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

traceon commented 3 years ago

Is there a way I could reproduce it locally?

MinimaJack commented 3 years ago

i try to reproduce on free training version...

MinimaJack commented 3 years ago

WOW! I can reproduce on free version! How i can show you? telegram: https://t.me/minimajack or email?

MinimaJack commented 3 years ago

Has any news or need help to reproduce?

ClickHouse / clickhouse-odbc

Connection not worked on Ubuntu #374

reproduce same behavior with playground:

Variant 1

Variant 2

Variant 3

ldd

SSLMode=allow

On windows all ok