Connecting from .NET Core to Informix on Linux

0
votes

We are trying to figure out a good strategy to connect a .NET Core Microservice to an Informix DB. The .NET Core Microservice is running on RHEL Linux.

Our current implementation uses the OdbcConnection .NET Core class to attempt its connection. No matter what I've tried I also get the same result. It can't connect.

Here's an example connection string we are using:

Dsn=;Driver=/opt/HCL/informix/4.10/lib/cli/iclis09b.so;Host=redactedip;Server=redactedserver;Protocol=olsoctcp;Database=redacted;LogonID=redacted;Uid=redacted;Pwd=redacted;Client_Locale=en_US.CP1252;Db_Locale=en_US.819;

I have attempted an odbc.ini file with a DSN crafted within it. This works from isql at the command line, but does not work in our .NET Core app. (We've also used these same settings above to set DSN in odbc.ini)

I have also attempted connection strings in many different fashions. None which work.

Can you connect to Informix on Linux without an odbc.ini? (Just using a connection string?)

Are there other packages/solutions for .NET Core which would perhaps be better and more robust than a raw ODBC Connection? I've seen packages like IBM.Data.DB2.Core which is supposed to be able to access Informix. Has anyone used this successfully?

Even a source example of a working Linux configuration of ANY type for a .NET Core application would be helpful...

Any ideas?

1
.netlinux.net-coreinformix
A .NET Core provider would be available for Windows and Linux in the next version of Informix CSDK. In the meantime you should be able to connect using the System.Data.Odbc provider, just make sure you have set 'UNICODE=UCS-2' in your ODBC.INI. I ran a quick test on my Ubuntu box and it works fine. Better if to use the latest 4.50 CSDK. – jsagrera
@jsagrera - So you would recommend a odbc.ini approach as opposed to a connection string approach? Have you been able to connect successfully with a connection string alone? This is the cleanest approach for us from a CI/CD perspective. If you can give me an example of a connection string that works it would be very helpful. I've seen many examples so far and none seem to. – James Scott
Next CSDK (4.50.FC4) will have a 'new' SQLSetConnectAttr called SQL_INFX_ATTR_CALL_FROM_DOTNET which will automatically set UCS-2. It will also set other options that are needed to properly work with .NET Core on Linux. Just a couple of suggestions, enable 'needOdbcTypesOnly' in the connection string and switch to the threaded version of the driver 'iclit09b.so', it will work better ;) – jsagrera

1 Answers

1
votes

OK, very happy to say I found a solution. As jsagrera mentioned the UNICODE=UCS-2 setting seems to be pretty important. Here are some of the things that were misconfigured that once I fixed really helped out:

1) Permissions - make sure you have appropriate permissions on the informix driver directory.

2) Permissions - make sure you have appropriate permissions on odbcinst.ini

3) Make sure any driver you reference in connection string OR odbc.ini exists as a match in the odbcinst.ini

4) You CAN use a connection string but still have to reference an odbc.ini file for that one UNICODE setting. I have tried a few different things to get around it by including it as an ENV variable, or in connection string itself. It doesn't seem to work.

5) Here are some properties that I used in my connection string: Dsn=;Driver=Informix;Host=redactedIP;Server=redactedsqlhost;Protocol=olsoctcp;Database=redacted;Uid=redacted;Pwd=redacted;Client_Locale=en_US.CP1252;Db_Locale=en_US.819; 6) I also had to define the following env vars for my process: INFORMIXDIR, INFORMIXSQLHOSTS, ODBCINI, LD_LIBRARY_PATH

Good luck! I'm still working on a solution for unicode so I don't have to include the entry in my odbc.ini file.

UPDATE: After additional experimentation with the DB2 .NET Core Provider I was able to connect with that as well. (After I enabled the DRDA protocol for informix). If you are looking for an easy way to test the .NET Core Provider I would suggest downnloading the Informix Docker Container. It already has DRDA enabled and you can connect through localhost to experiment.