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.

Note 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.

Note 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)".

Note 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:

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.

Note 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:

Adding the ODBC data source

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

Note 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.

Note 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.

Note 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.