CODEWITHSUNDEEP
pythonwindowsodbcinformixpyodbc
wraeth
wraeth

Reputation: 143

Connecting to Informix with pyodbc on Windows

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:

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.

Upvotes: 3

Views: 5536

Answers (1)

Michał Niklas
Michał Niklas

Reputation: 54322

It should be comment, but is too long.

While I cannot reproduce your error I have some ideas.

  1. 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

  2. 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')

  3. 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.

Upvotes: 4

Related Questions