Connecting to Informix with pyodbc on Windows

Question

I am attempting to write a process in python that needs to be portable between different database environments. One of the environments that it must be able to connect to is Informix.

I've been searching for how to connect to Informix in Python and have come across both InformixDB and ibm_db{,_sa}, both of which seem overly difficult to use (and I've tried and tried and just can't get them working).

I'm trying (again) to get this working with pyodbc but can't establish a connection to the database from Windows:

set INFORMIXDIR="C:\Program Files\IBM Informix Client SDK"
set CLIENT_LOCALE=en_US.CP1252
set DB_LOCALE=en_US.819

python
Python 3.3.3 (v3.3.3:c3896275c0f6, Nov 18 2013, 21:18:40) [MSC v.1600 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import pyodbc
>>>
>>> cnxn = pyodbc.connect(dsn='devdb')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HY000', '[HY000] [Informix][Informix ODBC Driver][Informix]Unspecified System Error = -23101. (-23101) (SQLDriverConnect)')

From what I've found, Error -23101 is caused by the Locale's not matching correctly, however these are the same values used in the ODBC configuration as well as any other Informix-related utility that I have at my disposal.

I am at a loss to figure out how to connect to Informix and can't think of any more search terms to use to try and figure out this issue. How can it be so difficult to use ODBC - almost every other language I know doesn't have a problem with it!

Note: just to be clear, the ODBC connection is configured correctly and works with other ODBC-based applications (I can connect using QTODBC or Perl DBI).

Thanks in advance for any help.

Edit: Heh, I'm not reputable enough to attach an image yet, but I've uploaded it at http://wraeth.id.au/wp-content/uploads/2014/10/odbcad32.png if you want to have a look.

Edit 2:

Updated to ActivePython-3.3.4 with no change
Created symbolic link to IFX ClientSDK dir to remove spaces in path resulting in "The driver did not supply an error"
Confirmed DB_LOCALE is en_US.819 by checking the sysdbslocale table in the database.

Also, confirmed that %INFORMIXDIR% is set to a valid CSDK installation:

> mklink /D informix "C:\Program Files\IBM Informix Client SDK"
> set INFORMIXDIR=C:\informix
> dir %INFORMIXDIR%\gls
 Volume in drive C has no label
 Volume Serial Number is 808D-98FF

 Directory of C:\informix\gls

19/09/2013  04:50 PM    <DIR>          .
19/09/2013  04:50 PM    <DIR>          ..
19/09/2013  04:50 PM    <DIR>          cm3
19/09/2013  04:50 PM    <DIR>          cv9
19/09/2013  04:50 PM    <DIR>          dll
19/09/2013  04:50 PM    <DIR>          etc
19/09/2013  04:50 PM    <DIR>          lc11

Using the symbolic link as INFORMIXDIR still doesn't allow it to connect:

ActivePython 3.3.4.1 (ActiveState Software Inc.) based on
Python 3.3.4 (default, Feb 25 2014, 15:11:05) [MSC v.1600 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import os, pyodbc
>>> os.path.exists(
...   os.path.join(
...     os.environ.get('INFORMIXDIR'),
...     'gls'
...   )
... )
True
>>> cnxn = pyodbc.connect(dsn='devdb', uid='user', pwd='password')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Solution

Uninstalling and reinstalling the Client SDK to a path that didn't have spaces (C:\informix) seems to have resolved the issue.

I work with ActivePython 3.3.4 with pyodbc module and Informix Client 4.10.TC4DE. It simply works. Is your CLIENT_LOCALE and DB_LOCALE the same as on Environment tab of ODBC Driver Setup dialog? Can you show us screenshot of this dialog? — Michał Niklas
Also try to run you program without setting any Informix *_LOCALE environment variables. When I set your values (instead of Polish pl_pl.CP1250 I normally use) I got the same error as you. Then I cleared those environment variables and I was able to connect with server. — Michał Niklas
@MichałNiklas I'm using vanilla Python-3.3.3 and Client 4.10TC1. I'm not at the machine at the moment (getting information remotely) and will try installing/using ActivePython instead tomorrow. I'll attach a screenshot shortly, but the CLIENT_LOCALE and DB_LOCALE are set the same as in the ODBC config dialog; and the same result occurs when I unset the variables (or start from a clean command prompt). — wraeth
You can also connect to sysmaster database and check locale of your database with select * from sysdbslocale; — Michał Niklas
It also may be problem with INFORMIXDIR. Is it correct? Does exist %INFORMIXDIR%\gls? — Michał Niklas

Michał Niklas Michał Niklas · Accepted Answer · 2014-10-08T08:38:54

It should be comment, but is too long.

While I cannot reproduce your error I have some ideas.

Reinstall ClientSDK and install it in c:\informix directory setting it as %INFORMIXDIR%. This should change ODBC registry entries about driver:
```
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM INFORMIX ODBC DRIVER]
"Driver"="C:\\informix\\bin\\iclit09b.dll"
"Setup"="C:\\informix\\bin\\iclit09b.dll"
```
and about database (devdb is my DSN)
```
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\devdb]
"Driver"="C:\\informix\\bin\\iclit09b.dll"
```
(those entries are from 32 bit Windows)

I advice it after reading answers and comments to: Informix connection works through Windows, but not through Cygwin
If you have ActiveState Python then you can use odbc module instead of pyodbc. Is is part of win32 package and works only on Windows but maybe it can connect with your database. You can open database with:
```
import odbc
cnxn = odbc.odbc('devdb/user/password')
```
If some ODBC software works then you can enable tracing ODBC and compare traces. It would be useful especially if you can connect from odbc module but not from pyodbc.

Connecting to Informix with pyodbc on Windows

Solution

1 Answers