Loading Large Dataset using datatables with AJAX

OK, so we manage to retrieve records from Active Directory from SQL Server using Linked Server, and the process is now automated as detailed in a previous article.

Now let’s take that data and display them in a simple listing with page pagination and search/filter capability. To achieve this we will make use of a JavaScript library called datatables. And to ensure great loading time (i.e. no page freeze for large dataset of 5000 or more retrieved records), we will make of the AJAX and deferRenders options in datatables.

Retrieving records from the database

Create a new ColdFusion component called staff.cfc and add the following code for retrieving staff records from a database table. The records from this table were populated from an Active Directory query via SQL Server. A sample partial code snippet to illustrate


<cffunction name="getStaff" access="remote" returnformat="json">
   <cfscript>
      var qStaff = "";
      var sStaff = {"data" : []};
      var jsStaff ="";
      var rowNum = 0;
   </cfscript>

   <cfquery name="qStaff" datasource="<your-datasource-name>">
      SELECT givenName,sn,mail
      FROM staff
   </cfquery>

   <cfscript>
      if (qGet.RecordCount gt 0){
         for (staff in qStaff){
            rowNum++;
            sStaff.data[rowNum] = {};                       
            StructInsert(sStaff.data[rowNum],"givenName",staff.givenName);
            StructInsert(sStaff.data[rowNum],"sn",staff.sn);
            StructInsert(sStaff.data[rowNum],"mail",staff.mail);  
         } //for
         jsStaff = serializeJSON(sStaff);
      }  //if
   </cfscript>

   <cfreturn jsStaff>
</cffunction>

Important notes

  • This function (method) must have this attribute:  returnformat=json
  • Convert the returned query records into a struct in the format required by datatables, i.e. {“data” : []}
  • Finally serialise this struct into JSON before returning it using ColdFusion function serializeJSON

Displaying the records on the front-end

On the front-end, let’s use datatables with its deferRender and AJAX options to load and display the records from the database table. Here’s a partial sample code to illustrate.


<table id="staff" class="table table-striped table-bordered table-hover" width="100%">
   <thead>
      <tr>
         <td>First Name</td>
         <td>Last Name</td>
         <td>Email</td>         
      </tr>
   </thead>
</table>

<script>
   $(document).ready(function() {
      $('#staff').dataTable({
         'deferRender': true,
         'ajax': {
            'url': 'staff.cfc?method=getStaff'
         },
         'columns': [            
            { 'data': 'givenName' },
            { 'data': 'sn' },
            { 'data': 'mail' }
         ]
      }); //dataTable
   });
</script>

Important notes

  • For the table, we only need to define the table header when using the AJAX option
  • We use datables’ columns option to match the table columns to the ajax returned record. Order is important.
  • For the datatables option set deferRender=true to defer rending for large dataset and hence improve loading time
  • Datatables’ ajax option is use to retrieve records from the database
  • The datatables’ twin options of ajax and deferRender is crucial for improving loading time for large data set on the front-end
Advertisements

Related Selects with jQuery AJAX Post in ColdBox

Let’s create a related select form elements using jQuery AJAX post in ColdBox in this quick tip. That is, the available drop down values that are shown in the second select element is dependent on the user selection of the first select element.

We will be working with  the following technologies:

  • jQuery 1.x
  • ColdBox 4.2.x
  • ColdFusion 10

We will assume the following data model and relationship (simplified for this article):

  • Models
    • facility (id,name)
    • department (id,name)
  • Relationship
    • a facility has many departments

Let’s create the methods that we will need in the handler, and save it as handlers\reports.cfc.


component {
  property name="reportService" inject="model";
  property name="facilityService" inject="model";
  property name="departmentService" inject="model";

  public void function submit(event){
    var rc = event.getCollection();
    
    //retrieve list of facilities
    rc.facilities = facilityService.list(asQuery=false);
  }//submit

  public void function getAllDepartments(event) {
    //this method retrieves the data that will be return in JSON format to the front end
    //must be an array before converting to JSON format

    var rc = event.getCollection();
    var cr = "";
    var departments = [];

    //retrieve list of departments based on selected facility
    cr = departmentService.newCriteria();
    cr.createAlias('facilities','f');
    cr.add(cr.restrictions.isEq("f.facilityID",javacast("int",rc.id)));
    rc.departments = cr.list(asQuery=false);

    if( !isNull(rc.departments) && ArrayLen(rc.departments) ){
      for (var i=1; i lte ArrayLen(rc.departments); i++){
        departments[i] = {};
        departments[i].id = rc.departments[i].getDepartmentID();
        departments[i].name = rc.departments[i].getName();
      }//for
    }//if

    event.renderData(type="JSON",data=departments,nolayout=true);
  }//getAllDepartments

}//component

Please note that values must be stored in an array before calling the renderData method with type of JSON.

In the report model’s view where the facility and department select drop downs are used,  add the following code for views/reports/submit.cfm:


<form id="frmReport" class="form-horizontal" method="post" action="#event.buildLink('reports.save')#">
  <div class="form-group required">
    <label for="facility" class="col-md-4 control-label">Facility</label>
    <div class="col-md-3">
      <select name="facility" id="facility" class="form-control">
        <option value="">Please select...</option>
        <cfloop array="#rc.facilities#" index="facility">
        <option value="#facility.getFacilityID()#">#facility.getName()#</option>
        </cfloop>
      </select>
    </div>
  </div>

  <div class="form-group required">
    <label for="department" class="col-md-4 control-label">Department</label>
    <div class="col-md-3">
      <select name="department" class="form-control" id="department">
      <option value="" selected>Please select...</option>
      </select>
    </div>
  </div>
</form>

Finally, at the bottom of submit.cfm, add the JavaScript that will populate second select drop down list base on user selection from the facility drop down (first select).


<script>
$(document).ready(function() {
   $('#facility').change(function(){
      var $selectedFacilityID = $(this).val();
      if($selectedFacilityID.length > 0){
         $.ajax({
            url:'<cfoutput>#event.buildLink("reports.getDepartmentsList")#</cfoutput>/id/' +
$selectedFacilityID,
            type: 'post',
            dataType: 'json',
            success: function(data){
               //id: data[i].ID
               //name: data[i].NAME
               //clear all options to start with
               $('#department').find('option').not(':first').remove();
               if (data.length > 0){            
                 //populate the departments drop down with returned values
                 for (i=0; i < data.length; i++){
                    $('#department').append('<option value="' + data[i].ID + '">' + data[i].NAME + '</option>');
                 } //for
               }//if
            },
            error : function(xhr,textStatus,errorThrown){
               console.log(xhr);
               console.log(textStatus);
               console.log(errorThrown);
            }
         }); //ajax post - getDepartmentsList
      } //if $selectedFacilityID.length
   }); //facility
}
</script>

We call the getDepartmentsList method using jQuery AJAX post and passing in the selected facility’s id to return its departments.

data is a variable used to store the  data from a successful AJAX post. This variable name can be changed. For example, sometimes it is called results. Use do notation to access the columns, and a for loop to loop through the returned records.

Create a Video Lightbox using Bootstrap, HTML5 and ColdFusion Custom Tag

Let’s create a video lightbox using Bootstrap, HTML5’s video tag and encapsulate it with a ColdFusion custom tag. Here’s what you need to do achieve this:

  • Define the CSS styles
  • Create the ColdFusion custom tag
  • Use the custom tag

Define the CSS styles

Create a new file and save it as videos.css. Enter the following styles and save:


.bs-video{
   margin: 10px 0px 10px 0px;
}

.modal-content {
   margin: 0 auto;
   display: block;
}

@media screen and (min-width: 768px) {
   .modal-dialog {
      width: 750px; /* New width for default modal */
   }
   .modal-sm {
      width: 350px; /* New width for small modal */
   }
}

.videogallery {
   width:900px;
   zoom:1;
}

Create the ColdFusion custom tag

We will use HTML5’s video tag and Boostrap modal to show the video in a popup. Create a new ColdFusion file and save it as video-popup.cfm. Enter the following code for this custom tag:


<cfif thisTag.executionMode EQ "start">
   <cfparam name="attributes.width" default="720">
   <cfparam name="attributes.height" default="405">
   <cfparam name="attributes.path" default="">
   <cfparam name="attributes.desc" default="">
   <cfparam name="attributes.vid" default="">
   <cfparam name="attributes.video" default="">
   <cfparam name="attributes.mediaType" default="video/mp4">
   <cfparam name="attributes.image" default="">

   <cfoutput>
      <div class="bs-video">
         <a href="##videoModal-#attributes.vid#" id="#attributes.vid#" class="btn btn-default modalLink" data-toggle="modal"><img src="#attributes.path#/thumbnails/#attributes.image#" alt="#attributes.desc#"/></a>

         <div id="videoModal-#attributes.vid#" class="modal fade">
            <div class="modal-dialog">
               <div class="modal-content">
                  <div class="modal-header">
                     <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                     <p class="modal-title">#attributes.desc#</p>
                  </div>
                  <div class="modal-body">
                     <video id="video-#attributes.vid#" width="#attributes.width#" height="#attributes.height#" controls>
                        <source src="#attributes.path#/#attributes.video#" type="#attributes.mediaType#">
                        Your browser does not support the video tag. Please upgrade your browser.
                     </video>
                  </div>
               </div><!--- modal-content --->
            </div><!--- modal-dialog --->
         </div><!--- videoModal --->
      </div><!--- bs-video --->
   </cfoutput>
</cfif>

Use the custom tag

Let’s use the custom tag we have just created. Create a new file and call it whatever you want. Let’s start with the following skeleton and add the following:

  • Link to Bootsrap library and styles
  • Import the ColdFusion custom tag
  • Import the styles for the video lightbox, i.e. videos.css

You should now have the following:


<!DOCTYPE html>
<html lang="en">
<head>
   <meta http-equiv="Content-Type" content="text/html" charset="utf-8" />
   <cfheader name="X-UA-Compatible" value="IE=Edge" />
   <title>Video Lightbox with HTML5, Bootstrap and ColdFusion Custom Tag</title>
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link rel="stylesheet" href="/path/to/bootstrap.min.css">
   <link rel="stylesheet" href="/path/to/videos.css" type="text/css">
   <script src="/path/to/jquery.min.js"></script>
   <script src="/path/to/bootstrap.min.js"></script>
</head>
<body>

   <cfimport prefix="content" taglib="/path-to/custom-tags/video-tag-folder/" />

</body>
</html>

Now let’s make use of the ColdFusion custom tag we have created to display a video lightbox. In the body after the cfimport tag, add the following code snipet:


<body>
   <cfimport prefix="content" taglib="/path-to/custom-tags/video-tag-folder/" /> 
   <h1>Videos</h1>
   <div class="videogallery">
      <cfoutput>
         <div class="row">
            <div class="col-md-3">
               <content:video-popup path="path-to-video" image="path-video-thumbnail-image#" video="path-to-video-file" type="video/mp4" desc="video-desc" vid="-video-id" />
            </div>
         </div>
      </cfoutput>
   </div>
</body>

 

Populating a Related Select Using jQuery in ColdFusion

In this quick tip we will use jQuery to populate a related select html form element in ColdFusion. The first select element will show a list of states, and the second will show a list of cities based on the selected state.

We will assume that you have a database with tables that holds the state and city information, that the files contacts.cfc and addClients.cfm are saved in the same file folder, and request.dsn is defined in Application.cfc.

Here’s what we need to do:

  • Create queries to retrieve list of states and cities
  • Create a simple form with two related select
  • Use jQuery to populate the related select

1. Create queries to retrieve list of states and cities.

Let’s create a ColdFusion component and name it contacts.cfc. In this component, add the following code fragments:


<cfcomponent>
   <cffunction name="getStates" returntype="query" hint="Get list of states">
      <cfscript>var qGet = "";</cfscript>
      <cfquery name="qGet" datasource="#request.dsn#">
         SELECT stateID, stateName
         FROM states
      </cfquery>
   </cffunction>

   <cffunction name="getCities" output="false" returntype="array" hint="Get cities for a given state">
   <cfargument name="stateID" type="string" required="yes" default="0">
      <cfscript>
         var qGet = "";
         var aCities = [];
      </cfscript>

      <cfquery name="qGet" datasource="#request.dsn#">
         SELECT c.cityID, c.cityName
         FROM cities c
         INNER JOIN states s ON s.stateID = c.FK_stateID
         WHERE c.FK_stateID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.stateID#">
      </cfquery>

      <cfscript>
         //convert query to array
         var cols = qGet.columnList;
         var colsLen = listLen(cols);
         for(var i=1; i<=qGet.recordCount; i++) {
            var row = {};
            for(var k=1; k<=colsLen; k++) {
               row[lcase(listGetAt(cols, k))] = qGet[listGetAt(cols, k)][i];
            } //for k
            arrayAppend(aCities, row);
         } //for i

         return aCities;
      </cfscript>
   </cffunction>
</cfcomponent>

2. Create a simple form with two related select

Let’s a create form to add a client, and name it addClient.cfm. In this file, add the following code fragments:

<!DOCTYPE html>
<html>
<head>
   <meta charset="UTF-8">
   <title>Add Client</title>
   <link href="/path/to/jquery.min.css" rel="stylesheet" type="text/css" />
   <link href="/path/to/bootstrap.min.css" rel="stylesheet" type="text/css"/ >
</head>
<body>
   <script src="/path/to/jquery.min.js"></script>
   <script src="/path/to/bootstrap.min.js"></script>
   <cfscript>
      //retrieve list of states from db
      oContact = createobject("component","contacts");
      states= oContact.getStates();
   </cfscript>

   <cfoutput>
      <form name="addClient" class="form-horizontal" method="post" action="">
         <div class="form-group required">
            <label for="state" class="col-md-4 control-label">State &nbsp;</label>
            <div class="col-md-7">
               <select name="state" id="state" class="form-control">
                  <option value="">&nbsp;</option>
                  <cfloop query="states">
                  <option value="#states.stateID#">#states.stateName#</option>
                  </cfloop>
               </select>
            </div>
         </div>
         <div class="form-group required">
            <label for="city" class="col-md-4 control-label">City &nbsp;</label>
            <div class="col-md-7">
               <!--- the 'city' select will be populated by jQuery when a state is selected --->
               <select name="city" id="city" class="form-control">
                  <option value="">&nbsp;</option>
               </select>
            </div>
         </div>
      </form>
   </cfoutput>

3. Use jQuery to populate the related select

In addClient.cfm, just after the closing form tag, add the following code fragment:


<script>
$(document).ready(function() {
   $('#state').on('change', function(){ 
      var $selStateID = $(this).val();
      //use jQuery ajax call to retrieve list of cities based on selected state
      $.ajax({ 
         method: 'post', 
         dataType: 'json', 
         url: 'contacts.cfc', 
         data: { 
            method: 'getCities', 
            returnFormat: 'json', 
            stateID: $selStateID 
         }, 
         success: 
            function(results,status,xhr){ 
               var $citySelect = $('#city'); 

               //clear all options value except the first one
               $citySelect.find('option:not(:first)').remove(); 

               //if we have cities returned from ajax call, the create options for cities select
               if (results.length){ 
                  $.each (results, function(i){ 
                     $citySelect.append('<option value="' + results[i].cityid + '">' + results[i].cityname + '</option>'); 
                  }); //each 
               } //if 
          } //success 
       }); //$.ajax 
     }); // state.onchange 
   });//document 
   </script> 
</body> 
</html>

Important notes:

For the above jQuery ajax retrieval to work, please ensure the following:

  • dataType must be json,
  • returnFormat must be json, and
  • query to retrieve list of cities must be converted to an array (do not return a query)

The stateID is passed to the function to retrieve all cities with the selected state. The jQuery ajax post happens when the user selects a state from the first select drop down list. $selStateID represent the ID of the selected state.

When a list of cities is returned from the jQuery ajax post, we then populate the cities select drop down list with this return set of values. cityid and cityname are fields in the cities table.

 

Using jQuery UI Autocomplete with hidden ID in ColdBox

Previously in How to use jQuery UI Autocomplete with ColdBox and Bootstrap the value used was singular and also the record identifier. However what if we want to submit the identifier but show another text value for the auto-suggest to the user, for example, if we want to show the client’s full name for auto suggest, but submit the client’s ID.

The environment used for this example is:

  • ColdBox 3.8
  • BootStrap 3.1.1
  • ColdFusion 10

The steps are essentially the same  (the differences are in the helper function and front end UI), i.e.

  • Reference jQuery UI in the main layout
  • Create a helper function to retrieve the required data
  • Use autocomplete on the view page

1 Reference the jQuery UI in the main layout

Open layouts\Main.cfm and add the following reference to the style sheet and JavaScript for jQuery UI (if not already present), i.e.

<head>
  <link href="/path/to/bootstrap/bootstrap.min.css" rel="stylesheet" type="text/css"/ >
  <link href="/path/to/jquery-ui/themes/smoothness/jquery-ui.min.css" rel="stylesheet" type="text/css" />
</head>
<body>
  <script src="/path/to/jquery/jquery.11.1.js"></script>
  <script src="/path/to/jquery/ui/jquery-ui.min.js"></script>
  <script src="/path/to/bootstrap/bootstrap.min.js"></script>
</body>

2 Create a helper function to retrieve the required data

Load includes\helpers\ApplicationHelper.cfm and add the following function:

<cffunction name="getClients" access="remote" output="no">
  <cfscript>
    var qGet = "";
    var clientsList = [];
    var stClients = "";
  </cfscript>
  <cfquery name="qGet" datasource="clients">
    SELECT
      clientid, title, firstname, lastname
    FROM clients
  </cfquery>
  <cfscript>
    //convert query to array
    for (var row in qGet) {
      stClients.label = row.title & ' ' & row.firstname & ' ' & row.lastname;
      stClients.value = row.clientid;
    }
    ArrayAppend(clientsList, stClients);
    return clientsList;
  </cfscript>
</cffunction>

3 Use jQuery UI Autocomplete on the view page

In the view page, for example, the edit.cfm where autocomplete is needed, add the following code:

//somewhere in the form section, add this:
<input type="text" name="clientname" id="clientname" class="form-control client-suggest" value="">
<input type="hidden" name="clientid" id="clientid" class="form-control" value="">

<script>
<cfoutput>
  //retrieve list of clients from db, getClients is a function in the application helper
  var #toScript(getClients(), "clientsList")#;
</cfoutput>

$(document).ready(function() {
   var suggestInput = function (sourceValues, elemClassName){
      $('input.' + elemClassName).each(function(){
         var formElemName = $(this).attr('name');
         var hiddenElemID = formElemName + 'id';

         //on auto suggest
         $(this).autocomplete({
            source: sourceValues,
            select: function(event, ui) {
               selectedObj = ui.item; 
               $(this).val(selectedObj.label);
               $('#'+ hiddenElemID).val(selectedObj.value); 
               return false; 
            },
            change: function(event,ui) {
               var formElemVal = $(this).val();
               if (formElemVal === ''){
                  //clear id from hidden field if input is blank
                  $('#'+ hiddenElemID).val('');
               } //if
            }//change
         }); //autocomplete

         //clear id from hidden field if input is blank
         $('#' + formElemName).blur(function(){
            if ( $('#' + formElemName).val() == ''){
               $('#' + hiddenElemID).val(''); 
            }
         });
      } //each
   } //suggestInput

   if ($('.client-suggest').length){
      //activate auto suggest for client input box 
      //(i.e. the input with class of 'client-suggest')
      suggestInput(clientsList,'client-suggest');
}); //document.ready
</script>

Here we use the ColdFusion toScript function to convert ColdFusion value to JavaScript saving it as clientsList. getClients is the ColdBox helper function which we added to ApplicationHelper.cfm.

The client name input box will sugget the client’s full name (including title), but upon form submission, the client’s ID will be submitted.

Quick Tip on using ColdFusion cfquery in cfscript

We’re all familiar with using cfquery to query databases in ColdFusion. But what is its equivalent in cfscript? Here are a few query examples to illustrate. For each example we will show the cfquery and its equivalent in cfscript. For these examples we will be performing the CRUD operations on the clients database.

1. Create (i.e. INSERT into the database)

Let’s start with the cfquery tag version:

<cfquery name="qInsert" datasource="clientsDS">
  INSERT INTO clients
  (
    title,
    firstname,
    lastname,
    email,
    phone
  )
  VALUES
  (
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  )
</cfquery>

Here’s the cfscript equivalent:

<cfscript>
  qService = new query();
  qService.setDatasource("clientsDS");
  qService.setName("qInsert");
  qService.addParam(name="title", value="#trim(form.title)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="firstname", value="#trim(form.firstname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="lastname", value="#trim(form.lastname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar");
  qService.setSql("
    INSERT INTO clients
    (title, firstname, lastname, email, phone)
    VALUES
    (:title, :firstname, :lastname, :email, :phone)
  ");
   qService.execute();
</cfscript>

2 Read (i.e. SELECT from the database)

Let’s start with the cfquery tag version:

<cfquery name="qGet" datasource="clientsDS">
  SELECT 
    title, firstname, lastname, email, phone
  FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

  qService = new query(); 
  qService.setDatasource("clientsDS"); 
  qService.setName("qGet"); 
  qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
  qService.setSql("
   SELECT title, firstname, lastname, email, phone
   FROM clients
   WHERE clientID = :clientID
  ");
  results = qService.execute().getResult();

3 Update (i.e. Update the database)

Let’s start with the cfquery tag version:

<cfquery name="qUpdate" datasource="clientsDS">
  UPDATE clients
  SET
    email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qUpdate");
 qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
 qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar")
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
  UPDATE clients
  SET
   email = :email, 
   phone = :phone
  WHERE clientID = :clientID
 ");
 qService.execute();

4 Delete (i.e. DELETE from the database)

Let’s start with the cfquery tag version:

<cfquery name="qDelete" datasource="clients">
  DELETE FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qDelete");
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
   DELETE FROM clients
   WHERE clientID = :clientID
 ");
 qService.execute();

Setting Up an MS Access 32 bit Data Source in ColdFusion

If you see this error when verifying an MS Access data source in ColdFusion Administrator:

Connection verification failed for data source: <data-source-name> java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

Then you need to follow these steps to properly set up an MS Access 32 bit Data Source on a 64 bit ColdFusion 10 installation running on Windows 2008 SP2 (because the add new data source function in ColdFusion 10 Admin doesn’t do correctly):

  1. Preparations
  2. Set up the 32 bit System DSN
  3. Create the Data Source in ColdFusion Administrator
  4. Verify Connection to Data Source

1 Preparations

Before we get started, here are some tips to ensure a successful setup:

  • If the Microsoft Access driver is not available, then please download and install it:
    • Copy this file to the server where ColdFusion is installed: AccessDatabaseEngine_x64.exe
    • Launch to install the driver using default location and settings
  • If the MS Access data source exists in ColdFusion Administrator, please remove it before creating it via the 32 bit ODBC Data Source Administrator
  • You will see following errors when the db subfolder in the ColdFusion installation is missing:
    • If data source deletion in ColdFusion Administrator results in a 500 Internal Server Error
    • java.io.FileNotFoundExcetption and file operation write error:
      An error occurred when performing a file operation WRITE on file C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp
      The cause of this exception was: java.io.FileNotFoundExcetption: C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp (The system cannot find the path specified).
    • If the db folder is missing, then copy the db folder into C:\ColdFusion10\<instance-name>. At the very least, you will need this subfolder and its contents: C:\ColdFusion10\<instance-name>\db\slserver54
    • If you have multiple ColdFusion instances, then a good place to look for a copy of the db folder is under the default cfusion instance, i.e. C:\ColdFusion10\cfusion\

2 Set up the 32 bit System DSN

To setup the 32 bit MS Access System DSN, follow these steps:

  • Log on to the server where ColdFusion is installed
  • Browse to C:\Windows\SysWOW64 and locate the 32 bit ODBC Data Source Administrator, i.e. odbcad32.exe
  • Right click this file and launch it as an administrator
  • In the ODBC Data Source Administrator dialogue, make the following selections:
    • Select the System DSN tab and click Add…
    • Select Microsoft Access Driver (*.mdb) from the drivers list and click Finish
    • In the Data Source Name input box, enter the exact data source name to be used in the ColdFusion Administrator (the name here must match)
    • Under Database, click Select… and browse to select the Microsoft Access database file, i.e. database-name.mdb
    • Click OK to close the dialogue
    • You should now see this new data source in the System DSN list
  • Confirm an entry has been added to the registry in the 32 bit node as follows:
    • Click Start and type regedit in the search box and then press Enter
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here

3 Create the Data Source in ColdFusion Administrator

Follow these steps to create the Microsoft Access data source in the ColdFusion Administrator:

  • Log on to the ColdFusion Administrator
  • Click Data Sources under the Data & Services section on the left pane
  • Check to see if this data source already exists. If it does, and there isn’t a registry entry for it, then delete it first before continuing.
  • In the Add New Data Source section at the top, enter and select the following:
    • Data Source Name (this must be the same name used when creating the 32 bit System DSN)
    • Driver: select Microsoft Access
    • Click Add
  • For Database File, enter the full path and file name of the Microsoft Access database (or browse to select it from the file system)
  • Leave Use Default User Name checked
  • Click Show Advanced Settings and make the following changes:
    • For Allowed SQL, de-select the following:
      • CREATE
      • DROP
      • ALTER
      • GRANT
      • REVOKE
    • Tick Enable connection validation
  • Click Submit to create this new data source
  • You will see the following error messages:
    Unable to update the NT registry.
    Variable DRIVERPATH is undefined.
  • Ignore these error messages. An entry in the registry would have been created.
  • To confirm an entry has been added to the registry in the 64 bit node, do this:
    • Click Start and type regedit in the search box and press Enter (if not already opened)
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here
  • Return to the ColdFusion Administrator and click Data Sources under the Data & Services section on the left pane
  • Find the new data source that you have just created, and click the tick icon to the left of the data source name to verify it.
  • You should see OK in the status column (right most) if everything is good.

4 Verify Connection to Data Source

As a final check to ensure that we can access this new data source, let’s do the following:

  • Create a ColdFusion file (.cfm) and enter the following:
    <!doctype html>
    <html>
    <head>
       <meta charset="utf-8">
       <title>MS Access Datasource Check</title>
    </head>
    <body>
       <h1>MS Access Datasource Check</h1>
       <cfquery name="qCheckDS" datasource="data-source-name">
          SELECT now() AS dt
       </cfquery>
       <cfdump var="#qCheckDS#" label="qCheckDS">
    </body>
    </html>
    
  • Save it somewhere on your website and browse to it.
  • If data source connection is successful, you will see this dump, that is, something similar to this:

Congratulations! You’ve just successfully created an MS Access 32-bit data source on a 64-bit ColdFusion 10 installation.