# Connecting to Informix data sources

Connections to Informix databases are supported through ODBC on both Windows and UNIX operating systems. On Windows, ColdFusion uses native drivers and on UNIX, ColdFusion uses both native and ODBC drivers to connect to Informix databases. ColdFusion 4.5 supports Informix 7.3 and higher.

## Configuring Informix Options (Windows)

If ColdFusion Server Enterprise edition is installed on a Windows NT server, you can configure Informix ColdFusion data sources using a native driver.

| Native Driver: Informix 7.3 (Windows)

The following table describes ColdFusion options for the DB2 Universal Database 5.2/6.1 native driver. You set these options when you configure a ColdFusion data source. See "Adding Data Sources for ColdFusion" for more information about adding data sources to ColdFusion.

Informix Native Driver Options
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.

## Configuring Informix Options (UNIX)

If ColdFusion Server is installed on a UNIX server, you can configure Informix ColdFusion data sources using either native or ODBC drivers.

| Native Driver: Informix 7.3 (UNIX)

ColdFusion native drivers are the same for both Windows NT and UNIX. To see ColdFusion options for the DB2 Universal Database 5.2/6.1 native driver, see the table in "Native Driver: Informix 7.3 (Windows)".

| ODBC: Informix 7.x/9.x (UNIX)

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. See "Adding Data Sources for ColdFusion" for more information about adding data sources to ColdFusion.

MERANT Informix ODBC Options
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.
Server Host Name | The name of the machine on which the INFORMIX server resides.
Use INFORMIX registry for Logon ID and Password -- Determines whether the drive reads the Logon ID and Password directly from the INFORMIX registry.
Cursors | Preserve cursors at the end of each transaction -- Determines whether cursors will be preserved or closed at the end of each transaction. Enable this attribute if you want cursors to be held at the current position when the transaction ends. Enabling this option may impact the performance of your database operations.
Enable scrollable cursors -- Determines whether the driver provides scrollable cursors. The INFORMIX driver can use scrollable cursors only if there are no long columns (SQL_LONGVARCHAR or SQL_LONGVARBINARY) in a Select list. If you enable this option, you must not include long columns in the Select list.
Enable Insert cursors -- Determines whether the driver can use Insert cursors during parametrized inserts.

## Connecting to Informix Data Sources (UNIX)

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

1. Install the Informix ESQL.C9.13 client software.
2. Edit the following files: ColdFusion start script, SQLHOSTS, master NIS services, and $INFORMIXDIR/etc/onconfig.
3. Stop and restart ColdFusion Server.

### Installing the Informix ESQL.C9.13 Client Software

The Informix ESQL/C9.13 version client software does not ship with ColdFusion, but you can download it from the Informix Web site:

    http://
    www.intraware.com/guest/products/product.html?plne=000111

You can also download it from the Allaire FTP site:

ftp://www.allaire.com/incoming/CSDK.TAR.Z

You will need to uncompress, and untar this file into a separate subdirectory on your server. For example: `/opt/isdk`. This is the directory you will point to in the start script as INFORMIXDIR

Run the script `installclientsdk to` install the client SDK.

Before you continue, make sure 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

Edit `coldfusion/bin/start` script so that it contains the following lines:

    # 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

Edit the sqlhosts file so that it contains the following lines:

    dbserver nettype hostname service name
    alldev onipcshm alldev online0
    alldevtli ontlitcp alldev turbo

In these three lines:

- dbserver 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 is the hostname of the server where the database is, you can put the ip address or hostname
- service name is the entry in the `/etc/services` or master NIS services 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 services file

Edit your `/etc/services` or master NIS services 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

When you've completed all the steps in this section, you will need to 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 both native driver connectivity as well as ODBC.

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

| To install the client software:

1. Connect to the machine that is hosting the Informix software. For example, on Windows, `\\machine1\infshare\informix\Informix_ODS_722`.
2. Run the `setup.exe` and click Next.
3. Choose Custom
4. Choose the Client connectivity: I-Connect 7.20, CLI 2.50.

### Modifying the services file entry

After the installation is complete you will need to 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 our network. Make the following entry at the bottom of the file:

    turbo 1526/tcp

Note | Check with your system administrator for the name of the service.

### Configuring Informix SETNET32 settings

After you've installed the client software you must configure your workstation to connect to the Informix databases. Let's assume 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 typing in the values, click the Apply button.

- Server Information:

  Informix Server = ol_ts_informix

  Hostname = ts_informix

  Protocol = olsoctcp

  Service Name = turbo

  After typing in the values, click the Apply button.

- Environment

  INFORMIXDIR=C:\PROGRAM FILES\INFORMIX

  INFORMIXSERVER=ol_ts_informix

  INFORMIXSQLHOSTS=\\TS_INFORMIX

  After typing in the values, click the Set button. Then you'll have to 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:

1. Run the ODBC administrator in Control Panel.
2. Select the System DSN tab and click the add button.
3. From the list of installed drivers, choose Informix-CLI 2.5 (32 bit).
4. Fill out the ODBC INFORMIX 7.2 Driver Setup Dialog box as follows:

       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

Once the client software is configured, verify the Inf_ol7 data source as described in "Verifying ColdFusion Data Sources" to make sure it is configured properly. If verification fails, check the system environment variables.

| To check the system environment variables:

1. Open the System Control Panel/system and click the Environment tab. In the System Variables box, there should be a variable called "InformixDir," which should point to the Informix folder (for example, `c:\program files\informix`). If it does not exist, add one.

   There should also be a variable called "Path" which should include the path to the Informix bin directory. If not then modify the Path variable to include it.

2. 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, you may want to try having ColdFusion run under an administrator account on the Web server.

Also, make sure that all ColdFusion services are running under a specific account instead of the default system account. By default, ColdFusion will install to run under the system account.

| To change the Windows NT account ColdFusion uses:

1. Using the Services Control Panel, highlight the Allaire ColdFusion 4.0 service and click Startup. Make sure to choose a user name from the user list rather then typing it.
2. Reenter the Password and Change Password values.
3. Stop and Restart the Allaire ColdFusion 4.0 service.
4. Repeat this process for the ColdFusion Executive and ColdFusion IDE services as well.

Once you've reconfigured the account under which ColdFusion runs, you can retry verification of the data source in the ColdFusion Administrator.