I am trying to query an Informix database via an ODBC created on IBM Informix ODBC driver version 4.10.00.15364 from Teradata SQL assistant.
I am getting all blanks in output where the field type is varchar or char in source though the source database has information in those columns. Even when I export data in a flat file, the varchar/char columns come as blank. The version of Teradata SQL assistant is 14.10.0.02
Is there any resolution available?
This was first reported a year or so back.
When I traced the application I found that the Microsoft .Net Data Provider for ODBC was specifying a length of 2 for the data buffer when retreieving character data.
That's only long enough to return the null character at the end of a string - so the result is always empty strings returned to the application.
This occurs only when using the newer Informix ODBC drivers (3.5 and above I think it was)
I tried changing a bunch of different settings within the ODBC DSN but I could not persuade it to set a more reasonable buffer size.
Unfortunately SQL Assistant has no direct control over what is passed to the Informix driver. As a .Net application it always works indirectly through the .Net Data Provider, which in turn talks to the Informix ODBC driver.
Informix did tell us that they had 'steamlined' the ODBC driver for performance ... and apparently something they changed makes microsoft's provider make a very stupid choice in buffer size... but we were not able to determine what that was.
As a result NO version of SQL Assistant after 12.0 will work correctly with Informix unless you use the older ODBC driver.
(Version 12.0 should work fine since it is not a .Net app so it directly talks to the Informix ODBC driver and will use the correct buffer size.)