Access ODBC Databases from Macromedia ColdFusion MX by using the Easysoft JDBC-ODBC Bridge

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

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

  1. Open the ColdFusion MX Administrator in a browser:
    http://server_name/cfide/administrator/index.cfm
    

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

  2. Choose the SERVER SETTINGS > Java and JVM menu option. In the Class Path field, specify the location where EJOB.jar is installed on your server. For example, /usr/java/lib on Unix or C:\Program Files\Java\Lib on Windows.
  3. Click Submit Changes and restart the ColdFusion MX server:

    To restart the ColdFusion MX server on Unix:

    /opt/coldfusionmx/bin/coldfusion restart
    

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

    To restart the ColdFusion MX server on Windows, in Control Panel > Administrative Tools > Services, right-click the ColdFusion MX service and click Restart.

  4. After the server has restarted, choose the DATA & SERVICES & Data Sources menu option. In the Add New Data Source box:
    • Type a name in the Data Source Name field.
    • Select Other from the Driver drop-down list box.
    • Click Add.
  5. In the Data Source box, type the following to add the data source:
    Setting Value
    CF Data Source Name Type a name for the DSN. To use the sample application shown later in this tutorial, name the DSN Easysoft.
    JDBC URL Type a connection URL.
    Driver Class easysoft.sql.jobDriver
    Driver Name Easysoft SQL JOB driver

    You will only need to type a Username and Password if they are not included within your JDBC connection URL.

  6. After you have completed the data source details, click 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 JOB data source to retrieve and display data from the Suppliers table in the Microsoft Access Northwind database.

  7. 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>
    
  8. 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>
    

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)