Connecting to a legacy informix database with django

Question

I'm trying to connect to an existing Informix database with Django, I'm using django-pyodbc-azure to handle the connection via odbc, the connection work fine when trying in the python interpreter:

>>>import pyodbc 
>>>conn = pyodbc.connect('DSN=test_ifx;UID=test;PWD=test')
>>>curs = conn.cursor()
>>>curs.execute("select * from someTable")
<pyodbc.Cursor object at 0x0052E520>

So this work just fine but in django I create the database connection parameters like that :

DATABASES = {
    'default' : {
        'ENGINE' : 'sql_server.pyodbc',
        'NAME' : 'test',
        'USERNAME' : 'test',
        'PASSWORD' : 'test',
        'PORT' : '1260',
        'OPTIONS':{
            'dsn' : 'test_ifx'
        }
    }
}

So when I launch the server I get the following error:

Unhandled exception in thread started by <function check_errors.<locals>.wrapper at 0x0353DED0>
Traceback (most recent call last):
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 216, in ensure_connection
    self.connect()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 194, in connect
    self.connection = self.get_new_connection(conn_params)
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sql_server\pyodbc\base.py", line 321, in get_new_connection
    conn.timeout = query_timeout
pyodbc.Error: ('HYC00', '[HYC00] [Informix][Informix ODBC Driver]Driver not capable. (-11092) (SQLSetConnectAttr)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\utils\autoreload.py", line 225, in wrapper
    fn(*args, **kwargs)
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\core\management\commands\runserver.py", line 123, in inner_run
    self.check_migrations()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\core\management\base.py", line 427, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\migrations\executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\migrations\loader.py", line 49, in __init__
    self.build_graph()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\migrations\loader.py", line 207, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\migrations\recorder.py", line 61, in applied_migrations
    if self.has_table():
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\migrations\recorder.py", line 44, in has_table
    return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 255, in cursor
    return self._cursor()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 232, in _cursor
    self.ensure_connection()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 216, in ensure_connection
    self.connect()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 216, in ensure_connection
    self.connect()
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\django\db\backends\base\base.py", line 194, in connect
    self.connection = self.get_new_connection(conn_params)
  File "C:\Users\PatrickStewball\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sql_server\pyodbc\base.py", line 321, in get_new_connection
    conn.timeout = query_timeout
django.db.utils.Error: ('HYC00', '[HYC00] [Informix][Informix ODBC Driver]Driver not capable. (-11092) (SQLSetConnectAttr)')

My computer run on windows 7 64bits, the server with the informix database is on my localnetwork. Any help would be great.

EDIT: ODBC trace

So I do have DIAG [01S00] [Informix][Informix ODBC Driver]Invalid connection string attribute. (-11005) error in the odbc trace, the problem is now how to go from pyodbc.connect('DSN=test_ifx;UID=test;PWD=test') which is working to the Django DATABASES parameters

python.exe mana 520-1df4    ENTER SQLSetEnvAttr 
        SQLHENV             0x00000000
        SQLINTEGER                 201 <SQL_ATTR_CONNECTION_POOLING>
        SQLPOINTER                 2 <SQL_CP_ONE_PER_HENV>
        SQLINTEGER                   4 

python.exe mana 520-1df4    EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
        SQLHENV             0x00000000
        SQLINTEGER                 201 <SQL_ATTR_CONNECTION_POOLING>
        SQLPOINTER                 2 <SQL_CP_ONE_PER_HENV>
        SQLINTEGER                   4 

python.exe mana 520-1df4    ENTER SQLAllocHandle 
        SQLSMALLINT                  1 <SQL_HANDLE_ENV>
        SQLHANDLE           0x00000000
        SQLHANDLE *         0x7278D8A4

python.exe mana 520-1df4    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  1 <SQL_HANDLE_ENV>
        SQLHANDLE           0x00000000
        SQLHANDLE *         0x7278D8A4 ( 0x00124248)

python.exe mana 520-1df4    ENTER SQLSetEnvAttr 
        SQLHENV             0x00124248
        SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
        SQLPOINTER                 3 <SQL_OV_ODBC3>
        SQLINTEGER                   4 

python.exe mana 520-1df4    EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
        SQLHENV             0x00124248
        SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
        SQLPOINTER                 3 <SQL_OV_ODBC3>
        SQLINTEGER                   4 

python.exe mana 520-1df4    ENTER SQLAllocHandle 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x00124248
        SQLHANDLE *         0x03C1E4B4

python.exe mana 520-1df4    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x00124248
        SQLHANDLE *         0x03C1E4B4 ( 0x001242C0)

python.exe mana 520-1df4    ENTER SQLDriverConnectW 
        HDBC                0x001242C0
        HWND                0x00000000
        WCHAR *             0x70048B34 [      -3] "******\ 0"
        SWORD                       -3 
        WCHAR *             0x70048B34 
        SWORD                       -3 
        SWORD *             0x00000000
        UWORD                        0 <SQL_DRIVER_NOPROMPT>

python.exe mana 520-1df4    EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
        HDBC                0x001242C0
        HWND                0x00000000
        WCHAR *             0x70048B34 [      -3] "******\ 0"
        SWORD                       -3 
        WCHAR *             0x70048B34 <Invalid buffer length!> [-3]
        SWORD                       -3 
        SWORD *             0x00000000
        UWORD                        0 <SQL_DRIVER_NOPROMPT>

        DIAG [01S00] [Informix][Informix ODBC Driver]Invalid connection string attribute. (-11005) 

        DIAG [01S00] [Informix][Informix ODBC Driver]Invalid connection string attribute. (-11005) 

        DIAG [01S00] [Informix][Informix ODBC Driver]Invalid connection string attribute. (-11005) 

python.exe mana 520-1df4    ENTER SQLSetConnectAttr 
        SQLHDBC             0x001242C0
        SQLINTEGER                 102 <SQL_ATTR_AUTOCOMMIT>
        SQLPOINTER                 0 <SQL_AUTOCOMMIT_OFF>
        SQLINTEGER                  -5 

python.exe mana 520-1df4    EXIT  SQLSetConnectAttr  with return code 1 (SQL_SUCCESS_WITH_INFO)
        SQLHDBC             0x001242C0
        SQLINTEGER                 102 <SQL_ATTR_AUTOCOMMIT>
        SQLPOINTER                 0 <SQL_AUTOCOMMIT_OFF>
        SQLINTEGER                  -5 

        DIAG [01S02] [Informix][Informix ODBC Driver]Option value changed. (-11007) 

python.exe mana 520-1df4    ENTER SQLGetInfoW 
        HDBC                0x001242C0
        UWORD                       77 <SQL_DRIVER_ODBC_VER>
        PTR                 0x0400F9C8 
        SWORD                       40 
        SWORD *             0x03C1E434

python.exe mana 520-1df4    EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
        HDBC                0x001242C0
        UWORD                       77 <SQL_DRIVER_ODBC_VER>
        PTR                 0x0400F9C8 [      10] "03.00"
        SWORD                       40 
        SWORD *             0x03C1E434 (10)

python.exe mana 520-1df4    ENTER SQLGetInfoW 
        HDBC                0x001242C0
        UWORD                    10002 <SQL_DESCRIBE_PARAMETER>
        PTR                 0x04000818 
        SWORD                        4 
        SWORD *             0x03C1E434

python.exe mana 520-1df4    EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
        HDBC                0x001242C0
        UWORD                    10002 <SQL_DESCRIBE_PARAMETER>
        PTR                 0x04000818 [       2] "N"
        SWORD                        4 
        SWORD *             0x03C1E434 (2)

python.exe mana 520-1df4    ENTER SQLGetInfoW 
        HDBC                0x001242C0
        UWORD                      111 <SQL_NEED_LONG_DATA_LEN>
        PTR                 0x04000818 
        SWORD                        4 
        SWORD *             0x03C1E434

python.exe mana 520-1df4    EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
        HDBC                0x001242C0
        UWORD                      111 <SQL_NEED_LONG_DATA_LEN>
        PTR                 0x04000818 [       2] "Y"
        SWORD                        4 
        SWORD *             0x03C1E434 (2)

python.exe mana 520-1df4    ENTER SQLAllocHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E40C

python.exe mana 520-1df4    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E40C ( 0x0400CEB0)

python.exe mana 520-1df4    ENTER SQLGetTypeInfo 
        HSTMT               0x0400CEB0
        SWORD                       12 <SQL_VARCHAR>

python.exe mana 520-1df4    EXIT  SQLGetTypeInfo  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        SWORD                       12 <SQL_VARCHAR>

python.exe mana 520-1df4    ENTER SQLFetch 
        HSTMT               0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
        HSTMT               0x0400CEB0

python.exe mana 520-1df4    ENTER SQLFreeStmt 
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    ENTER SQLFreeHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    ENTER SQLAllocHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E408

python.exe mana 520-1df4    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E408 ( 0x0400CEB0)

python.exe mana 520-1df4    ENTER SQLGetTypeInfo 
        HSTMT               0x0400CEB0
        SWORD                       -9 <SQL_WVARCHAR>

python.exe mana 520-1df4    EXIT  SQLGetTypeInfo  with return code -1 (SQL_ERROR)
        HSTMT               0x0400CEB0
        SWORD                       -9 <SQL_WVARCHAR>

        DIAG [HY004] [Informix][Informix ODBC Driver]SQL data type out of range. (-11064) 

python.exe mana 520-1df4    ENTER SQLFreeStmt 
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    ENTER SQLFreeHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    ENTER SQLAllocHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E404

python.exe mana 520-1df4    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E404 ( 0x0400CEB0)

python.exe mana 520-1df4    ENTER SQLGetTypeInfo 
        HSTMT               0x0400CEB0
        SWORD                       -3 <SQL_VARBINARY>

python.exe mana 520-1df4    EXIT  SQLGetTypeInfo  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        SWORD                       -3 <SQL_VARBINARY>

python.exe mana 520-1df4    ENTER SQLFetch 
        HSTMT               0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
        HSTMT               0x0400CEB0

python.exe mana 520-1df4    ENTER SQLFreeStmt 
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    ENTER SQLFreeHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    ENTER SQLAllocHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E400

python.exe mana 520-1df4    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x001242C0
        SQLHANDLE *         0x03C1E400 ( 0x0400CEB0)

python.exe mana 520-1df4    ENTER SQLGetTypeInfo 
        HSTMT               0x0400CEB0
        SWORD                       93 <SQL_TYPE_TIMESTAMP>

python.exe mana 520-1df4    EXIT  SQLGetTypeInfo  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        SWORD                       93 <SQL_TYPE_TIMESTAMP>

python.exe mana 520-1df4    ENTER SQLFetch 
        HSTMT               0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0

python.exe mana 520-1df4    ENTER SQLGetData 
        HSTMT               0x0400CEB0
        UWORD                        3 
        SWORD                        4 <SQL_C_LONG>
        PTR                 <unknown type>
        SQLLEN                     4
        SQLLEN *            0x00000000

python.exe mana 520-1df4    EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        UWORD                        3 
        SWORD                        4 <SQL_C_LONG>
        PTR                 <unknown type>
        SQLLEN                     4
        SQLLEN *            0x00000000

python.exe mana 520-1df4    ENTER SQLFreeStmt 
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0400CEB0
        UWORD                        0 <SQL_CLOSE>

python.exe mana 520-1df4    ENTER SQLFreeHandle 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x0400CEB0

python.exe mana 520-1df4    ENTER SQLSetConnectAttr 
        SQLHDBC             0x001242C0
        SQLINTEGER                 113 <SQL_ATTR_CONNECTION_TIMEOUT>
        SQLPOINTER                 0
        SQLINTEGER                  -5 

python.exe mana 520-1df4    EXIT  SQLSetConnectAttr  with return code -1 (SQL_ERROR)
        SQLHDBC             0x001242C0
        SQLINTEGER                 113 <SQL_ATTR_CONNECTION_TIMEOUT>
        SQLPOINTER                 0
        SQLINTEGER                  -5 

        DIAG [HYC00] [Informix][Informix ODBC Driver]Driver not capable. (-11092) 

python.exe mana 520-1df4    ENTER SQLGetDiagRecW 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0
        SQLSMALLINT                  1 
        SQLWCHAR *          0x03C1E4FC
        SQLINTEGER *        0x03C1DCD8
        SQLWCHAR *          0x03C1DCFC 
        SQLSMALLINT               1023 
        SQLSMALLINT *       0x03C1DCDC

python.exe mana 520-1df4    EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0
        SQLSMALLINT                  1 
        SQLWCHAR *          0x03C1E4FC [       5] "HYC00"
        SQLINTEGER *        0x03C1DCD8 (-11092)
        SQLWCHAR *          0x03C1DCFC [      51] "[Informix][Informix ODBC Driver]Driver not capable."
        SQLSMALLINT               1023 
        SQLSMALLINT *       0x03C1DCDC (51)

python.exe mana 520-1df4    ENTER SQLGetDiagRecW 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0
        SQLSMALLINT                  2 
        SQLWCHAR *          0x03C1E4FC
        SQLINTEGER *        0x03C1DCD8
        SQLWCHAR *          0x03C1DCFC 
        SQLSMALLINT               1023 
        SQLSMALLINT *       0x03C1DCDC

python.exe mana 520-1df4    EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0
        SQLSMALLINT                  2 
        SQLWCHAR *          0x03C1E4FC
        SQLINTEGER *        0x03C1DCD8
        SQLWCHAR *          0x03C1DCFC 
        SQLSMALLINT               1023 
        SQLSMALLINT *       0x03C1DCDC

python.exe mana 520-294c    ENTER SQLEndTran 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0
        SQLSMALLINT                  1 

python.exe mana 520-294c    EXIT  SQLEndTran  with return code -1 (SQL_ERROR)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0
        SQLSMALLINT                  1 

        DIAG [HYC00] [Informix][Informix ODBC Driver]Driver not capable. (-11092) 

python.exe mana 520-294c    ENTER SQLDisconnect 
        HDBC                0x001242C0

python.exe mana 520-294c    EXIT  SQLDisconnect  with return code -1 (SQL_ERROR)
        HDBC                0x001242C0

        DIAG [25000] [Microsoft][Gestionnaire de pilotes ODBC] État de la transaction non valide (0) 

python.exe mana 520-294c    ENTER SQLFreeHandle 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0

python.exe mana 520-294c    EXIT  SQLFreeHandle  with return code -1 (SQL_ERROR)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x001242C0

        DIAG [HY010] [Microsoft][Gestionnaire de pilotes ODBC] Erreur de séquence de la fonction (0)

"Driver not capable. (-11092) (SQLSetConnectAttr)" ..... Why not getting an ODBC trace to see which parameter Django is trying to set (and not accepted by the Informix ODBC driver). That may throw some more lights ;) — jsagrera, Aug 01 '18 at 13:35
thanks for the help, I've added the odbc trace. I guess I don't have the appropriate parameters for the database in django. — PatrickStewball, Aug 01 '18 at 13:55
Informix does not support SQL_ATTR_CONNECTION_TIMEOUT. Looking at the code of sql_server\pyodbc\base.py and pyodbc-master\src\connect.cpp the call to SQLSetConnectAttr(SQL_ATTR_LOGIN_TIMEOUT) is only done if the connection_timeout django property is greater than 0. Are you setting that at all? — jsagrera, Aug 01 '18 at 14:09
@J_S I didn't set the property at all, I've added the following options to the database options parameters to make sure but no improvement `'OPTIONS': { 'dsn' : 'test_ifx', 'connect_timeout': 0, 'timeout' : 0, }` — PatrickStewball, Aug 01 '18 at 14:17
let me check the code again, You can use SQL_ATTR_CONNECTION_TIMEOUT before the connection, but not after, if is set after you get the "driver not capable" — jsagrera, Aug 01 '18 at 14:40
I know it's not ideal, but an option would be to comment out the SQLSetConnctAttr(cnxn->hdbc, SQL_ATTR_CONNECTION_TIMEOUT....) line in the pyodbc connection.cpp file. That will stop the call ;). In your ODBC trace it's clear the call is done after the connection is establish. Maybe from django the connection is "reused" a second time, and it tries to reset the connection timeout. — jsagrera, Aug 01 '18 at 14:56
looking at the pyodbc code it appears SQL_ATTR_CONNECTION_TIMEOUT is set even if the value is set to zero which is not ideal. It should skip the SetAttr call like it does with other attributes. To me it should have a "if (timeout > 0) ...." before the block. — jsagrera, Aug 01 '18 at 15:21
Are you using FreeTDS as the underlying driver? If so, take a look at the `host_is_server` option: https://github.com/michiya/django-pyodbc-azure You may have to turn it to false, or connect using the server name and password instead of the DSN. — FlipperPA, Aug 01 '18 at 15:43

jsagrera · Accepted Answer · 2018-08-01T16:35:42.627

django-pyodbc-azure will not work with the Informix ODBC without some rewriting. The "driver not capable" error is caused by the following code in pyodbc:

    unicode_results = options.get('unicode_results', False)
    timeout = options.get('connection_timeout', 0)
    retries = options.get('connection_retries', 5)
    backoff_time = options.get('connection_retry_backoff_time', 5)
    query_timeout = options.get('query_timeout', 0)

    conn = None
    retry_count = 0
    need_to_retry = False
    while conn is None:
        try:
            conn = Database.connect(connstr,
                                    unicode_results=unicode_results,
                                    timeout=timeout)
        except Exception as e:
            for error_number in self._transient_error_numbers:
                if error_number in e.args[1]:
                    if error_number in e.args[1] and retry_count < retries:
                        time.sleep(backoff_time)
                        need_to_retry = True
                        retry_count = retry_count + 1
                    else:
                        need_to_retry = False
                    break
            if not need_to_retry:
                raise

 #   conn.timeout = query_timeout
    return conn

the "conn.timeout = query_timeout" cause the SQLSetConnectAtt() call because it tries to set the attribute after the connection is establish. It also doesn't make much sense because query_timeout and connection timeout are two different things.

Anyway, even if you fix that (the comment will do it) the sql_server.pyodbc code has some SQL specific for SQLServer. which Informix does not understand.

django fails with:

    raise dj_exc_value.with_traceback(traceback) from exc_value
File "D:\infx\Python36-32\lib\site-packages\django_pyodbc_azure-2.0.6.1-py3.6.egg\sql_server\pyodbc\base.py", line 462, in _set_autocommit
File "D:\infx\Python36-32\lib\site-packages\django_pyodbc_azure-2.0.6.1-py3.6.egg\sql_server\pyodbc\base.py", line 424, in _get_trancount
django.db.utils.ProgrammingError: ('42000', '[42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (-201) (SQLExecDirectW)')

because it tries to execute:

    python.exe mana 1588-2630   ENTER SQLExecDirectW 
    HSTMT               0x0471EB20
    WCHAR *             0x03F55420 [      18] "SELECT @@TRANCOUNT"
    SDWORD                    18

    python.exe mana 1588-2630   EXIT  SQLExecDirectW  with return code -1 (SQL_ERROR)
    HSTMT               0x0471EB20
    WCHAR *             0x03F55420 [      18] "SELECT @@TRANCOUNT"
    SDWORD                    18

    DIAG [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (-201)

There are not many SQLServer sql statements in the 'base.py', so you may want to try rewriting those for Informix, but I'm not 100% sure it will work. On a second though, not it's not as simple. 'introspection.py' is full of SQLServer specific SQL.

Thanks for your answer, I'll not go into changing pyodbc as it sound tedious and with no guarantee of sucess. Do you have any idea that don't involve pyodbc to connect to an informix database? — PatrickStewball, Aug 02 '18 at 08:00
Umm. I guess you want something compatible with Django right? IfxPy it's a module specific for Informix, but there is no module for django. — jsagrera, Aug 02 '18 at 08:13

Connecting to a legacy informix database with django

1 Answers1