Oracle database design, development and administration
Data Management
Project Management
Cryptic crossword.
A bit about me.
Read my blog
Recent additions
Home

  • Setting up connectivity between CFMX and an Oracle database:

    Note: This section offers detailed steps in connecting your CFMX server to Oracle. An earlier article deals with Oracle connectivity for ColdFusion versions 5 and below.That article also offers tips on configuring Oracle Net client software. The latter may be helpful if you intend to use the Oracle OCI driver or ODBC sockets (options III and IV below)

    CFMX offers several options to connect to Oracle. I discuss these below, in increasing order of setup complexity. Note that Macromedia recommends use of their supplied Oracle driver (option I below). Failing that it is probably best to use the Oracle Thin driver (option II). ODBC (option IV) is always available as a fallback, but some advanced database features could be unavailable. Macromedia provides limited support for the OCI driver (option III), so this should be used only as a last resort.

    I. Default driver:

    Connecting your CFMX server to Oracle using the driver that ships with CFMX is a breeze. CFMX ships with an efficient type 4 JDBC driver for Oracle. What this means is that, in contrast to previous versions of CF, you no lon ger need to install Oracle Net client software on the CF server machine.

    The datasource setup is so easy that it barely merits discussion. However, I include a brief description here for completeness:

    1. Login to CFMX administrator and click on the data sources link on the left frame.

    2. Add your datasource name (any descriptive name) and select "Oracle" from
    the drop down list of drivers. Click "Add" to proceed.

    3. Add the Oracle SID (system identifier) and the port number on which the Oracle listener is configured to check for requests. The latter defaults to port 1521, which is prefilled for you. Check with your DBA for these settings.

    4. Fill in the network host name of the computer on which the database resides. Alternately you could use the IP address.

    5. If you want to test your connection, fill out a valid Oracle username and password.

    6. Click advanced and check the BLOB and CLOB boxes if you intend to use large objects (LOBs).

    7. Click "submit" to register your datasource. The administrator will display "OK" in the status column if connectivity to the datasource is verified. Else an error message will be displayed.

    II. Oracle thin driver:

    Oracle corporation supplies its own type 4 JDBC driver - the Oracle Thin driver. The driver is usually in <Oracle Home>\jdbc\lib\classes12.zip, where is your Oracle Home directory.

    You can configure a datasource for this driver as follows:

    1. Register the location of the Oracle driver file in the CFMX JVM classpath. To do this, click on "Java and JVM" in the CFMX administrator and enter the path to the Oracle driver file. Click on "submit changes" to register your entry. You will need to restart the CFMX service after this is done.

    2. Login again to your CFMX administrator and click on "Data Sources". Add a descriptive datasource name and select "Other" from the driver pull down list. Click add to proceed.

    3. Enter the JDBC url like so: "jdbc:oracle:thin:@hostname:port:sid", where "hostname" is the network name (or IP address) of the database machine, "port" the port number (default 1521) on which the Oracle listener is set up and "sid" the Oracle system identifier. Avoid using dots to qualify your sid.

    4. Enter the driver class as: "oracle.jdbc.OracleDriver".

    5. Enter a driver name (any descriptive name) and a valid Oracle username and password (to test the connection). Click submit to register and test the connection.

    III. Oracle OCI driver:

    If you choose to use the Oracle OCI driver (a type 2 JDBC driver), you need to do considerably more. Here are the steps:

    1. Install and test Oracle Net client software on the CF server machine. Help on how to do this is available here .

    2. Register the location of the driver (<Oracle Home>\jdbc\lib\classes12.zip), where is your Oracle Home directory) with the CFMX JVM classpath. See step 1 in section II above.

    3. Login to your CFMX administrator and click on "Data Sources". Add a descriptive datasource name and select "Other" from the driver pull down list. Click add to proceed.

    4. Enter the jdbc url for the OCI driver: "jdbc:oracle:oci:@database", where "database" is the Oracle Net service name for the database instance.

    5.Enter the driver class as: "oracle.jdbc.OracleDriver".

    6. Enter a descriptive display name for the driver, and a valid Oracle username and password if you want to test the connection. Click "submit" to register and test the driver.

    IV. ODBC driver:

    To set up connectivity using ODBC, you need to do the following.

    1. Install and test Oracle Net client software on the CF server machine. Help on how to do this is available here .

    2. Set up an ODBC datasource for your Oracle database using the Windows ODBC administrator. Basic steps on how to do this are described here .

    3. Login to your CFMX administrator and click on "Data Sources". Add a descriptive datasource name and select "ODBC Socket" from the driver pull down list. Click add to proceed.

    4. Select the datasource from the "ODBC DSN" drop down list.

    5. Click on "Advanced Settings",and then add a valid Oracle username and password if you want to test the connection.

    6. Click "submit" to register and test the driver.


Back to the top