add a datasource to cfm 11/Tomcat

OK. To answer your original question, yes it is possible to add a jdbc datasource to the tomcat container running in ColdFusion. I shall illustrate this by adding a PostgreSQL JDBC datasource. Be aware that what follows is experimental and, as far as I know, undocumented elsewhere. Make notes that will enable you, if necessary, to undo the procedure and return the system to its original state.

The steps are as follows:

1) Download and install the current version of the PostgreSQL database management system for your Operating System; ensure it is running. Jot down the password that you give the server.

2) Download the PostgreSQL JDBC41 driver, version 9.3-1102. This is the version that corresponds to Java 1.7 and Java 1.8, the Java Virtual Machine versions of ColdFusion 11. The download is a JAR file. Copy it to Tomcat's lib directory, located at {CF_HOME_DIRECTORY}\runtime\lib\.

3) You now have to add the driver details to the Tomcat configuration files, {CF_HOME_DIRECTORY}\runtime\conf\context.xml and {CF_HOME_DIRECTORY}\runtime\conf\web.xml. Make a back-up of these files before you proceed.

Open the file context.xml in a text editor, and add the following element just before the last end-tag </Context>:

<Resource name="jdbc/postgres" auth="Container"

          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"

          url="jdbc:postgresql://127.0.0.1:5432/booktown"

          username="cf_user" password="cf_password" maxActive="20" maxIdle="10" maxWait="-1"/>

Open the file web.xml in the text editor, and add the following element just before the last end-tag </web-app>:

<resource-ref>

<description>postgreSQL Datasource example</description>

<res-ref-name>jdbc/postgres</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

4) The Resource XML element points to the URL of the booktown database. I will now add this database and the corresponding user to the PostgreSQL server.

Download booktown.sql. Use it to install the booktown database on the command-line by running a PostgreSQL command like

psql -f /path/to/booktown.sql -U postgres template1

If that runs without a hitch, then the booktown sample database will have been installed. Initially the user is the default superuser, 'postgres'. Use it to log in on the command-line:

psql -U postgres

This will ask you to enter the password, the one you jotted down earlier. The command-line then begins with the prompt:

postgres=#

Now create another user, cf_user, also on the command-line, corresponding to the Resource XML element:

postgres=# CREATE ROLE cf_user PASSWORD 'cf_password' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

5) You are now done configuring. Restart ColdFusion.

To test, save the following code as a CFM file and run it.

<cfscript>

username="cf_user";

password="cf_password";

classObj = CreateObject("java", "java.lang.Class");

classObj.forName("org.postgresql.Driver");

driverManager = CreateObject("java", "java.sql.DriverManager");

connectionURL="jdbc:postgresql://127.0.0.1:5432/booktown";

conn=driverManager.getConnection(connectionURL,username,password);

myQuery="SELECT * FROM books";

preparedStatement = conn.prepareStatement(myQuery);

rs=preparedStatement.executeQuery();

while (rs.next()) {

writeoutput(rs.getString("title") & "<br>");

}

rs.close();

conn.close();

</cfscript>

To undo the above procedure, begin by stopping ColdFusion. Then delete the JAR file from Tomcat's lib direct, and restore the files context.xml and web.xml to their original state. Optionally, uninstall the PostgreSQL database management system.