Connection string for Informix for .NET

Question

We are using an Informix database, and are connecting to it from .NET sucessfully using ODBC. The connection string we are using is;

DRIVER={IBM INFORMIX ODBC RIVER};
UID=username; PWD=password;
DATABASE=our_database;
HOST=devsrv01;
SERVER=devsrv01_tcp;
SERVICE=ids9tcp2;
PROTOCOL=onsoctcp;
CLIENT_LOCALE=en_US.CP1252;
DB_LOCALE=en_US.819;

We want to change from ODBC and use IBM's SDK and libraries as outlined on their site.

The code we are using is;

string ConnectionString = "Database=our_database; Server=172.22.0.0:1528; UID=username; Password=password; ";

try
{                               
    IfxConnection conn = new IfxConnection(ConnectionString);
    conn.Open();
}
catch (IfxException ex)
{}

The conn.Open() throws the exception;

ERROR [08001] [IBM] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "172.22.0.0". Communication function detecting the error: "recv". Protocol specific error code(s): "", "", "0". SQLSTATE=08001

"Ah ha!" you say. Just put in "PROTOCOL=onsoctcp;" But this makes the IfxConnection(ConnectionString); command throw an ArgumentException. If the connection string contains any invalid <field>=<value> setting it throws this exception. If I put in garbage=junk; in it throws the same ArgumentException which makes me think it doesn't recognise the Protocol (or PRO) field.

(FYI) 172.22.0.0 is the IP for devsrv01 and does not end 0.0.

Answer 1

Did you check http://www.connectionstrings.com/ ? It might offer some insight.

Answer 2

The manual (http://publibfp.boulder.ibm.com/epubs/pdf/c2394251.pdf) uses Protocol or PRO rather than PROTOCOL. Are the keywords case-sensitive? The notation suggests it might be, but I don't know enough about .NET to be sure. And the error you get when you use PROTOCOL suggests that it might be case-sensitive.

(The URL might not work directly: I may have validated with a login address somewhere along the route. I had enough problems with my machine on the way that I've forgotten. )

Answer 3

Assuming you want to use the .NET, the Informix CSDK (not the DRDA one) and ADO.NET the easiest way to get connected is to use add the database to Visual Studio's list of Data Connections (View -> Server Explorer, then click the "Connect to Database" button, then just fill in the form (This assumes you installed the Visual Studio Extensions with the CSDK...)).

Then go into the project's properties, go to the setting tab, add a new setting with a type of '(Connection String)' and for the value put:

 Database=mydatabase;Password=mypassword;Server=myserver;User ID=myuser"

So in the app.config it will look something like this:

 <add name="MyApplication.Properties.Settings.MyConnectionString"
  connectionString="Database=mydatabase;Password=mypassword;Server=myserver;User ID=myuser"
  providerName="IBM.Data.Informix" />

Also make sure the database and server has been configured in the hosts file (C:\Windows\System32\drivers\etc\hosts) and/or with the Informix Database Connection Tool 'inetd32.exe' located in the Start Menu somewhere...

Also if you so feel inclined you can drag tables and such from the database in the Server Explorer window onto a opened XSD file in Visual Studio to have it automaticlly add the connection string to the configuration and setup everything to give you a typed dataset and it will handled the CRUD layer for you... (Though you might get a bunch of errors because VS cannot pull the Informix schema using the latest drivers for some reason...)

Anyway, you can then use the connection string like:

IfxConnection myconnection = new IfxConnection(MyApplication.Properties.Settings.Default.MyConnectionString);

Answer 4

I eventaully found out that the answer was to include;

Persist Security Info=True;Authentication=Server;

Don't ask me why. I just makes it work.

+1 for everyone - thanks for your help.