Installing and Configuring ColdFusion Server
Managing Data Sources

Connecting to Oracle Databases

You can configure Oracle ColdFusion data sources for Windows NT and UNIX using ODBC and native drivers.

Configuring Oracle 7.3.4/8.0.x options (Windows)

If you install ColdFusion Server Enterprise Edition on a Windows NT server, you can configure an Oracle data source using a native driver.

Native drivers: Oracle 7.3.4 and 8.0.x options (Windows)

The following table describes ColdFusion native driver options for Oracle 7.3.4 and 8.0.x native driver data sources on Windows NT.

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Host String	Enter the database alias that you created using the Oracle Net8 Easy Config utility. To find the database alias for the database that you want to connect to, you can use the Oracle Net8 Easy Config utility.

Configuring Oracle 7.3.4/8.0.x options (UNIX)

If you install ColdFusion Server Enterprise Edition on a Solaris server, you can configure an Oracle data source using a native driver.

Native drivers: Oracle 7.3.4 and 8.0.x options (UNIX)

The following table describes ColdFusion native driver options for Oracle 7.3.4 and 8.0.x native driver data sources on UNIX:

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Host String	Enter the database alias that you created using the Oracle Net8 Easy Config utility. To find the database alias for the database that you want to connect to, you can use the Oracle Net8 Easy Config utility.

ODBC drivers: MERANT Oracle 7.3.4 and Oracle 8.0.x options (UNIX)

The following table describes ColdFusion ODBC driver options for the MERANT Oracle 7.3.4 and MERANT Oracle 8.0.x ODBC drivers on UNIX:

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Connect String	The client connection string designating the server and database that you want to access.

Connecting to Oracle 7.3.4 and 8.0.x databases (UNIX)

Before ColdFusion applications can connect to Oracle 7.3.4 and 8.0.x databases, you must install the Oracle client software.

You need to modify the /opt/coldfusion/bin/start script to include the ORACLE_HOME environment variable and add the $ORACLE_HOME/lib directory to the LD_LIBRARY_PATH. For an example, see "The /opt/coldfusion/bin/start script" on page 132. You must edit the $CFHOME/bin/start script for all versions. The environment variable LD_LIBRARY_PATH must point to the directory containing the libclntsh.so file, which ColdFusion uses.

If the libclntsh.so file was built in the $CFHOME/lib using a script that you downloaded from http://www.coldfusion.com, then the LD_LIBRARY_PATH variable should contain an entry for $CFHOME/lib prior to the entry for $ORACLE_HOME/lib, for example:

LD_LIBRARY_PATH = $CFHOME/lib:$ORACLE_HOME/lib:other_database_library_paths...

If the libclntsh.so file is in $ORACLE_HOME/lib, then you need just the single entry for this directory, for example:

LD_LIBRARY_PATH = $ORACLE_HOME/lib:other_database_library_paths...

Verify the following:

• You can connect to the Oracle server from Solaris with sql*plus before you try to connect from ColdFusion.
• You are running the Oracle listener.
• You have set up a tnsnames.ora file. For Oracle 7.3.4, it is in the /var/opt/oracle directory.

Example

This is an example of a tnsnames.ora file:

scup=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=scup)

      (PORT=1521))

    (CONNECT_DATA=(SID=WG73)))

The following table describes the code:

Code	Description
scup=	Setting for the connect string name in the ColdFusion Administrator.
HOST=scup	The IP address, as in HOST=205.185.22.33.
PORT=1521	Port that the tcp listener is assigned to. You can look at the /etc/services file of the UNIX machine where the Oracle server resides to identify it.
SID=WG73	Database identifier. See your Oracle administrator. These settings are configured when you install the database.

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

The /opt/coldfusion/bin/start script

#!/bin/sh

# start - setup environment and run ColdFusion servers

# This script should be run as root.# Set during install

CFHOME=/opt/coldfusion

# Sybase Open Client directory

SYBASE=/work/sybclient11.1

# Oracle SQL *Net RDBMS directory

# See CFHOME/odbc/src/oracle for script to build library

ORACLE_HOME=/opt/oracle7

# Set library search path

#

# NOTE: Add your database client library directory to the FRONT of this list

#

# Example: 

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

# This is the list of variables that ColdFusion will see

# Add any special Database environment varaibles here

VAR_LIST="LD_LIBRARY_PATH CFHOME SYBASE ORACLE_HOME INFORMIXDIR INFORMIXSERVER II_SYSTEM"

#==========================================================

# You should not need to touch anything below this point

#==========================================================

The environment variables CFHOME and ORACLE_HOME are assumed to point to the ColdFusion installation directory (usually /opt/coldfusion) and the Oracle Client Software installation directory (for example, /opt/oracle8).

Use the genclntsh script provided by Oracle in the $ORACLE_HOME/rdbms/lib directory and follow the Oracle instructions to build the libclntsh.so library in the $ORACLE_HOME/lib directory.

Oracle troubleshooting

If ColdFusion cannot establish a connection to the Oracle 7.3.4 driver, try the following workarounds:

• Move the location of the ORACLE_HOME/lib to the end of the LD_LIBRARY_PATH in the start script.
• Make sure that the listener port number is the same one that you are pointing to in the tnsnames.ora file.
• Check in the $ORACLE_HOME/lib directory for the libclntsh.so file. If it does not exist, you must reinstall the Oracle client software.

If the basic information that you entered in the Oracle Net8 Easy Config is correct, but the connection test fails, then seek assistance from your Oracle 8 database administrator (DBA). You should first check the basic connection information: hostname, SID, username, and password. You can do this using the Net8 Easy Config utility or by directly inspecting the tnsnames.ora file.

In addition, on UNIX, make sure that you defined the Oracle client library and ORACLE_HOME in the coldfusion/bin/start script.

Connecting to Oracle 8.0.x through ODBC (UNIX)

To build a version of libclntsh.so for Oracle 8.0.4 and 8.0.5:

1. From the shell, set ORACLE_HOME, CFHOME, and LD_LIBRARY_PATH as set in the start script:
   • Set ORACLE_HOME to the location of the Oracle client libraries.
   • Set CFHOME to the location of your ColdFusion application server.
   • Set LD_LIBRARY_PATH the same as in your start script.

     For example:

     CFHOME=/opt/coldfusion;export CFHOME

     ORACLE_HOME=/opt/oracle8;export ORACLE_HOME

     LD_LIBRARY_PATH=/lib:/usr/dt/lib:/usr/openwin/lib:$CFHOME/lib:$ORACLE_HOME/lib;export LD_LIBRARY_PATH

2. From $CFHOME/odbc/src/oracle run the script./genclntsh8.

   The script generates a new libclntsh.so in CFHOME; move it to $CFHome/lib.

   
   

   Note Oracle 8 users have the original libclntsh.so library in the $ORACLE_HOME/lib directory. Therefore when creating the LD_LIBRAY_PATH in the $CFHOME/bin/start script, the $CFHOME/lib directory, containing the correct library, must be on the LD_LIBRARY_PATH before $ORACLE_HOME/lib. Otherwise, the original Oracle version loads, resulting in an error.

   
   

Troubleshooting the Oracle 7.3.4 native driver (Windows)

When using the Oracle 7.3.4 native driver on Win32 systems, you might get the following error:

Error Diagnostic Information 

Oracle Error Code = 0 

Internal error: The data access manager failed to initialize the Oracle environment. 

The error occurred while processing an element with a general identifier of (cfquery), occupying document position (3:1) to (3:50). 

This problem might occur with the Oracle 7.3.4 native driver in several situations:

• There is a duplicate copy of the OCIW32.DLL file (most common cause).
• You cannot verify the connection.
• You are running a cfquery.

To diagnose and fix the problem:

1. Make sure that SQL*Net 7.3.4.0 or later is installed on your server.
2. Make sure that your winnt\system32 directory contains the file ociw32.dll.
   • If you have a copy in winnt\system32, rename it, then stop and start the ColdFusion services.
   • If you do not have a copy in winnt\system32, search your hard drive for the file. If you find a copy anywhere other than orant\bin or cfusion\bin, rename the file, then restart ColdFusion services.

Configuring the Oracle 8 Client (Windows, UNIX)

This section includes the typical configuration steps that you need to use the Oracle 8 native database driver on UNIX or Windows NT. The following procedures were written using version 8.0.4.0.0 of the Oracle 8 Client.

Before you get started, make sure you have the following information handy:

• The name of the host system where the Oracle database resides
• The System Identifier (SID) for your Oracle 8 database
• A login ID and password for connecting to the Oracle 8 database

To use the ColdFusion native driver for Oracle 8:

1. Install the required client software.
2. Use the SQL Net Easy Configuration utility to create a database alias.

   You can find the UNIX version of this utility in $ORACLE_HOME/bin/net8wiz.sh.

3. Create the data source in the ColdFusion Administrator, Native Drivers page.
4. Edit the coldfusion/bin/start script to include the following values:
   • The database variable ORACLE_HOME
   • The library path set in LD_LIBRARY_PATH

To install the Oracle 8 Client:

1. Install the Oracle 8 Client software.
2. Select the Database Administrator or Application User option. This example uses Application User.
3. Step through options involving stopping Oracle services that are running on your system, and choose whether to install online documentation.

Running the Oracle Net8 Easy Config utility

The following procedure creates a database alias that you use to reference the Oracle database when creating the data source in the ColdFusion Administrator. When you create the database alias, the process writes all of the database connection information to a configuration file called tnsnames.ora.

To run the Oracle Net8 Easy Config utility:

1. Open the Oracle Net8 Easy Config utility.

   On servers running Windows NT, the icon is found in your Oracle for Windows NT program group. On UNIX, this utility is found in $ORACLE_HOME/bin/net8wiz.sh.

2. Enter a New Service Name and click Next.

   

3. In the resulting dialog box, select TCP/IP as the networking protocol to connect with and select the Oracle 8 database that you want to use in your ColdFusion application.
4. In the dialog box that displays, enter the host name of the server where the Oracle 8 database resides. The following figure shows the default for the port number:

   

5. After entering a host name, enter the Database SID, which identifies your specific Oracle database instance.

   The default is ORCL, but your database SID might be different. For this information, see your DBA.

6. In the next dialog box, test the database service that you created.

   To test the connection to the Oracle database, enter a valid username and password for accessing the Oracle database. If you do not have this information, see your DBA.

Creating the data source in ColdFusion

Now you need to create the data source in ColdFusion.

To create the data source:

1. Open the ColdFusion Administrator to the Data Sources, Native Drivers page.
2. Enter a data source name and select the Oracle 8 native driver from the drop-down list.
3. When you click Add, ColdFusion opens the configuration page for the data source. Here you enter information that tells ColdFusion where to find the database. The following options are the most important ones for a successful connection:
   • Host string  Enter the exact database alias that you created using the Oracle Net8 Easy Config utility.
   • ColdFusion Login username and password  These options display when you click the CF Settings button. The username and password are the same as those used in the Oracle Net8 Easy Config connection test. If you do not know what the username and password should be, see your Oracle 8 DBA.

     You might want to avoid setting a username and password here for security reasons. If you do not set a name and password, each CFML database call must specifically define a username and password.

4. After you create the data source, open the Verify Data Source page in the Administrator to verify that ColdFusion can connect to the Oracle 8 database.

Copyright © 2001, Macromedia Inc. All rights reserved.

---