pyodbc fails to connect to Informix DSN while isql works perfectly (-11060 SQLDriverConnect error)

[ThomasSchillingAdmin]

I'm working with credativ-pg-migrator (thanky you for the great work) to migrate data from Informix to PostgreSQL using a DSN connection via pyodbc. My system is Debian-based, using Python 3.12, unixODBC 2.3.12, and IBM INFORMIX ODBC DRIVER.

The DSN is correctly configured in /etc/odbc.ini and works flawlessly with isql:

isql -v jetze

However, any attempt to connect via pyodbc.connect("DSN=jetze") fails with the following error:

pyodbc.Error: ('H000', '[H000] [ (-11060) (SQLDriverConnect)')

I've verified the following:

DSN appears in pyodbc.dataSources()

Treiberpfad in odbcinst.ini is correct (iclit09b.so exists and is readable)

DSN contains Servername, Database, LogonID, pwd, locales, and translation DLL

sqlhosts definition matches SERVER=tcp_azur_Test with onsoctcp, hostname, and service/port

/etc/services contains the correct entry for informix 9088/tcp

Environment variables are properly set:

export ODBCINI=/etc/odbc.ini
export ODBCINSTINI=/etc/odbcinst.ini

All attempts to bypass DSN and use a manual connection string also result in the same -11060 error.

Furthermore, I attempted to downgrade pyodbc to version 4.0.34, but the build fails under Python 3.12 due to compatibility issues with Unicode APIs.

Request:

Is there known incompatibility between pyodbc and Informix ODBC drivers in Python 3.12?

Would it be possible to recommend either:

A working version of pyodbc that supports Informix on Python ≥ 3.12

A workaround or config fix that avoids -11060

Integration of an alternative Python ODBC layer compatible with credativ-pg-migrator?

Any help or insights would be appreciated — thanks!

[JosefMachytka-credativ]

Hi,
currently I have best results with JDBC connection to Informix - see in https://github.com/credativ/credativ-pg-migrator/wiki/Connection-to-Informix

But I will retest ODBC and let you know

[JosefMachytka-credativ]

I found one issue in the migrator code for Infomix ODBC connectivity. I pushed repair to the main branch. Beside FreeTDS and unixODBC also IBM Informix ODBC client must be installed. But even with this repair I am unable to make ODBC connection working. I will try to debug it in coming days.

[ThomasSchillingAdmin]

meanwhile ;-)

Summary of our successful workaround for Informix connection in credativ-pg-migrator

We were facing repeated crashes and connection errors with the default pyODBC driver used in credativ-pg-migrator, specifically SQLDriverConnect (-11060) and even stack overflows when executing simple queries like SELECT 1.

Our workaround:

✅ We created a custom Python wrapper that exposes connect(), cursor(), execute() and fetchone() using the IfxPy driver.

✅ We placed it as pyodbc.py inside our Python environment's site-packages, effectively hijacking the pyodbc import with our own implementation.

✅ The native pyodbc.so module was disabled to ensure Python prefers our wrapper.

✅ We added fallback logic inside the wrapper to replace SELECT 1 (unsupported in Informix) with SELECT 1 FROM systables WHERE tabid = 1, and return {0: 1} via fetchone() to satisfy the planner's expectation.

💥 Result: The tool loads our wrapper, connects to Informix reliably, runs without stack crashes, and passes all connection checks during dry-run.

We consider this a clean, modular replacement for unstable pyODBC integrations in Informix scenarios. Happy to share the wrapper if others run into similar problems.

[JosefMachytka-credativ]

IfxPy seems to function only up to python 3.8 - it refuses to be installed on 3.9+
So, I presume you use this version? Or did you solve installation on 3.9+?

[ThomasSchillingAdmin]

I’m using a Python 3.9 (pgm-env-39) environment for IfxPy. To make the installation work, I had to downgrade setuptools to version 57.5.0.

System setup:
OS: Ubuntu 22.04
Python: 3.9.18

Installed packages:

credativ-pg-migrator 0.9.1
IfxPy 3.0.5
JayDeBeApi 1.2.3
jpype1 1.5.2
numpy 2.0.2
packaging 25.0
pandas 2.3.0
pip 23.0.1
psycopg2 2.9.10
pyodbc 4.0.32
python-dateutil 2.9.0.post0
pytz 2025.2
PyYAML 6.0.2
setuptools 57.5.0
six 1.17.0
tabulate 0.9.0
tzdata 2025.2

I placed the wrapper directly in .pyenv/versions/pgm-env-39/lib/python3.9/site-packages/pyodbc.py to avoid breaking the rest of the system.

It’s still not running flawlessly—there are some hurdles left to clear before it’s truly production-ready. Ideally, I’d prefer running this excellent software natively. Maybe it’s time we explore a Docker-based solution?

[JosefMachytka-credativ]

This is interesting, with setuptools downgraded to 57.5.0 I can install IfxPy even with python 3.11, but connectivity does not work. I solved all missing dependencies, but connection attempts still end with the same error:

[Informix][Informix ODBC Driver][Informix]Unspecified System Error = -23101. SQLCODE=-23101

And so far I cannot solve it. But it also seems to suggest that IfxPy actually uses internally ODBC connectivity configured on that machine.

[JosefMachytka-credativ]

Unfortunately IfxPy seems to be abandoned - there is for example this issue opened from 2022 - OpenInformix/IfxPy#74 - asking for update to 3.11, another from 2023 asking for python 3.12...

Anyway, it looks like ifxPy is most likely just wrapper for ODBC

[ThomasSchillingAdmin]

I ran into the same problem. If the pyodb.so exists in the directory where pyodbc.py is located, the .so file has priority. I deleted it so that pyodb.py was loaded.

Here is my experimental Wrapper:

import IfxPy

print("Wrapper geladen!")

class IfxCursor:
    def __init__(self, conn):
        self.conn = conn
        self.stmt = None
        self._manual_result = None


    def execute(self, sql):
        if sql.strip().upper() == "SELECT 1":
            print("------------AUSNAHME-------------")
            self.stmt = None
            self._manual_result = {0: 1}  # Speichern für später
        else:
            print("🧪 SQL:", sql)
            self.stmt = IfxPy.exec_immediate(self.conn, sql)


    def fetchall(self):
        results = []
        row = IfxPy.fetch_assoc(self.stmt)
        while row:
            results.append(row)
            row = IfxPy.fetch_assoc(self.stmt)
        return results

    def fetchone(self):
        if self._manual_result:
            result = self._manual_result
            self._manual_result = None
            return result
        row = IfxPy.fetch_assoc(self.stmt)
        if row:
            # Automatisch numerischen Key einbauen
            return {i: val for i, val in enumerate(row.values())}
        return None


    def close(self):
        if self.stmt:
            IfxPy.free_stmt(self.stmt)

class IfxConnection:
    def __init__(self, conn_str):
        self.conn = IfxPy.connect(conn_str, "", "")

    def cursor(self):
        return IfxCursor(self.conn)

    def close(self):
        IfxPy.close(self.conn)

    def commit(self):
        pass  # optional, Informix autocommit enabled?

    def rollback(self):
        pass  # optional

def connect(dsn=None, **kwargs):
    # Translate DSN from odbc.ini into connection string (mock)
    # Oder feste Verbindungsparameter als Platzhalter
    conn_str = (
        "SERVER=tcp_azur_Test;"
        "DATABASE=dokumente;"
        "HOST=10.0.1.7;"
        "SERVICE=9088;"
        "PROTOCOL=onsoctcp;"
        "UID=user;"
        "PWD=pass"
    )
    return IfxConnection(conn_str)

If "Wrapper geladen" is debuged, the Wrapper works

[JosefMachytka-credativ]

I was busy with migrations. But problem is I still cannot connect Informix via ODBC on my Debian 12 testing environment. And one of our clients had similar issues when trying ODBC connection to Informix on their network. I will return to this when I have more time.