Connecting to Informix Databases

On Windows and UNIX, ColdFusion lets you access Informix databases using ODBC and native drivers. ColdFusion 5 supports Informix 7.3 and later, including Informix Dynamic Server.

If you install ColdFusion on a Windows server, you can configure an Informix database as a ColdFusion data source using ODBC, OLE DB, or a native driver. For information about using OLE DB with ColdFusion data sources, see "About OLE DB".

Informix for Windows requires version 2.5 or later of either the Informix-Connect for Windows or the Informix Software Developer's Kit for Windows. Informix for Solaris and HP-UX requires Informix-Client Software Developer's Kit version 2.5 or later for UNIX.

Configuring Informix using ODBC

This configuration is now available on all platforms except Linux, which only supports the Informix Dynamic Server. The following table describes ColdFusion options for the MERANT Informix 7.x/9.x ODBC driver. You set these options when you configure a ColdFusion data source.

Option

Description

Data Source Name
A name for your ODBC data source.

Description
Descriptive information about the data source.

Database Name
The name of the database to which you want to connect.

Host Name

The name of the machine on which the Informix server resides.

Use Informix registry for Logon ID and Password Determines whether the server reads the Logon ID and Password directly from the Informix registry.

Server Port Number (Informix Dynamic ODBC Server Driver only)
The number of the server port. This will match the number entered in the services file for the Informix server.

Service (Informix 7.x/9.x Driver only)
The network services file.
On Windows NT, the services file is located in C:\winnt40\system32\drivers\etc.
On UNIX, the file is located in /etc.

Server Name
The name of the Informix server as it appears in the sqlhosts file.

Protocol (Informix 7.x/9.x Driver only)
The network protocol.

Option	Description
Data Source Name	A name for your ODBC data source.
Description	Descriptive information about the data source.
Database Name	The name of the database to which you want to connect.
Host Name	The name of the machine on which the Informix server resides. Use Informix registry for Logon ID and Password Determines whether the server reads the Logon ID and Password directly from the Informix registry.
Server Port Number (Informix Dynamic ODBC Server Driver only)	The number of the server port. This will match the number entered in the services file for the Informix server.
Service (Informix 7.x/9.x Driver only)	The network services file. On Windows NT, the services file is located in C:\winnt40\system32\drivers\etc. On UNIX, the file is located in /etc.
Server Name	The name of the Informix server as it appears in the sqlhosts file.
Protocol (Informix 7.x/9.x Driver only)	The network protocol.

Configuring Informix using the native driver

The configuration options for ColdFusion native drivers are the same for Windows NT and UNIX. The following table describes ColdFusion options for the Informix native driver. You set these options when you configure a ColdFusion data source.

Option

Description

Data Source Name
A name for your data source.

Description
Descriptive information about the data source.

Default Database
The name of the database to which you want to connect by default.

Server
The name of the Informix server, including the full path.

Host
The name of the machine on which the Informix server resides.

Service
The network services file.
On Windows NT, the services file is located in C:\winnt40\system32\drivers\etc. On UNIX, the file is located in /etc.

Protocol
The network protocol.

Client Locale
Specifies the language, territory, and code set that the client application (ColdFusion) uses to perform operations that read or write to the database.

Database Locale
Specifies the language, territory, and code set that the Informix server needs to interpret locale-sensitive data types.

Translation DLL
Leave blank.

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Default Database	The name of the database to which you want to connect by default.
Server	The name of the Informix server, including the full path.
Host	The name of the machine on which the Informix server resides.
Service	The network services file. On Windows NT, the services file is located in C:\winnt40\system32\drivers\etc. On UNIX, the file is located in /etc.
Protocol	The network protocol.
Client Locale	Specifies the language, territory, and code set that the client application (ColdFusion) uses to perform operations that read or write to the database.
Database Locale	Specifies the language, territory, and code set that the Informix server needs to interpret locale-sensitive data types.
Translation DLL	Leave blank.

Connecting to Informix data sources (UNIX)

Before you can connect to an Informix data source through ColdFusion, you must perform the following tasks:

Install the Informix client software.
Edit the following files: ColdFusion start script, SQLHOSTS, master NIS, and $INFORMIXDIR/etc/onconfig.
Stop and restart ColdFusion Server.

Installing the Informix client software

The Informix client software does not ship with ColdFusion, but you can download it from the Informix Web site.

To install the Informix client software:

Download the appropriate client software from http://www.informix.com.
You must uncompress and/or untar this file into a separate subdirectory on your server; for example: /opt/isdk.
This is the directory that you point to in the start script as INFORMIXDIR.
Run the script installclientsdk to install the client SDK.
Before you continue, verify that you can connect to the Informix server from a client other than ColdFusion or with a utility such as iconnect.

Editing the ColdFusion start script

Add the following lines to the coldfusion/bin/start script:

# Informix client directory

INFORMIXDIR=/opt/isdk;export INFORMIXDIR

INFORMIXSERVER=alldevtli;export INFORMIXSERVER

INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts;export INFORMIXSQLHOSTS

LD_LIBRARY_PATH=/usr/dt/lib:/lib:/usr/openwin/lib:$CFHOME/lib

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql

Editing the SQLHOSTS file

Add the following lines to the sqlhosts file:

dbserver nettype hostname service name

alldev onipcshm alldev online0

alldevtli ontlitcp alldev turbo

The following table describes the code and its functions:

Code

Description

dbserver
This name matches the value in your Informix server /etc/onconfig file, and also matches the INFORMIXSERVER environment variable in your /coldfusion/bin/start script.

nettype
Determines what kind of network protocol to connect with.

hostname
The hostname of the server where the database is. You can put the IP address or hostname.

service name
The entry in the /etc/services or master NIS file for the port that informix listens on. This can also be the port# for the service name, such as 1526.

Code	Description
dbserver	This name matches the value in your Informix server /etc/onconfig file, and also matches the INFORMIXSERVER environment variable in your /coldfusion/bin/start script.
nettype	Determines what kind of network protocol to connect with.
hostname	The hostname of the server where the database is. You can put the IP address or hostname.
service name	The entry in the /etc/services or master NIS file for the port that informix listens on. This can also be the port# for the service name, such as 1526.

Editing the /etc/services or NIS file

Edit your /etc/services or master NIS file so that it contains a line like this:

turbo 1526/tcp

Editing the $INFORMIXDIR/etc/onconfig file

Edit the $INFORMIXDIR/etc/onconfig file so that it contains the following lines:

# System Configuration

SERVERNUM 0 # Unique id corresponding to an OnLine instance

DBSERVERNAME alldev # Name of default database server 

DBSERVERALIASES alldevtli # List of alternate dbservernames

DEADLOCK_TIMEOUT 60 # Max time to wait for lock in distributed env.

RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)

Stopping and restarting ColdFusion services

After you complete all the steps in this section, you must stop and restart ColdFusion services to reload the odbc.ini file.

Connecting to Informix through ODBC/CLI (Windows, UNIX)

The following setup information for Informix describes how to install and configure Informix client software for Windows and UNIX systems. This information applies to native driver connectivity and ODBC.

In order to install INFORMIX-CLI on Windows NT, you must have administrative privileges. Log on as administrator before performing the installation. Check with your database or network administrator for database server name, host name, correct protocol, and service name.

To install the client software:

Connect to the machine that is hosting the Informix software; for example, on Windows: \\machine1\infshare\informix\Informix_ODS_722.
Run the setup.exe and click Next.
Select Custom.
Select the Client connectivity: I-Connect 7.20, CLI 2.50.

Modifying the services file entry

After the installation is complete you must modify your workstations' Services File located in the \winnt\system32\drivers\etc\ folder for Windows NT and \windows\system\ for Windows 95/98. This entry is needed for the client software to find the instance of the Informix service on your network. Make the following entry at the bottom of the file:

turbo 1526/tcp

Note

If necessary, check with your system administrator for the name of the service.

Configuring Informix SETNET32 settings

After you install the client software, you must configure your workstation to connect to the Informix databases. The following example assumes that the demo database that ships with Informix is installed on the Informix server and the name of the demo database is "stores7." Using the Start button in the Windows taskbar, go to Programs/Informix-CLI 32 and select Informix Setnet 32.

Configure the Informix Setnet32 utility as follows:

Host Information:

  Current Host = ts_informix

  Username = informix

  Password = informix

After you enter the values, click the Apply button.

Server Information:

  Informix Server = ol_ts_informix

  Hostname = ts_informix

  Protocol = olsoctcp

  Service Name = turbo

After you enter the values, click the Apply button.

Environment:

  INFORMIXDIR=C:\PROGRAM FILES\INFORMIX

  INFORMIXSERVER=ol_ts_informix

  INFORMIXSQLHOSTS=\\TS_INFORMIX

After you enter the values, click the Set button.

Now you must create an ODBC data source using the ODBC Administrator in the Windows ODBC Control Panel applet.

Adding the ODBC data source

Follow these steps to add the ODBC data source to your system.

To add the ODBC data source to your system:

Run the ODBC administrator in Control Panel.
Select the System DSN tab and click the Add button.
From the list of installed drivers, select Informix-CLI 2.5 (32 bit).

Enter the following information in the ODBC INFORMIX 7.2 Driver Setup dialog box:

Data Source Name: Inf_ol7

Description: Demo Data

Database Name: stores7

Click the advanced button

Database List: 

Default User Name: informix

Host Name: ts_informix

Service Name: turbo

Server Name: ol_ts_informix

Protocol Type: olsoctcp

Yield Proc: 1 - None

Cursor Behavior: 0 - Close

Enable Scrollable Cursors: 0 - Disabled

Get DB List From Informix: 1 - Yes

Now you have an Informix ODBC data source. You can use this in a ColdFusion application. It is important to note that you must provide a username and password in the ColdFusion cfquery tag.

Verifying the Informix data source

After you configure the client software, verify the Inf_ol7 data source, as described in Installing and Configuring ColdFusion Server, to make sure it is configured properly. If verification fails, check the system environment variables.

To check the system environment variables:

Open the System Control Panel/system and click the Environment tab. In the System Variables dialog box, the variable called InformixDir should point to the Informix folder (for example, C:\program files\informix). If it does not exist, add an InformixDir variable.
There should also be a variable called Path, which should include the path to the Informix bin directory. If it does not, then modify the Path variable to include it.
After adding these variables, restart the system.

If you are having trouble accessing a data source, and the data source resides on a different machine, try running ColdFusion under an administrator account on the Web server.

Also, make sure that all ColdFusion services are running under a specific account ("This Account", in the Control Panel) instead of the default system account. By default, ColdFusion installs to run under the system account.

To change the Windows NT account that ColdFusion uses:

Select Start > Settings > Control Panel > Services > Cold Fusion Application Server > StartUp.
In the Log On As section, select This Account and browse to an administrator account. Enter username and password values.
Reenter the Password and Change Password values.
Stop and Restart the ColdFusion Application Server service.
Repeat steps 1 through 4 for the ColdFusion Executive and ColdFusion IDE services as well.

After you reconfigure the account under which ColdFusion runs, you can retry verification of the data source in the ColdFusion Administrator.

Advanced ColdFusion Administration
Advanced Data Source Management