How to create a dsn-less database connection in Coldfusion

Coldfusion datasources are great. Having one single point where you can setup a named connection to a given database complete host, ports, username and passwords makes life so much easier than other languages. However what if you need to create a temporary connection to an outside database but you don't have access to the Coldfusion administrator?

Solution: Roll Our Own Connection

Since Coldfusion is built on java we know that it has a library of drivers which it uses to connect to different databases. All we need to do is jump down into java to access these drivers and create our own connection to the database outside of the CF admin.

Note: For this example I am going to create a JDBC connection to a demo MySQL5 Database on localhost.

Step 1: List Available Drivers

First lets check if our desired driver exists. While this is not strictly necessary if you know that your driver already exists in Coldfusion it doesn't hurt to know how to get the data. In addition this step will tell us what arguments we need to supply to the connection methods and in what order.

To do this we create an instance of coldfusion's service factory and dump the results of the getDrivers() method.

A quick look at the structure should tell you if your driver is available, plus the connection string format it expects.

Step 2: Load the Driver Using the Java Class Loader

In this example I am connection to a MySQL5 database, and according to the structure from the above cfdump the relevant driver is "com.mysql.jdbc.Driver". So to load this driver into memory we create a classloader object and call the forName() method specifying the driver.

Step 3: Connect to the Database Using the Java Driver Manager

Now that we have the database driver loaded we can create a connection to the target database using a "java.sql.drivermanager" object. We know from the getDrivers() cfdump that the correct connection string for the MySQL5 JDBC connection is in the format
"http://[host]:[port]/[dbname]?user=[username]&password=[password]".

Step 4: Run a Query

Now that we have established a connection to the database we can run a test query. It is worth noting that as we bypassed coldfusions datasources we have also bypassed the <cfquery> tag, so to run a query we need to build a sql string and pass it to the java "ExecuteQuery" method. This of course means that <cfqueryparam> will not work either.

Step 5: Convert to a Coldfusion Query Object

In addition to not being able to use <cfquery> and <cfqueryparam>, we must also convert the result set returned from the ExecuteQuery() method into a Coldfusion query object.

Step 6: Close the Connection

Finally we need to remember to close the connection to the database to free up resources.

Complete Code

So there we have it. A simple way to connect to a database from Coldfusion without creating a datasource. For reference the complete code is below, you will obviously need to put in error handling.