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

[PyodbcUnix]

Hi @asser,

do you have some news here?

[mkr-ac]

Hi there!

Struggling with the same issue. Any updates?

@asser few questions:

1. You've diagnosed the issue to be caused by a mismatch between unixODBC and Informix CSDK. However, I'm not really sure, which one of these is in fact causing the issue (by just reading, you could have just adapted unixODBC to broken Informix CSDK, that could have worked, but other drivers might not work with your new version, do they?).
2. I've looked into the unixODBC's GH and could not find any related issue. Have you reported it?
   Thanks!

[asser]

Hi, sorry for the lack of response. I haven't had the opportunity to investigate this further, so I have no updates to share either unfortunately. For now the project I am working on is happy with Pyodbc v4.

Re. your questions @mkr-ac ,

1. No I'm not sure at all here either. That's why I was thinking to raise the issue with unixODBC, as they might be able to provide some more guidance.
2. I did not report the issue. If you do that, do feel free to tag me in case I can contribute with something :)

[mkr-ac]

Hi @asser,

I spent some time researching and Copiloting the issue.

1. Microsoft Standard says, that SQLWCHAR is wchar_t. However, wchar_t on Windows refers to UTF-16, and therefore is 2 bytes long.
2. Most ODBC drivers, which are ported to Linux from Windows, implement this on linux side as unsigned short. In fact, it appears that everyone except Informix does it like this (at least Copilot-based research returned this result, take it with a grain of salt :) ).
3. I believe that's the reason why unixODBC uses unsigned short as default - it's compatible with about everyone except Informix.
4. IBM says on Informix documentation pages: The ODBC specification defines these functions with the wchar_t data type. and they implement it like so, but using Linux-standard wchar_t literally. That is 4-bytes int. Quite probably the only ones in the world, and they're proud of it.

So, I can't even imagine feasible solution. Answer from colleagues was - just use JDBC...

Unfortunately, downgrading to pyodbc v4 would cause chain reaction of downgrading and end up in complete rebuild of containers, which I want to avoid. So, I quite probably will have to let it be and follow my colleague's advice...

[PyodbcUnix]

Hi together,

wouldn't it be good if this project would have again that also the possibility without the W function? As it Was in pyodbc before? What should be the disadvantage?

BG

[PyodbcUnix]

@gordthompson @mkleehammer @keitherskine could you please implement that back?

[asser]

wouldn't it be good if this project would have again that also the possibility without the W function? As it Was in pyodbc before? What should be the disadvantage?

No idea about any disadvantages, but yea that would "fix" the issue with Informix CSDK (ie. allow to use the ANSI connect workaround again).

So, I can't even imagine feasible solution. Answer from colleagues was - just use JDBC...

@mkr-ac That sums it up pretty well I think. I think unixODBC has some kind of encoding translation going on, when setting UNICODE in odbc.ini (see earlier in the thread). But that only seems to work once connected to the database, so it won't apply to SqlDriverConnect.

I think it might be related to this unixODBC issue, or at least there's some discussion in there that looks relevant: lurcher/unixODBC#33

But again, the unixODBC people know better :-)