title: Connecting to Informix with pyodbc on Windows
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>I'm trying (again) to get this working with pyodbc but can't establish a connection to the database from Windows:</p>
  
  <pre><code>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.
  &gt;&gt;&gt;
  &gt;&gt;&gt; import pyodbc
  &gt;&gt;&gt;
  &gt;&gt;&gt; cnxn = pyodbc.connect(dsn='devdb')
  Traceback (most recent call last):
    File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  pyodbc.Error: ('HY000', '[HY000] [Informix][Informix ODBC Driver][Informix]Unspecified System Error = -23101. (-23101) (SQLDriverConnect)')
  </code></pre>
  
  <p>From what I've found, <strong>Error -23101</strong> 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.</p>
  
  <p>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!</p>
  
  <p><strong>Note</strong>: 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).</p>
  
  <p>Thanks in advance for any help.</p>
  
  <p><strong>Edit</strong>: Heh, I'm not reputable enough to attach an image yet, but I've uploaded it at <a href="http://wraeth.id.au/wp-content/uploads/2014/10/odbcad32.png" rel="nofollow">http://wraeth.id.au/wp-content/uploads/2014/10/odbcad32.png</a> if you want to have a look.</p>
  
  <p><strong>Edit 2</strong>:</p>
  
  <ul>
  <li>Updated to ActivePython-3.3.4 with no change</li>
  <li>Created symbolic link to IFX ClientSDK dir to remove spaces in path resulting in "The driver did not supply an error"</li>
  <li>Confirmed <code>DB_LOCALE</code> is <code>en_US.819</code> by checking the <code>sysdbslocale</code> table in the database.</li>
  </ul>
  
  <p>Also, confirmed that <code>%INFORMIXDIR%</code> is set to a valid CSDK installation:</p>
  
  <pre><code>&gt; mklink /D informix "C:\Program Files\IBM Informix Client SDK"
  &gt; set INFORMIXDIR=C:\informix
  &gt; 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    &lt;DIR&gt;          .
  19/09/2013  04:50 PM    &lt;DIR&gt;          ..
  19/09/2013  04:50 PM    &lt;DIR&gt;          cm3
  19/09/2013  04:50 PM    &lt;DIR&gt;          cv9
  19/09/2013  04:50 PM    &lt;DIR&gt;          dll
  19/09/2013  04:50 PM    &lt;DIR&gt;          etc
  19/09/2013  04:50 PM    &lt;DIR&gt;          lc11
  </code></pre>
  
  <p>Using the symbolic link as <code>INFORMIXDIR</code> still doesn't allow it to connect:</p>
  
  <pre><code>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.
  &gt;&gt;&gt; import os, pyodbc
  &gt;&gt;&gt; os.path.exists(
  ...   os.path.join(
  ...     os.environ.get('INFORMIXDIR'),
  ...     'gls'
  ...   )
  ... )
  True
  &gt;&gt;&gt; cnxn = pyodbc.connect(dsn='devdb', uid='user', pwd='password')
  Traceback (most recent call last):
    File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  pyodbc.Error: ('HY000', 'The driver did not supply an error!')
  </code></pre>
  
  <p><br /></p>
  
  <h2>Solution</h2>
  
  <p>Uninstalling and reinstalling the Client SDK to a path that didn't have spaces (<code>C:\informix</code>) seems to have resolved the issue.</p>