I have been at this for a while, trying all kinds of different packages from openSource, IBM, and many others. I have not yet found one that works without some sort of confusing install method that I can not get to work, or some sort of integration with other third-party pieces that I can not seem to get working.
I am simply trying to perform SQL statements on a Informix Server using Python. No different than mySQL and other tools. Using cursors or full result dumps, really do not care. I want to be able to formalize a query string statically or dynamically and then tell whatever tools/module to execute said query and return results (if any).
I have tried:
What I have managed:
I am just stumped. Looking for assistance on what to download (URLs), how to go about installing it (tips and tricks, environment variables, where to install it, etc..) I want to have something that does not depend on Java or writing Java, etc. I am looking for a solution that may will give me the ability to write Python to query, insert, update, and delete from an Informix database and tables. I want to combine my previously written Java and Bash script into a Python script.
Frustrated and looking for any assistance.
Thank you for listening and please ask questions if you do not understand my plea.
Informix on Linux is a bag of pain. My personal setup to get Informix-connect to work with CPython3 is stacking the Informix Client SDK with unixODBC and pyodbc. There are some hoops to jump through, none of which are documented. Almost all the setup is completely useless yet required to prevent some parts of the Informix-driver to bail out. Note that some options are case- and space-sensitive (Description=Informix != description = Informix).
/opt/IBM/informix/opt/IBM/informix/lib/cli and /opt/IBM/informix/lib/esql to your dynamic linker lookup paths. On Fedora you can do this by putting them in a new file /etc/ld.so.conf.d/informix.confCreate a new /etc/odbc.ini and add the following:
[ODBC Data Sources]
Infdrv1=IBM INFORMIX ODBC DRIVER
[Infdrv1]
Driver=/opt/IBM/informix/lib/cli/iclit09b.so
Description=Informix
Database=WHATEVER_YOUR_DB_NAME_IS
Servername=WHATEVER_YOUR_SERVER_NAME_IS
CLIENT_LOCALE=en_us.8859-1 # MAY BE DIFFERENT
DB_LOCALE=en_us.819 # MAY BE DIFFERENT
[ODBC]
UNICODE=UCS-2
Create a new /etc/odbcinst.ini and add the following
[IBM INFORMIX ODBC DRIVER]
Description=Informix Driver
Driver=libifcli.so
You need to set the environment variables INFORMIXDIR and ODBCINI. On Fedora you may add a new file /etc/profile.d/informix.sh and add
export INFORMIXDIR=/opt/IBM/informix
export ODBCINI=/etc/odbc.ini
Edit /opt/IBM/informix/etc/sqlhosts and put your basic connection information there. In the most simple case it has only one line that reads
YOUR_SERVER_NAME\tonsoctcp\tYOUR_DB_NAME\tpdap-np
Note that pdap-np is actually port 1526 which is also the Informix "Turbo"-Driver tcp port. See your /etc/services
Create an empty .odbc.ini in your $HOME e.g. by touch $HOME/.odbc.ini. It needs to be there. It needs to be 0 bytes. I love this part.
Install unixODBC and pyodbc from your favorite repository.
Remember to get your env-changes going, e.g. via reboot. You can now connect like this:
import pyodbc
DRIVER = 'IBM INFORMIX ODBC DRIVER'
SERVER = 'YOUR_SERVER_NAME'
DATABASE = 'YOUR_DB_NAME'
constr = 'DRIVER={%s};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' % (DRIVER, SERVER, DATABASE, USER, PASS)
con = pyodbc.connect(constr, autocommit=False)
From there on you can get your cursor, execute queries, fetch results and such. Note that there are numerous bugs in quirks in IBM's ODBC-driver, out of my head:
NULLs may cause a segfault as the IBM driver puts a 32bit int where a 64bit int is expected to signal the value being null. In case you are affected by this, you need to patch unixODBC for all possible column types to deal with this.SELECT COUNT(*) FROM foobar needs to be SELECT COUNT(*) AS c FROM foobar).