How to connect select data from Informix database in python3 using pyodbc / unixODBC

3
votes

I'm having a problem selecting data from Informix database tables using python3.5 and pyodbc/unixODBC.

I have a little py file (ifx.py) the contents of which are:

import pyodbc
db=pyodbc.connect('DSN=Test1')
db.execute("SELECT * FROM customer ")

and I get an error :

pyodbc.Error: ('HY000', '[HY000] [Informix][Informix ODBC Driver]Invalid byte in codeset conversion input. (21000) (SQLExecDirectW)')

All the searches I've done imply something to do with LOCALE settings, but I check and they're all set the same:

CLIENT_LOCALE=en_US.819
DB_LOCALE=en_US.819
dbs_collate=en_US.819

If I use the unixODBC 'isql' utility I can connect to and query the data happily.

Version Information.

unixODBC.x86_64 0:2.2.14-14.el6
pyodbc.version => '4.0.6'
Linux => Red Hat Enterprise Linux Server release 6.8 
python3.5 => Python 3.5.3 
Database => IBM Informix Dynamic Server Version 12.10.FC6X5 
ClientSDK => IBM Informix CSDK Version 4.10, IBM Informix-ESQL Version 4.10.FC6

Environment Variable ODBCINI points to /etc/odbc.ini. Contents are:

[ODBC Data Sources]
Test1=IBM INFORMIX ODBC DRIVER
;
; Define ODBC Database Driver's Below - Driver Configuration Section
;
[Test1]
Driver=/opt/informix/lib/cli/iclis09b.so
Description=IBM INFORMIX ODBC DRIVER
Database=eunice
LogonID=
pwd=
Servername=indika_test
Port=9916
CursorBehavior=0
CLIENT_LOCALE=en_US.819
DB_LOCALE=en_US.819
TRANSLATIONDLL=/opt/informix/lib/esql/igo4a304.so

/etc/odbcinst.ini contents:

[ODBC Drivers]
IBM INFORMIX ODBC DRIVER=Installed
[IBM INFORMIX ODBC DRIVER]
Driver=/opt/informix/lib/cli/iclit09b.so
Setup=/opt/informix/lib/cli/iclit09b.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.51
FileUsage=0
SQLLevel=1
smProcessPerConnect=Y

and odbcinst -j output is 

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Thanks in advance for any help or suggestions.

3
linuxpython-3.xinformixpyodbcunixodbc

3 Answers

1
votes

This is probably due to an multibyte character in the customer table, which can not be converted to Latin 1, ISO 8859-1 from UTF8. Even with same codes set on both CLIENT_LOCALE and DB_LOCALE, an ODBC client makes an internal conversion from UTF-8 to 8859. If there are any 2-byte chars (chars beyond extended-ascii 255) they will not convert to a Latin-1 client. I would look at the data in customer table and identify the problematic data.

1
votes

It is possible (or likely) that there is an Unicode encoding mismatch. On my Debian system, the python builds (both 2.7 and 3.4) use UCS-4 encoding. You can verify with this command:

python3 -c "import sys;print(sys.maxunicode<66000 and'UCS2'or'UCS4')"

Once you find the encoding, you need to make sure the Informix odbc.ini file has the same Unicode encoding. In my odbc.ini file,

[ODBC]

;uncomment the below line for UNICODE connection

UNICODE=UCS-4

I hope this resolves your issue.

1
votes

Thanks for the responses. It was unicode mismatch.

db=pyodbc.connect('DSN=Test1')
db.setdecoding(pyodbc.SQL_WCHAR, encoding='UTF-8')
db.setdecoding(pyodbc.SQL_CHAR, encoding='UTF-8')
db.setencoding(encoding='UTF-8')

By adding setencoding and setdecoding I was able to successfully select data form the Informix DB.