Pyodbc v5 Informix SqlDriverConnect Error

[PyodbcUnix]

Environment

• Python: 3.10
• pyodbc: 5.2.0
• OS: Ubuntu 22.04
• DB: Informix
• driver: InformixClient SDK 4.10

Issue

Hello together,

First i want to thank you for providing this cool package!
I updated my System to pyodbc 5.2.0 and now i get back the error SQLDriverConnect.w -11060 SQLError.

With pyodbc 4.0.39 everything works fine.
Hope you can help me...

[gordthompson]

It would be helpful if you could create ODBC trace logs when calling pyodbc.connect() for both cases — 4.0.39 (successful) and 5.2.0 (unsuccessful) — and post the logs here as file attachments.

[asser]

I've been having the same problem for a while, attaching the ODBC trace log here. It's a simple pyodbc.connect(dsn='database') call.

odbctrace.log

Does it contain anything useful?

I was trying to replicate the issue directly in C code, with SQLDriverConnectW(), hoping to find out what changed from 4.0.39 to 5.x, but gave up temporarily :)

[asser]

Here's the trace log for Pyodbc 4.0.39:
odbctrace-4.0.39.log

It seems actually that SQLDriverConnectW() also fails, but it then falls back to SQLDriverConnect() which succeeds. That would explain why 5.x doesn't work, since SQLDriverConnect() was removed in commit 5c1f1c0 ?

No idea why SQLDriverConnectW() wouldn't work though.

[PyodbcUnix]

Hi @asser,

first thank you for Debugging that problem! Did you use the same Informix Client?

@gordthompson is it possible to do a Bugfix on that? Would be really great!

Wish you a nice year!

[asser]

@PyodbcUnix Yes, both of these are with CSDK 4.50.FC11.

According to Informix' own documentation, SQLDriverConnectW() is supposed to work though, so I don't know if there is something about parameter encoding. Informix is not very informative in its error messages. I might try to see if I can raise a support issue with them to find out, but it could take a while to get a response.

Ref. https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=unicode-supported-functions

I just now noticed they have a new CSDK 15.0.0 out as of Nov 19, I'll give that a try and see if it makes a difference.

[PyodbcUnix]

Hi @asser

can you try to add this in the odbc.ini or if there is already an ODBC section the following:

[ODBC]
UNICODE=UCS-2

Please also try to change UCS-2 to UCS-4 after test fail.

If there already was this UNICODE Parameter then also try to comment out with ; because then default is UTF-8

BG

[asser]

@PyodbcUnix Yes, it's already set to UCS-2 (in odbcinst.ini) and I've tried variations for it directly in odbc.ini - UTF-8, UCS-4, UCS-2, no value - all with the same error.

I've opened a support request now with our Informix supplier, hopefully they can help. I'll report back.

At this point I believe it's more of a driver issue or maybe unixODBC, causing SQLDriverConnectW() to always fail.

[asser]

Heard back from our supplier, unfortunately their support don't want to help out since they don't provide the Informix DB to us directly. A bit disappointing, even if it makes sense (their product is a package/application backed by Informix).

I don't have other ways of raising the issue with IBM, but if anybody does it would be good :)

[PyodbcUnix]

Hi @asser,

Oh thats unfortunately not good. But in odbcinst.ini you also did the tests in odbc.ini without the option in odbcinst.ini?

Maybe we have to change the wording of the connection string. I dont know. Im going crazy...

BG

[PyodbcUnix]

@gordthompson @mkleehammer @keitherskine i think there are more Environment or driver issues with the Connect W functions. Would it be possible that you add the ANSI again like it was in pyodbc 4.x.x?

Thanks for your help!

BG

[asser]

Oh thats unfortunately not good. But in odbcinst.ini you also did the tests in odbc.ini without the option in odbcinst.ini?

Maybe we have to change the wording of the connection string. I dont know. Im going crazy...

Sorry I didn't follow up on this - I think I had the option in odbcinst.ini at the same time yes. I'll see if I can try it without.

I got inspired to try out some C code today, and found out how to directly link against the Informix libraries without going through unixODBC. And it seems that:

ret = SQLDriverConnectW(V_OD_hdbc, 0, (SQLWCHAR *) L"dsn=economi", SQL_NTS, 0, 0, 0, SQL_DRIVER_NOPROMPT);

works, while:

ret = SQLDriverConnectW(V_OD_hdbc, 0, (SQLWCHAR *) u"dsn=economi", SQL_NTS, 0, 0, 0, SQL_DRIVER_NOPROMPT);

does not work ("economi" is the DSN I configured in odbc.ini).

So the problem might then be in the encoding of the connection string passed to the driver? I have no idea how unixODBC handles this, or if it's possible to control without modifying unixODBC.

Any ideas? I'm happy to try out anything, I'm not sure where to go from here, except see if I can get connect to work linking against unixODBC and trying different string encoding (but need to read up on some more code first) ;-)

[PyodbcUnix]

Hi,

@asser i also tried multiple different settings and strings but unfortunately failed.
And yes it has definetly something to do with the unicode and encoding.
Hope you'll get a solution on this. Otherwise the only thing would be using the non wide Charakter functions.

BG

[asser]

Thanks for following up on it so quickly! It's not a big issue for me, at the moment we're happily using pyodbc 4.0.39 anyway.

I'll probably dive some more into it later to satisfy my own curiosity :)

[PyodbcUnix]

Yeah its not a big issue now but in 2027 there is no support for python 3.11 anymore...

[asser]

Ok, this was a bit interesting. I messed around with unixODBC and built a local copy with CFLAGS=-DSQL_WCHART_CONVERT set.

This makes SQLDriverConnectW() with L-string work:

ret = SQLDriverConnectW(V_OD_hdbc, 0, (SQLWCHAR *) L"dsn=economi", SQL_NTS, 0, 0, 0, SQL_DRIVER_NOPROMPT);

As in it connects successfully to Informix.

SQL_WCHART_CONVERT forces SQLWCHAR to be defined as type of wchar_t instead of unsigned short.

So there seems to be some mismatch between the type definition of SQLWCHAR between unixODBC and the Informix CSDK, which I guess leads to the problem.

pyodbc didn't like my newly built library at all, and returned an error padded with \x00:

Traceback (most recent call last):
  File "/home/asser/projects/unilaan-python-backend/test-odbc/../test-odbc.py", line 6, in <module>
    db = pyodbc.connect(dsn='economi')
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pyodbc.Error: ('IM0', '[IM0] [\x00u\x00n\x00i\x00x\x00O\x00D\x00B\x00C\x00]\x00[\x00D\x00r\x00i\x00v\x00e\x00r\x00 \x00M\x00a\x00n\x00a\x00g\x00e\x00r\x00]\x00D\x00a\x00t\x00a\x00 \x00s\x00o\x00u\x00r\x00c\x00e\x00 \x00n\x00a\x00m\x00e\x00 (0) (SQLDriverConnect)')

To be fair, I didn't yet recompile pyodbc against my modified unixODBC, which will be the next step when I return to this.

[asser]

I suppose at this point I should raise the issue with unixODBC ;)

[PyodbcUnix]

Hi @asser

Wow cool you are more intelligent than me :D

But why do you have to change the Compile from pyodbc? It only calls the driver Manager.

BG