Connect to Informix database via Python on OS X Mountain Lion

Here is my successful attempt to connect to Informix database v9.0 via Python on OS X Mountain Lion.

Install Informix Client SDK

Download Informix Client SDK Developer Edition for Mac OS X x64, 64-bit, 3.70.FC4DE from here:

• https://www.ibm.com/marketing/iwm/tnd/search.jsp?rs=ifxdl

Choose Mac OS X operating system from the dropdown list.

Then extract the downloaded .tar file to a folder, and run the following Terminal command inside the folder:

sudo ./installclientsdk

Do whatever it says to finish the installation.

Edit sqlhosts file

Copy a sample sqlhosts file to /usr/local/etc:

cp /Applications/IBM/informix/etc/sqlhosts /usr/local/etc/sqlhosts

Edit /usr/local/etc/sqlhosts as needed. Fill out server name, protocol, ip address and service port, separated by tabs.

Configure environment variables

Open ~/.bash_profile in a text editor, append the following lines:

export INFORMIXDIR=/Applications/IBM/informix
export INFORMIXSQLHOSTS=/usr/local/etc/sqlhosts

Remember to restart Terminal to take effects.

Soft linking dynamic library files

To avoid the annoyance caused by DYLD_LIBRARY_PATH, soft link some dynamic library files to /usr/local/lib instead：

ln -s /Applications/IBM/informix/lib/cli/iclit09b.dylib /usr/local/lib/iclit09b.dylib
ln -s /Applications/IBM/informix/lib/esql/igl4a304.dylib /usr/local/lib/igl4a304.dylib
ln -s /Applications/IBM/informix/lib/esql/iglxa304.dylib /usr/local/lib/iglxa304.dylib

Install pypyodbc-informixcsdk

Run the following command to install a modified version of pypyodbc:

pip install git+https://github.com/rockallite/pypyodbc-informixcsdk.git

Try it

Open a Python interactive interpreter, type the following commands:

import pypyodbc
# Replace database-related parameters with your own values!
conn = pypyodbc.connect(driver='iclit09b.dylib', server='your_server', db='your_db', uid='username', pwd='password')
cursor = conn.cursor()
cursor.execute('CREATE TEMP TABLE ttbl (num DECIMAL(14, 2))')
cursor.execute('INSERT INTO ttbl VALUES (NULL)')
cursor.execute('SELECT num FROM ttbl')
cursor.fetchone()

If the last line prints (None,), you're done. Otherwise, check again from the beginning.

FAQ

• Why not DSN?

  Answer: While it passes test in iodbctest, connecting with DSN in pyodbc or pypyodbc crashes the Python interpreter.

• Why don't you use pyodbc which is likely more efficent?

  Answer: Due to a bug in Informix Client SDK (probably), a NULL value in an integer / float column of a row returned by pyodbc will crash the Python interpreter. I've no idea about C, so I'm not touching the source code of it.

• How about informixdb or ibm-db modules?

  Answer: informixdb isn't maintained for years. It's hard to compile. Even if you manage to compile it, you'll find it throwing out exceptions on Date / DataTime data types. ibm-db is for Informix database of version 11 and above, and it's using a different underlaying architecture.