Skip to content

Connect to Informix database via Python on OS X Mountain Lion

Jump to bottom
Rockallite Wulf edited this page Jan 28, 2019 · 16 revisions

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, Version 3.70.FC4DE from here (162 MB):

You may need to register on IBM website in order to download it.

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
export ODBCINI=/Library/ODBC/odbc.ini

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 pypyodbc-informixcsdk, 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

# Fill these with proper values.
# And don't use DSN! See FAQ for why.
conn = pypyodbc.connect(
    driver='iclit09b.dylib',
    server='your_server_name',
    db='your_database',
    uid='your_username',
    pwd='your_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 won't touch 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.

  • I just want a graphical Informix query tool for OS X. Any recommendations?

    Answer: RazorSQL (Commercial, $99.95 for a single user license) or SQuirreL SQL Client (LGPL). Both are Java-based, which means you may need to install Java runtime and JDBC driver for Informix (RazorSQL has one built-in).

Clone this wiki locally