Adding PostgreSQL connectivity to ColdFusion MX on a local Windows-based, Apache, server

• March 19, 2006
• James Skemp
• tutorials / guides

Having already added MySQL support for ColdFusion MX, it's time to look at adding PostgreSQL. For many of the same reasons we installed PostgreSQL with PHP, we'll be doing this to add to our potential resources. However, unlike our connection of MySQL with ColdFusion MX, we'll need to install some additional features to ColdFusion MX.

(This guide assumes you've already installed PostgreSQL.)

Downloading and installing the JDBC Driver

The first thing we'll need to do is install a special driver for ColdFusion. This driver can be found at http://jdbc.postgresql.org/. Select Download from the left-side navigation, and you'll be presented with a lot of options. Before we go any further, we'll need to determine what version we'll need to download, which will involve determining our version of JDK.

To determine this, find where you installed ColdFusion MX, and look in the runtime\jre\ folder. If you're not sure of where this is, make sure ColdFusion is started and open the ColdFusion Administrator (http://localhost/CFIDE/administrator/index.cfm if you've been following along).

Now, select Java and JVM under SERVER SETTINGS, on the left side. You'll now see a box detailing the Java Virtual Machine Path. Go to this location and open the README.txt file. Here, you'll be able to determine the version. You can also determine your version by checking the Server Information under ColdFusion Administrator. If you've been following along, you're probably using version 1.4.2. Because of this, and because we're using PostgreSQL 8.1, we'll download the JDBC 3, 8.1 Build 405 version (8.1-405 JDBC 3), which is about 400 KB. Save this to your downloads directory.

Now copy this file and move it to C:\CFusionMX\wwwroot\WEB-INF\classes, if you've installed ColdFusion to c:\CFusionMX\. Otherwise, substitute as necessary.

Now, enter the path and name of this file into the Class Path field in ColdFusion Administrator, with a comma at the end. For example, "C:/CFusionMX/wwwroot/WEB-INF/classes/postgresql-8.1-405.jdbc3.jar," (no quotes). Note that we've replaced the '\' with '/' in the path. Now Submit Changes.

Next, open the file at C:\CFusionMX\lib\neo-query.xml in Notepad. Do a search for postgresql, and scroll through until you find the following:

<var name='handler'><string>postgresql.cfm</string></var>

Change this to:

<var name='handler'><string>default.cfm</string></var>

Finally, restart ColdFusion via the Services panel. Note that it may take a little longer for ColdFusion to start, since we've added a new class for ColdFusion to load.

Finally, log back into ColdFusion Administrator and select Data Sources under DATA & SERVICES.

Creating a new database in PostgreSQL

The first thing we'll need to do is create a new database in PostgreSQL. Start pgAdmin and connect to the PostgreSQL Database Server 8.1.

Once you've connected, right click on Databases (1), and select New Database. Type in a Name (I used ColdFusion), select an Owner (we'll just use postgres for now), and select a Tablespace (pg_default). Now hit OK.

Adding PostgreSQL as a data source

We'll now add PostgreSQL as a data source in ColdFusion Administrator. In the Data Source Name, type in PostgreSQL, and select other for Driver. Now hit Add.

JDBC URL: jdbc:postgresql://localhost:5432/ColdFusion
Driver Class: org.postgresql.Driver
Driver Name: PostgreSQL
Username: postgres
Password: your_password (note, this will have been the second password you used, not the first)
Description: PostgreSQL database for ColdFusion MX 6.1. (or whatever you want to type)

Note that for JDBC URL, 'ColdFusion' should match the name of the database you created in PostreSQL above. Other than that and Username, your settings should match mine above. Once you do this, it should verify as OK. You can optionally Verify All Connections to verify them all.

Creating a PostgreSQL table in ColdFusion

We'll now create a new file in Notepad and paste the following text within it, to test our ColdFusion to PostgreSQL connection.

<cfquery name="CreateNewDatabase" datasource="PostgreSQL">
CREATE TABLE TestDatabase (
	TestColumn1 text,
	TestColumn2 text
);
CREATE UNIQUE INDEX TestDatabase_TestColumn1_TestColumn2_uidx ON TestDatabase (TestColumn1,TestColumn2);
</cfquery>
<cfoutput>If you see this, the table has been created</cfoutput>

Save and run this file (http://localhost/coldfusionmx6_1/CreatePostgreSQLTable.cfm), and you should see a notification that the table has been created.

If pgAdmin isn't still running, start it up and dig way down to find this table: PostgreSQL Database Server 8.1 > Databases > ColdFusion > Schemas > public > Tables > testdatabase. Whew.

Note that if you run this file again, you'll receive an error message, saying that the table already exists. Because we haven't added a check into this code to verify whether the table already exists, we'll hit a wall. Obviously, we'd have to add this code in in the real world.

Dropping/deleting our test table

Once you've verified that you can create a table, it's time to delete it. Add the following code to the end of the existing code, or create a new document with this code:

<cfquery name="DropNewDatabase" datasource="PostgreSQL">
	DROP TABLE TestDatabase
</cfquery>
<cfoutput>If you see this, the table has been dropped</cfoutput>

Run this code, and check pgAdmin to verify that the table no longer exists.

Again, note that if you run this code (just this last part), and the table already has been dropped, you'll get an error. Again, something that would have to be fixed in the real world.

Congratulations once again, as you've successfully setup ColdFusion and PostgreSQL.

As with MySQL, you can access any tables you setup in ColdFusion with PHP, and vice versa, so long as ColdFusion has the data source entered into ColdFusion Administrator.

View all of the steps to creating a local Web server, for development.

• postgresql
• coldfusion