mkleehammer
Unable to connect to Informix with pyodbc 5.x.x
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
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]
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)
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!
`