UnixODBC does not work without an intermediate shim or connector between it and the database. In the case of Informix, the connector is supplied by IBM in the CLI distribution files. If you ran installclientsdk during the install procedure (described above), you should be in good shape. Look for the files libifcli.so and igo4a304.so (maybe in /usr/lib or perhaps under cli and esql in /usr/share/informix/lib, /usr/local/informix/lib or /opt/informix/lib).
Before you begin, make sure that the usual Informix setup has been completed (see the section Using an Informix Database, above), specifically that:
/etc/ld.so.conf.d/informix-i386.conf:
.
.
/usr/share/informix/lib/cli
/usr/share/informix/lib/esql
5) Once this file is created, you can rerun ldconfig:
su
ldconfig
To make the hookup between UnixODBC and the Informix connector, make sure there is an entry in the ODBC instances file that points to the Informix connector. If UnixODBC was installed in /usr/local/unixODBC (as we show above), the file is /usr/local/unixODBC/etc/odbcinst.ini. If an RPM or other OS package was installed, the file may be /etc/odbcinst.ini.
Make sure, when you add the Informix driver to odbcinst.ini, that there is no stray white space between the parameter names and their values. When you access the database, if you get "Server %s is not listed as a dbserver name in sqlhosts" or anything similar, it is probably caused by stray white space in either the Informix driver definition in odbcinst.ini or the DSN entries, in odbc.ini, below, that use the Informix driver. The driver's parser seems to be very easily confused.
The connection to the Informix connector should look like this:
[Informix]
Description=Informix driver for Linux
Driver=/usr/share/informix/lib/cli/libifcli.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.51
FileUsage=0
SQLLevel=1
smProcessPerConnect=Y
Note that we obtained the ODBC version that the driver speaks for the DriverODBCVer parameter from the UnixODBC drivers table found at:
http://www.unixodbc.org/drivers.html
From that chart, the 2.2.14 version of UnixODBC speaks version 3.51 of the ODBC protocol to the Informix connector.
The permissions on odbcinst.ini can be:
-rw-r--r-- root root
Define the ODBCINI environment variable to point at your system wide odbc.ini. Do something like:
export ODBCINI=/usr/local/unixODBC/etc/odbc.ini
or
export ODBCINI=/etc/odbc.ini
Note that you must do this, since ODBCINI is the only way to control where the Informix driver looks for it's configuration information.
Define your Informix DSNs in odbc.ini (which should be located in the same directory as the odbcinst.ini file). Once again, don't include any whitespace between the parameter names and their values. Also, there must be no comments or whitespace at the beginning of the odbc.ini file. The first line must say "[ODBC Data Sources]" or the driver will fail to find your DSNs. Trust us, it is best to make this file look just like this:
[ODBC Data Sources]
CollectOh=Informix
[CollectOh]
Description=Collections Database
Driver=Informix
Server=localhost
Port=1498
Database=/my/database/path/collectdb
CLIENT_LOCALE=en_us.8859-1
DB_LOCALE=en_us.8859-1
TRANSLATIONDLL=/usr/share/informix/lib/esql/igo4a304.so
Each DSN is listed under the "[ODBC Data Sources]" section with its value set to the name given to the Informix driver in odbcinst.ini. Once you've listed all of the DSNs under the "[ODBC Data Sources]" section, you can include comments but no whitespace. The "Driver" parameter of each DSN should also have its value set to the name given to the Informix driver in odbcinst.ini.
The "Server" parameter can be set to "localhost" or it can be set to the same name used for the database engine, as it is set up in sqlhosts. If you use the database engine name, presumably you can connect to databases that are hosted on remote machines via TCP.
If the port used in the sqlhosts file is something non-intuitive, its number should be included with each DSN. We're not sure what "intuitive" is so we just get the number from /etc/services and/or sqlhosts and always include it.
For full-blown Informix, the "Database" parameter should be set to just the database name, since the server knows where to find all of the databases (e.g. "Database=CollectOh"). For Informix SE, the "Database" parameter should be set to the complete path name (minus the ".dbs" part) of the database, as shown above.
You will have to locate igo4a304.so in the directory where it actually lives and put its name in for the "TRANSLATIONDLL" parameter but it should be found somewhere in the same path where Informix was installed, usually under lib/esql.
The permissions on odbc.ini can be:
-rw-r--r-- root root
However, if for some reason it is necessary that the user name and password be set in the DSN definition, it may be better to use something like this:
-rw-r----- root informix
But, note that if you do this, you will need to include all of the users of ODBC in the informix group. Typically, this will include apache, since httpd now runs under the apache userid and PHP programs are typical users of ODBC to access databases for web pages.
If you do set a user name and password in any of the DSNs, make sure that the values for the User parameter and Password parameter both match the user information set when the database was created and permissions assigned.
You should now (assuming you have permissions to connect to the database) be able to connect to the database using ODBC. Try the unixODBC command:
isql -v CollectOh
If you need to use a username and possibly a password, try this:
isql -v CollectOh user [password]
Note that you may need to supply a path name for isql, if UnixODBC was installed somewhere that is not in the current path:
/usr/local/unixODBC/bin/isql -v CollectOh
If isql can't connect to the database, you may wish to consult the "Troubleshooting UnixODBC Connector Problems" section in these notes, to diagnose and fix any generic UnixODBC problems. Errors that are specific to the Informix driver follow in the next paragraphs.
If you get an error message that reads "Unspecified System Error = -11041", it could well be due to the fact that you didn't set the INFORMIXDIR environment variable to the root of your Informix installation (typically /usr/share/informix, /usr/local/informix or /opt/informix) and/or export it properly. Do something like:
export INFORMIXDIR=/usr/share/informix
Or, if you get an error saying "Unspecified System Error = -25553", it may mean that, like us, you are an idiot and have set INFORMIXDIR to the wrong path name.
If you get an error message that reads "Data source name not found and no default driver specified", it could be caused by your failure to set the ODBCINI environment variable to point to your odbc.ini file. Do something like:
export ODBCINI=/usr/local/unixODBC/etc/odbc.ini
or
export ODBCINI=/etc/odbc.ini
Other reasons for this error are the "[ODBC Data Sources]" section not being the absolutely first line in the odbc.ini file, the DSN not being mentioned in that section or anything else that is bunged up in the DSN's parameters. Have fun debugging this one. The best advice is to make the odbc.ini file look EXACTLY like it is shown above.
If you finally get UnixODBC isql to connect to the database, it will give you an SQL> prompt. At that point, you can type in any valid SQL query against your chosen database and you should see an answer. A good, quick test is the "select tabname from SYSTABLES;" command, which will enumerate all of the tables in the chosen database (there is no "show tables" in Informix). When you are done playing and marvelling at how wonderful ODBC is, you can type "quit" to get out. Congrats! Your ODBC shim works.
A useful application of the Informix/ODBC connector is to access an Informix database from PHP. If you'd like to use an Informix database from PHP, this example may prove instructive:
<html>
<head>
<title>ODBC test script</title>
</head>
<body>
<p align="center"><b>ODBC Test Script</b></p>
<script language="php">
// We must set these environment variables for Informix to work. Either
// do it here or in php.ini.
putenv("INFORMIXDIR=/usr/share/informix");
putenv("ODBCINI=/usr/local/unixODBC/etc/odbc.ini");
// Open up a connection to the database.
if (!($con = odbc_connect("CollectOh", "", "")))
echo "<p>Connection to CollectOh failed.</p>\n";
else
{
// Let's try enumerating all of the tables in the database (there ain't
// no "show tables" here).
if (($res = odbc_exec($con, "select * from SYSTABLES")))
{
echo "<p>\n";
odbc_result_all($res);
echo "</p>\n";
}
// Close up shop, like good dobies.
odbc_close($con);
}
</script>
</body>
</html>