3 Replies Latest reply on Jan 28, 2015 12:19 PM by BKBK

    add a datasource to cfm 11/Tomcat

    demarcao Level 1

      Is it possible to add a jdbc datasource to the tomcat container running a cf 11 instance?  If so, is the any documentation on how to do this?

      thanks in advance..

        • 1. Re: add a datasource to cfm 11/Tomcat
          BKBK Adobe Community Professional & MVP

          You could just add the datasource in the usual manner. That is, via the datasource section in the Administrator page of the particular instance. Or am I missing something?

          • 2. Re: add a datasource to cfm 11/Tomcat
            demarcao Level 1

            We have a 3rd party jar that needs a datasource loaded at the tomcat container level it can't see CF datasources...

            • 3. Re: add a datasource to cfm 11/Tomcat
              BKBK Adobe Community Professional & MVP

              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.