Setting up an Oracle Thin Client Data Source in ColdFusion

To set up an Oracle thin client data source in ColdFusion 10 on a Windows environment, do the following:

  • Browse to http://www.oracle.com/technetwork/topics/winx64soft-089540.html
    (Note: You will need to create an Oracle account to download if you don’t have one)
  • Download the Oracle Instant Client, i.e. instantclient-basic-win-x86-64-10.2.0.2.0-20060503 from the Oracle website
  • Unzip and copy ojdbc14.jar to C:\ColdFusion10\<instance-name>\wwwroot\WEB-INF\lib, e.g. C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib
  • Add an entry to the Path system variables
  • Access the System Properties, i.e. Computer > right click and select Properties
  • Click Advanced system settings
  • Click Advanced tab
  • Click Environment variables…
  • Select path under System variables
  • Click Edit…
  • Append C:\ColdFusion10 to Path using the semicolon as a separator, i.e. add this:
    ;C:\ColdFusion10
  • Restart the server (needed when the Path system variables is updated so applications will pick it up)

In the image above, the path to append to the Path System Variable should be:
C:\ColdFusion10

Once the driver is installed and the server rebooted, do the following to create the Oracle thin client data source in ColdFusion 10:

  • Launch the ColdFusion admin via a web browser
  • Click Data Sources under Data & Services section
  • In the Add New Data Source section, enter the data source name and choose Other for the driver selection
  • Click Add
  • Then enter the following details for this new Oracle thin client data source:
    • JDBC URL
    • Driver Class: oracle.jdbc.OracleDriver
    • Driver Name (can be same as driver class)
    • Username (if required)
    • Password (if required)
    • Description (if required)
  • Click Show Advanced Settings
  • Make changes as required, e.g. select the Allowed SQL, set Login Timeout, etc.
  • Click Submit to create the data source
  • If there are no errors, a mesage is displayed to indicate that the data source was successfully created

data sources

Specify the JDBC URL using this format: jdbc:oracle:thin:@host-name:port:sid

If you do not install the Oracle thin client driver first and try to create a new Oracle thin client data source in ColdFusion, then verifying this new data source will result in this error:

Connection verification failed for data source: <data source name>
java.sql.SQLException: No suitable driver available for <data source name>, please check the driver setting in the resources file, error: oracle.jdbc.OracleDriver.

The root cause was that: java.sql.SQLException: No suitable driver available for <data source name>, please check the driver setting in the resources file, error: oracle.jdbc.OracleDriver.

 

 

Advertisements

ColdFusion 11: Connection verification failed for data source

After upgrading to ColdFusion 11, verifying a data source in ColdFusion Admin (especially those where you need to specify the instance name as part of the server, i.e. Server: server-name/instance-name) will result in the following error thrown:

Connection verification failed for data source: <data-source-name>
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Conflicting connection information. When the instance name is specified, it is invalid to specify the port number.

The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Conflicting connection information. When the instance name is specified, it is invalid to specify the port number.

Follow these steps to resolve the above error:

  1. Edit the data source
  2. Expand Show Advanced Settings
  3. In the Connection String box, enter: allowPortWithNamedInstance=true
  4. Click Submit
  5. You should now see the message: data source updated successfully

Repeat the above steps for all data sources with the same entry for its Server, i.e. those with values of server-name/instance-name.