Accessing ODBC data sources from ColdFusion

This article explains how to use ColdFusion with the Easysoft JDBC-ODBC Bridge. You can then access databases and files such as Microsoft SQL Server, Microsoft Access, Microsoft Excel, and ISAM from ColdFusion.

If you have not yet done so, install the JDBC-ODBC Bridge. For installation instructions, refer to the JDBC-ODBC Bridge documentation. Then follow these instructions to add EJOB.jar (the JDBC-ODBC Bridge client JDBC driver) to ColdFusion, so that it can be accessed by ColdFusion applications:

1. Open the ColdFusion Administrator in a browser:

   http://server_name/cfide/administrator/index.cfm

   where server_name is the name of the machine on which the ColdFusion Administrator is running.

2. Choose SERVER SETTINGS > Java and JVM.
3. In the Class Path box, enter the location where EJOB.jar is installed. For example, /usr/java/lib on UNIX and Linux or C:\Program Files\Java\Lib on Windows.
4. Choose Submit Changes and restart the ColdFusion server:

   To restart the ColdFusion server on UNIX or Linux:

   /opt/coldfusionmx/bin/coldfusion restart

   Note that you need to be the root user to do this.

   To restart the ColdFusion server on Windows, in Windows Services, right-click the ColdFusion service, and choose Restart.

5. After the server has restarted, choose DATA > SERVICES > Data Sources. In the Add New Data Source box:
   • Enter a name in the Data Source Name field.
   • Choose Other from the Driver drop-down list box.
   • Choose Add.
6. In the Data Source box, enter the following to add the data source:

   Setting	Value
   CF Data Source Name	Enter a name for the DSN. To use the sample application shown later in this tutorial, name the DSN Easysoft.
   JDBC URL	Enter a connection URL.
   Driver Class	easysoft.sql.jobDriver
   Driver Name	Easysoft SQL JOB driver

   You only need to supply a Username and Password if they are not included in your JDBC connection URL.

7. After you have completed the data source details, choose Submit.

   To test the data source, create a ColdFusion application that uses the Easysoft JDBC-ODBC Bridge to retrieve some data. The following ColdFusion examples use a JDBC-ODBC Bridge data source to retrieve and display data from the Suppliers table in the Microsoft Access Northwind database.

8. Create a ColdFusion page named easytest.cfm under the web_root or web_application_root directory, and add these lines:

   <cfscript>
           obj = createObject("component", "easytest");
           getRecords = obj.GetNorthwind  ();
   </cfscript>
   
   <p><b>Selecting records from the Suppliers table in
   Northwind via the Easysoft JDBC-ODBC Bridge</b><p>
   
   <table cellpadding="5" cellspacing="0" border="1">
   <tr>
        <th>SupplierID</th>
        <th>CompanyName</th>
        <th>ContactName</th>
        <th>ContactTitle</th>
        <th>Address</th>
        <th>City</th>
        <th>Region</th>
        <th>PostalCode</th>
        <th>Country</th>
        <th>Phone</th>
        <th>Fax</th>
        <th>HomePage</th>
   </tr>
   <cfoutput query="getRecords">
   <tr>
        <td>#getRecords.SupplierID#</td>
        <td>#getRecords.CompanyName#</td>
        <td>#getRecords.ContactName#</td>
        <td>#getRecords.ContactTitle#</td>
        <td>#getRecords.Address#</td>
        <td>#getRecords.City#</td>
        <td>#getRecords.Region#</td>
        <td>#getRecords.PostalCode#</td>
        <td>#getRecords.Country#</td>
        <td>#getRecords.Phone#</td>
        <td>#getRecords.Fax#</td>
        <td>#getRecords.HomePage#</td>
   </tr>
   </cfoutput>
   </table>

9. Create a ColdFusion component file named easytest.cfc in the same directory as easytest.cfm, and add these lines:

   <cfcomponent>
           <cffunction access="public" name="GetNorthwind"
                       output="false" returntype="query">
                   <cfset var artistsQuery="">
                   <!--- Create a data source named Easysoft that --->
                   <!--- points to a System ODBC DSN for the --->
                   <!--- Northwind Access database  --->
                   <cfquery name="EasysoftTest" datasource="Easysoft">
                           Select * from Suppliers
                   </cfquery>
                   <cfreturn EasysoftTest />
           </cffunction>
   </cfcomponent>