Unable to connect to Informix with pyodbc 5.x.x

[gbuzic]

I cannot connect to the Informix server using pyodbc >= 5.0 with the OneDB Linux 64-bit ODBC Driver. There is no issue when using pyodbc < 5.0.

python error:

Traceback (most recent call last):
File "/usr/local/airflow/informix_pyodbc.py", line 32, in
conn = pyodbc.connect(conn_str, ansi=True)
pyodbc.Error: ('H000', '[H000] [ (-11060) (SQLDriverConnect)')

FAILURE! A connection error occurred.
This is likely due to an incorrect ServerName, Database, UID, or PWD.

--- ERROR DETAILS ---
('H000', '[H000] [ (-11060) (SQLDriverConnect)')

ODBC trace log:

[ODBC][5421][1751284780.466418][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0x2b9e280
[ODBC][5421][1751284780.473963][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x2b9e280
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 4
[ODBC][5421][1751284780.483959][SQLSetEnvAttr.c][381]
Exit:[SQL_SUCCESS]
[ODBC][5421][1751284780.491650][SQLAllocHandle.c][395]
Entry:
Handle Type = 2
Input Handle = 0x2b9e280
UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][5421][1751284780.492689][SQLAllocHandle.c][531]
Exit:[SQL_SUCCESS]
Output Handle = 0x2ac36b0
[ODBC][5421][1751284780.494001][SQLDriverConnectW.c][298]
Entry:
Connection = 0x2ac36b0
Window Hdl = (nil)
Str In = [DRIVER=/opt/hcl/onedb_driver/onedb-odbc-driver/lib/cli/iclit09b.so;HostName=xxx.xxx.xxx.xxx;ServiceName=50001;ServerName=xxxxx...][length = 320 (SQL_NTS)]
Str Out = (nil)
Str Out Max = 0
Str Out Ptr = (nil)
Completion = 0
[ODBC][5421][1751284780.509374][SQLDriverConnectW.c][869]
Exit:[SQL_ERROR]
[ODBC][5421][1751284780.509822][SQLGetDiagRecW.c][535]
Entry:
Connection = 0x2ac36b0
Rec Number = 1
SQLState = 0x7fffe5b30ae4
Native = 0x7fffe5b30abc
Message Text = 0x2ad6190
Buffer Length = 1023
Text Len Ptr = 0x7fffe5b30aba
[ODBC][5421][1751284780.510154][SQLGetDiagRecW.c][596]
Exit:[SQL_SUCCESS]
SQLState = [H]
Native = 0x7fffe5b30abc -> -11060 (32 bits)
Message Text = [[]
[ODBC][5421][1751284780.510516][SQLFreeHandle.c][290]
Entry:
Handle Type = 2
Input Handle = 0x2ac36b0
[ODBC][5421][1751284780.510821][SQLFreeHandle.c][339]
Exit:[SQL_SUCCESS]

[liaosnet]

ODBCINI file and unixODBC version ?

[gbuzic]

unixodbc-common/stable,now 2.3.11-2+deb12u1 all [installed,automatic]
unixodbc-dev/stable,now 2.3.11-2+deb12u1 amd64 [installed]
unixodbc/stable,now 2.3.11-2+deb12u1 amd64 [installed]

I don't use odbc.ini but connection string.

LD_LIBRARY_PATH= /usr/lib/x86_64-linux-gnu/odbc:/usr/lib/x86_64-linux-gnu:/opt/hcl/onedb_driver/onedb-odbc-driver/lib:/opt/hcl/onedb_driver/onedb-odbc-driver/lib/cli:/opt/hcl/onedb_driver/onedb-odbc-driver/lib/esql:

python connection part:
driver_path = "/opt/hcl/onedb_driver/onedb-odbc-driver"
conn_str = (
f"DRIVER={driver_path}/lib/cli/iclit09b.so;"
"HostName=IP ADDRESS"
"ServiceName=PORT;"
"ServerName=SERVER NAME;"
"Database=DATABASE NAME;"
"Protocol=olsoctcp;"
"UID=USER NAME;"
"PWD=PASSWORD;"
"CLIENT_LOCALE=en_US.UTF8;"
"DB_LOCALE=en_US.UTF8;"
f"TRANSLATIONDLL={driver_path}/lib/esql/igo4a304.so;"
)

conn = pyodbc.connect(conn_str, ansi=True)

[liaosnet]

unixodbc-common/stable,now 2.3.11-2+deb12u1 all [installed,automatic] unixodbc-dev/stable,now 2.3.11-2+deb12u1 amd64 [installed] unixodbc/stable,now 2.3.11-2+deb12u1 amd64 [installed]

I don't use odbc.ini but connection string.

LD_LIBRARY_PATH= /usr/lib/x86_64-linux-gnu/odbc:/usr/lib/x86_64-linux-gnu:/opt/hcl/onedb_driver/onedb-odbc-driver/lib:/opt/hcl/onedb_driver/onedb-odbc-driver/lib/cli:/opt/hcl/onedb_driver/onedb-odbc-driver/lib/esql:

python connection part: driver_path = "/opt/hcl/onedb_driver/onedb-odbc-driver" conn_str = ( f"DRIVER={driver_path}/lib/cli/iclit09b.so;" "HostName=IP ADDRESS" "ServiceName=PORT;" "ServerName=SERVER NAME;" "Database=DATABASE NAME;" "Protocol=olsoctcp;" "UID=USER NAME;" "PWD=PASSWORD;" "CLIENT_LOCALE=en_US.UTF8;" "DB_LOCALE=en_US.UTF8;" f"TRANSLATIONDLL={driver_path}/lib/esql/igo4a304.so;" )

conn = pyodbc.connect(conn_str, ansi=True)

==== system ===============
Ubuntu 22.04.3 LTS
Python 3.10.12
pyodbc 5.2.0

unixodbc/jammy-updates,jammy-security,now 2.3.9-5ubuntu0.1
unixodbc-common/jammy-updates,jammy-security,now 2.3.9-5ubuntu0.1
unixodbc-dev/jammy-updates,jammy-security,now 2.3.9-5ubuntu0.1

Informix 12.10.FC4W1

==== environment ===========
export INFORMIXSERVER=ids01
export INFORMIXDIR=/opt/ifxcsdk
export ODBCINI=/opt/ifxcsdk/etc/odbc.ini
export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/cli:$INFORMIXDIR/lib/esql:$LD_LIBRARY_PATH

==== ifx_pyodbc.py ===========
import sys
import pyodbc

# DSN connect need keyword PWD !
# conn = pyodbc.connect("DSN=testdb;PWD=xxxxxxxx")
conn = pyodbc.connect("DRIVER=/opt/ifxcsdk/lib/cli/iclis09b.so;HOST=192.168.0.212;SERVER=ids01;SERVICE=20088;PROTOCOL=onsoctp;DATABASE=testdb;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;UID=informix;PWD=xxxxxxxx");
# set connection encoding
conn.setencoding(encoding='UTF-8')

mycursor = conn.cursor()
mycursor.execute("drop table if exists company")
print("drop table company succeed!")

mycursor.execute("create table company(coid serial,coname varchar(255),coaddr varchar(255), primary key(coid))")
print("create table company succeed!")

mycursor.execute("insert into company values(0,?,?)","Company_TJ","TJ")
conn.commit()
mycursor.execute("insert into company values(0,?,?)","Company_BJ","BJ")
conn.commit()
print("insert table company succeed!")

cursor1 = conn.cursor()
cursor1.execute("select * from company")
print(cursor1.fetchall())
print("select table company succeed!")

conn.close()
sys.exit(0)

==== test ===================

root@netsky:~/pyodbc# pip3 list | grep pyodbc
pyodbc 5.2.0
root@netsky:~/pyodbc# python3 ifx_pyodbc.py
drop table company succeed!
create table company succeed!
insert table company succeed!
[(1, 'Company_TJ', 'TJ'), (2, 'Company_BJ', 'BJ')]
select table company succeed!
`

[bkline]

Am I reading this ticket correctly? It looks as if the previous comment (from last fall) would move this into "can't reproduce" territory, no?

[bkline]

It looks as if the previous comment (from last fall) would move this into "can't reproduce" territory, no?

Apparently not. I can reproduce the failure with the same script.

$ python3 ifx_pyodbc.py
Traceback (most recent call last):
  File "/home/bkline/packages/onedb-odbc-driver/ifx_pyodbc.py", line 6, in <module>
    conn = pyodbc.connect("DRIVER=/opt/ifxcsdk/lib/cli/iclis09b.so;HOST=127.0.0.1;SERVER=informix;SERVICE=20088;PROTOCOL=onsoctp;DATABASE=odbc_demodb;DB_LOCALE=en_US.utf8;CLIENT_LOCALE=en_US.utf8;UID=informix;PWD=REDACTED");
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pyodbc.Error: ('H000', '[H000] [ (-11060) (SQLDriverConnect)')

I also tried with IBM's 15.0.1.0 driver. Same error. Tried both pyodbc 5.1.0 and 5.3.0. Ubuntu 22.04.3 LTS; Python 3.12.3; unixODBC 2.3.12-1ubuntu0.24.04.1.

Connecting to the same database with the same settings/credentials using unixODBC's isql succeeds (but fails with iusql, which gets the same error that pyodbc gets).

This ticket is basically a duplicate of #1392, filed back in 2024. As far as I can tell, the only way pyodbc 5.x will be able to connect with Informix would be:

• the path for using SQLDriverConnect() (removed in 5.x) is restored; or
• IBM and/or One DB releases a driver built with 2-byte SQLWCHARs; or
• the DM and pyodbc are both built with SQL_WCHART_CONVERT defined

I haven't tested that last option, nor is there much incentive to, as it would break the ability to connect with drivers which expect SQLWCHAR characters to be two bytes wide (at least as I understand how things work), which would in turn break the "use ODBC when your software needs to talk to different databases" model (which, as we can see from the these tickets, is somewhat broken already). Unless all the other drivers have source code available for building with SQLWCHAR defined the same way, and you abandon the binaries provided by your Unix/Linux package distribution system (as if this stuff weren't complicated enough already).

I can imagine that when the wide-character functions were added to the ODBC spec, it was decided that the document would leave the definition of SQLWCHAR deliberately vague, so that drivers could use whatever character encoding was most suitable for their individual back ends, and the driver manager layer would somehow discover for each driver what that encoding was and take care of the translation between those two layers. If that's what they had in mind, it would have been nice if they had said so, and perhaps even thrown out some clues about how that discovery would happen.

Unfortunately, I no longer have a copy of the spec. I gave it to our local library (which probably pulped it, as its not in their catalog) some time back on the theory that it would always be online (silly me).

If I do any work on coming up with a PR to restore the path of connecting using the "ANSI" functions, I'll probably tie in that work with the earlier ticket, rather than with this duplicate. Before I do that, though, I'll need to dig in and learn what I can about the reasons that was dropped in the first place. Who knows, I may discover that there's some showstopper which means we'll never be able to use the current version of pyodbc to connect with Informix without a driver which supports 16-bit characters.

[bkline]

Adding UNICODE=UTC-2 to the [ODBC] section of $HOME/.odbc.ini fixes the problem.

$ python3 ifx_pyodbc.py
drop table company succeed!
create table company succeed!
insert table company succeed!
[(1, 'Company_TJ', 'TJ'), (2, 'Company_BJ', 'BJ')]
select table company succeed!

Tested with OneDB's driver as well as IBM's. See also #1392.

@gbuzic Can you confirm that this solution works on your system (and if so close this ticket)?