dx4.org : Using Oracle OID with JDBC on ColdFusion

17/Feb/2009 12:01

Using Oracle OID with JDBC on ColdFusion by Elliot

If like me you're unfortunate enough to have to work with Oracle and ColdFusion, you probably get sick of reconfiguring your data sources every time the DBAs move a database to a new host. It's the same problem that Oracle used to have with tnsnames.ora, and which they addressed with ldap.ora and OID. Wouldn't it be great if you could do the same thing with ColdFusion? Turns out you can.

First, you need to make sure that your ColdFusion install is set up to use the JDBC libraries that come with the Oracle client. In particular, you need a file called ojdbc14.jar - classes12.zip will not work for reasons I'll explain later. Install the whole Oracle client if you like, or just get a copy of ojdbc14.jar from somewhere. Then modify java.class.path in your bin/jvm.config file to include the full path to the ojdbc14.jar file. For example:

java.class.path={application.home}/servers/lib,{application.home}/servers/lib,{application.home}/lib,{application.home}/extralibs/ojdbc14.jar

Note that this setting applies to all servers associated with a particular CF install. If you want to test this away from your production stuff, you'll need to use a whole different install.

Restart your CF servers. In the administrative web interface, go to Server Settings -> Settings Summary. Under JVM Details, find Java Class Path and verify that the full path to ojdbc14.jar is listed there.

Now you're ready to define a data source. Go to Data & Services -> Data Sources. Under Add New Data Source, enter a name, select Other for the driver, and press Add. For JDBC URL, enter the following, substituting your own values for the italic sections.

jdbc:oracle:thin:@ldap://your-name-server.your-company.com/oracle-sid,cn=OracleContext,dc=your-company,dc=com

If you have multiple OID servers, you can repeat the ldap://... section, seperating entries with spaces. For Driver Class, enter oracle.jdbc.driver.OracleDriver. Driver Name can be anything you like, and enter User Name, Password, and Description normally.

Note that the JDBC libraries (or the Java LDAP libraries, or whatever) do their LDAP lookup in an unusual way. Normally you set a search base telling what part of the tree you're searching, a filter telling how to limit the results you get back, and an attribute list telling what kinds of attributes you want returned. For an OID directory, and using OpenLDAP's ldapsearch client, it looks like this:

ldapsearch -h your-name-server.your-company.com -x -b "dc=your-company,dc=com" cn=oracle-sid orclnetdescstring

That works fine, but it's not what CF/JDBC/whatever does. It prepends "cn=" to whatever you give it and sets that as the base, and gives a filter that matches anything (objectclass=*). In ldapsearch terms:

ldapsearch -h your-name-server.your-company.com -x -b "cn=oracle-sid,dc=your-company,dc=com" orclnetdescstring

Also, the reason why the JDBC libraries in classes12.zip won't work is that they ask for the wrong attribute - ora-desc-string instead of orclnetdescstring. I imagine that's a legacy name from an old version of OID, but I couldn't find any information about it. Part of the purpose of writing this article is to make there be a hit on Google for the term.