If you need to connect to an Oracle database from Coldfusion 10 the following are some recommendations on how to configure your connection.
1. What you need to know before setting up a DSN (Data Source Name) in your Coldfusion 10 environment:
- The Database OID entry. You can get this from your current DSN if you're already using Coldfusion, or contact the DBA group (dba-support@cornell.edu)
- Database credentials (username and password)
- Version of Oracle database (this helps with troubleshooting or providing support)
- If NOT using OID, then the server that your database lives on
2. CIT Hosting recommends customers use the OID connection string and the Oracle JDBC drivers with Coldfusion 10
- This differs from Coldfusion 9 due to the fact that Adobe does not officially support Oracle 12g database connections with their delivered Oracle drivers.
- Our initial testing does show the delivered Oracle drivers can work with Oracle 12g databases, however we have not fully exercised the drivers nor can we provide support if issues are found when connecting to a 12g database with the Adobe delivered drivers.
- Customers will need to download the Oracle driver for oracle.com (file name ojdbc7.jar for Java 1.7 support). We would recommend the ojdbc7.jar file for Oracle 12g databases since all hosted Oracle databases will be upgraded from 11.x to 12 in the coming months. Please contact dba-support@cornell.edu for more information on when you Oracle database will be upgraded.
3. Using Oracle JDBC with OID
- To install an Oracle JDBC driver (i.e. Oracle 12g), you'll need to upload a "jar" file that contains the updated driver to your hosting environment. This can be done by downloading the driver from Oracle's website (odbc7.jar for Java 1.7) and uploading the file into your "jars" directory (next to your htdocs directory) via your webdav client. Once the file is uploaded you'll need to restart your Coldfusion instance using the Hosting Dashboard
- When creating your DSN for the Oracle JDBC driver you will need to specify the "Other" Data Source when configuring Coldfusion via CFADMIN
- In CFADMIN, go to Data & Services then click on Data Sources
- You do not need to specify more than one OID server, ldap://oid.cit.cornell.edu is a loadbalanced service and will handle the failover for those databases configured in a RAC environment.
2. Using Macromedia with OID
If you choose to use the built-in Oracle driver you will need to use the Oid Lookup tool to retrieve the servername of the backend database. Please note that if the backend database moves you will need to update your connection information!
3. Using Macromedia with JDBC connection string - to allow failover if your databases are in a RAC configuration
If you have your databases in a RAC setup with CIT DBA's and OID is not working for you, here is an example of a jdbc connection string to use. You'll need to use the above OID lookup tool to find the hostnames and service name.
jdbc:macromedia:oracle://host1-vip.serverfarm.cornell.edu:1521;SERVICENAME=mydb11g.cit.cornell.edu;sendStringParametersAsUnicode=false;MaxPooledStatements=1000;AlternateServers=(host1-vip.serverfarm.cornell.edu,host2-vip.serverfarm.cornell.edu)
To enter the above, in CFAdmin, Create a new DSN and you'll need to choose "Other" in the DSN Driver. In the new window, enter your DSN name, the jdbc URL/connection string (however it looks from above), Driver Class should be "macromedia.jdbc.MacromediaDriver", Driver Name can be whatever you choose, and then the appropriate username and password to access.
Installing updated Oracle JDBC Drivers
If you need to install an updated Oracle JDBC driver (i.e. Oracle 12g), you'll need to upload a "jar" file that contains the updated driver to your hosting environment.
This can be done by downloading the driver from Oracle's website (odbc6.jar for Java 1.6 or odbc7.jar for Java 1.7) and uploading the file into your "jars" directory (next to your htdocs directory). Once the file is uploaded you'll need to restart your Coldfusion instance using the Hosting Dashboard.