Informix-CLI: ODBC for Informix

The type of connectivity provided by the I-Net and I-Connect products is known as native connectivity. It is driven by, written by, and proprietary to the Informix Corporation. The other type of connectivity is known as open connectivity, and is driven by adherence to standards and to free interoperability between vendors. In order to open the Informix database engines to the rest of the non-Informix world, Informix provides ODBC communications with the Informix-CLI (Call Level Interface) product.

Informix-CLI is an ODBC (Open DataBase Connectivity) driver that communicates using the Informix-Connect native database drivers. There are third-party ODBC drivers available that include their own connectivity mechanisms, which claim to be faster than an ODBC driver piggybacking on top of a native driver. One drawback with such drivers is that it is usually necessary to run a special server process on the server host that handles the host's end of the data communications. ODBC is almost strictly a client-side operation. All IDS and most OnLine systems will support ODBC connections with no modifications.

Many third-party reporting and data manipulation programs use ODBC to be able to connect to many databases. Once you set up ODBC on a client system, you can usually use a plethora of tools from various vendors against the same Informix database. We'll concentrate on setting up the Informix-CLI product on Windows NT systems. Most tools using ODBC are Windows-based, so most of your ODBC setups will be on NT systems.

Configure Informix-Connect First

The ODBC services provided by Informix-CLI are transported over the existing communications links provided by Informix-Connect. No ODBC communications will work unless the underlying communication methods are in place. Thus, before you set up ODBC, you have to have gone through the processes listed earlier in this chapter for setting up and testing Informix-Connect connections.

Installing Informix-CLI

Informix-CLI is available in several forms from Informix. At lease with CD distributions of IDS 7.24 for NT, both I-Connect and I-CLI were included on the CD distribution. I have heard that later versions of the engine do not include them. The IDS Developer 7.30 distribution does not include Informix-CLI, although it includes ESQL/C. Informix has a software development kit (SDK) that allows programmers to write and execute ODBC compliant code.

The install scripts for various versions all contain an Informix-Connect version. Before you attempt to install Informix-CLI, check whether there is already an Informix-Connect installed on your client. If there is one installed already, consider doing a test install of Informix-CLI that does not replace your current Informix-Connect. At the very least, realize that installing the Informix-Connect included with CLI may be dangerous to existing communications-dependent programs, and monitor them carefully for error after installing CLI.

Configuring Informix-CLI

This configuration example uses Informix-CLI Version 2.5. At press time, the latest available CLI was Version 3.1, which has a different interface from Version 2.5. The later version makes use of a tabbed user interface much like that of setnet32. The concepts will be the same no matter what version you are using.

The ODBC administrator program controls the setup of ODBC connections. This program can usually be found in the folder or program group that is created during the CLI install process. It can also be found as an icon in the Windows Control Panel. The one administrator program can control ODBC connections to multiple databases from multiple vendors. There are two terms in ODBC that are often confused:

• Data Source: A data source is information about the data that is to be retrieved via ODBC as well as information about how to access the data, such as database name, Informix-Connect parameters, hostname, and the like.

• ODBC Driver: A driver is a dynamic-link library (DLL) that a client application can use to insert, retrieve, and manipulate data in a database. Drivers are specific for various brands of databases.

The program starts and gives you a display of existing data sources. You may select one of the existing data sources for modification through the "Setup" button or for deletion using the "Delete" button.

The "Options" button opens a screen that can be very handy in debugging an ODBC connection. If the "Trace ODBC Calls" box is checked, the system will log every ODBC request to a log file that the user can select. Although this tracing process may slow the ODBC connection down considerably, the logfile records the entire communication between client and server.

Be aware, though, that this logfile can not only slow access; it can also consume lots and lots of disk space as the logfile grows. If you're making the decision to turn ODBC tracing on, be careful to shut it off as soon as you have the information you need. If you are debugging a problem with an existing ODBC installation, check to be sure that someone hasn't left the tracing on inadvertently.

Going back to the "Data Sources" screen, hitting the "Add" button will add a data source to the ODBC system. A list of installed ODBC drivers is shown. Choose the ODBC driver that you want to use for this data source, in our case Informix-CLI 2.5 (32 bit).

Once you've chosen your ODBC driver, you will get a screen allowing you to enter the specific information for your ODBC setup.

Both the data source name and the description boxes are informative in nature and can be just about anything you want them to be. The database name should be in the "databasename@servername" format. The information under the "Advanced" tab is automatically filled in and contains information from the sqlhosts registry entries.

This information is optional, but you can activate such features as management of open cursors, use of scroll cursors, and use of insert cursors from this screen. There's also a "Translate" option that is designed to allow ODBC to handle translation between different character sets. This button allows the user to choose various DLLs that translate the data. This could also be used for encryption/decryption and compression/decompression of data. Unless you really know what you're doing, you don't even need to go into the advanced setup.

The ODBC administrator then returns to its initial screen and shows your new data source. If it gets this far, it has successfully set up the ODBC communications. You can test this by going to any of your client programs that use ODBC and trying to use your new data source.