• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1926
  • Last Modified:

Jquery datatables server side with Coldfusion

Hello Experts,

I found a script to use jQuery datatables with coldfusion and cant seem to get it to work right. I followed all the instructions and went over each line of code but for some reason it wont load the data. I dont receive any errors either it just sits with a processing message (see attached screen shot) Also i noticed it isnt actually loading the datatable just the html structure. The script is broken into 2 pages I will copy the code below. Thanks in advance for any help you may provide !!!!

screen shot of the issue

<cfparam name="form.table" default="">
<cfparam name="form.sColumns" default="">
<cfparam name="form.editButtonText" default="">
<cfparam name="form.editButtonTarget" default="">
<cfparam name="form.sSearch" default="">
<cfparam name="variables.fieldlist" default="">

<cfsetting showDebugOutput=false>
<cfsetting enablecfoutputonly="true">
<cfprocessingdirective suppresswhitespace="true">

<!--- this comes from the AJAX script in the template --->
<cfset variables.fieldlist=form.sColumns>
<cfset variables.count=0>

<!--- strip off the comma if it is the last element --->
<cfif right(variables.fieldlist,'1') EQ ",">
    <!--- last char is a comma --->
    <cfset variables.listLength = len(variables.fieldlist)>
    <cfset variables.fieldlist = left(variables.fieldlist, variables.listLength-1)>

<!--- get count of records --->
<cfquery name="qGetCount" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
    SELECT COUNT(*) AS fullCount
    FROM #form.table#

<cfquery name="rResult" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
    FROM #form.table#

    WHERE 1 = 1
<cfif len(form.sSearch)>
        AND (
<cfloop from="1" to="#listLen(variables.fieldlist)#" index="variables.index">
#listGetAt(variables.fieldlist, variables.index,',')# LIKE '%#form.sSearch#%' <cfif variables.index LT listLen(variables.fieldlist)> OR </cfif>

<cfif isdefined('form.iSortCol_0')>
<cfloop from="0" to="#form.iSortingCols-1#" index="variables.i">
    #listGetAt(variables.fieldlist,form["iSortCol_#variables.i#"]+1)# #form["sSortDir_#variables.i#"]# <cfif variables.i is not form.iSortingCols-1>, </cfif>


<!--- strip off the table name from the values, otherwise it will break making the json --->
<cfset variables.fieldlist = ReplaceNoCase(variables.fieldlist,'#form.table#.','','all')>

<!--- create the JSON response --->
<cfsavecontent variable="variables.sOutput"><cfoutput>{
    "sEcho": #form.sEcho#,
    "iTotalRecords": #qGetCount.fullCount#,
    "iTotalDisplayRecords": #rResult.recordcount#,
    "aaData": [
    <cfloop query="rResult" startrow="#form.iDisplayStart+1#" endrow="#form.iDisplayStart+form.iDisplayLength#"><cfset variables.count=variables.count+1>
[<cfloop list="#variables.fieldlist#" index="variables.i">
<!--- custom translations --->
<cfif variables.i is not listLast(variables.fieldlist)>, </cfif>

<cfif rResult.recordcount LT form.iDisplayStart+form.iDisplayLength>
    <cfif variables.count is not rResult.recordcount>,</cfif>
    <cfif variables.count LT form.iDisplayLength>,</cfif>


Open in new window


<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.0/jquery.js"></script>
<script type="text/javascript" src="js/plugins/jquery.dataTables.min.js"></script>

<style type="text/css" title="currentStyle">
    @import "demo_page.css";
    @import "demo_table.css";

<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
    $('#displayData').dataTable( {
    "bProcessing": true,
    "bStateSave": true,
    "bServerSide": true,
    "sAjaxSource": "handler.cfm",
    "aoColumns": [
{"sName": "id", "sTitle": "idResource", "sWidth": "20%", "bSortable": "true"},
{"sName": "name", "sTitle": "name", "sWidth": "40%", "bSortable": "true"},
{"sName": "description", "sTitle": "description", "sWidth": "40%", "bSortable": "true"}
"sPaginationType": "full_numbers",
"aaSorting": [[1,'asc']],
"oLanguage": {
        "sLengthMenu": "Page length: _MENU_",
        "sSearch": "Filter:",
        "sZeroRecords": "No matching records found"
"fnServerData": function ( sSource, aoData, fnCallback ) {
            { "name": "table", "value": "ukLocationCodes" },
            { "name": "sql", "value": "SELECT [idResource], [name], [description]" }

            $.ajax( {"dataType": 'json',
                 "type": "POST",
                 "url": sSource,
                 "data": aoData,
                 "success": fnCallback} );

// $.getJSON( sSource, aoData, function (json) {fnCallback(json)} );
                } );
            } );

<h2>Data Tables </h2>

<table cellpadding="0" cellspacing="0" border="0" class="display" id="displayData">
            <th align="left">ID</th>
            <th align="left">Code</th>
            <th align="left">Location</th>
            <td colspan="3" class="dataTables_empty">Loading data from server</td>

Open in new window

1 Solution
erikTsomikSystem Architect, CF programmer Commented:
in Firefox try using firebug it will tell you does it fail
I'm not familiar w/the plugin but the error comes from this line:

        "aoColumns": [ {"sName": "id", "sTitle": "idResource", ...},

The name of the column in your SQL is "idResource" - not "id". So either use this:

            "aoColumns": [ {"sName": "idResource",

... OR change the sql to alias the column as "ID"

           { "name": "sql", "value": "SELECT [idResource] AS id, ....}

   I dont receive any errors either it just sits with a processing message

There was an error but you couldn't see it easily without an on error function. During development you can use it to log errors to FF's web console:  

             Tools => Web Developer > Web Console

$.ajax( {"dataType": 'json',
   "type": "POST",
   "url": sSource,
   "data": aoData,
   "success": fnCallback,
    error: function (jqXHR, status, errorThrown) {  // <==== log error to console
             console.log("jqXHR.status"+ jqXHR.status); 
             console.log("jqXHR.statustext"+ jqXHR.statustext); 
             console.log("jqXHR.responseText"+ jqXHR.responseText); 
} );

Open in new window

I know the script is just a demo example but ... I'd be remiss if I didn't mention how dangerous it is.

1) It's wide open to sql injection

2) Worse it creates an open gateway allowing anyone to query *any* table in your db with a single cfhttp call.

Both are very bad things ..
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

joedfuseAuthor Commented:
Thanks, I will check these answers today. This query sits behind an authentication so only internal staff will have access to it. Also I will be adding parameter scripts to each 'where' clause inside the queries. Once I get this working at least lol. I will post back today after I try these suggestions. Thanks
Don't rely on that. It won't prevent a mischievous employee from gaining access to info they shouldn't have or even tampering with the data and this application would be responsible for letting it happen.  I'd strongly recommend fixing the script to eliminate those holes, because security through obscurity never works ... Fixing it isn't hard and may as well do it the right way.  

Just my $0.02
joedfuseAuthor Commented:
Do you know of a better way to accomplish a dynamic table using coldfusion? Im definitly open for suggestions since the above changes didnt get it to load anyway lol
>  since the above changes didnt get it to load anyway lol

Really? Strange. I tested the script and it worked fine as long once I created a db table with that name (ukLocations) and those columns (idResource,etc...).  I used the onError debugging code to figure out why it wasn't working.  Did you add the debugging code I mentioned? In Firefox it should report the page is throwing an error and the details ie HTTP 500.

ie   error: function (jqXHR, status, errorThrown) {  // <==== log error to console
             console.log("jqXHR.status"+ jqXHR.status);
             console.log("jqXHR.statustext"+ jqXHR.statustext);
             console.log("jqXHR.responseText"+ jqXHR.responseText);
> Do you know of a better way to accomplish a dynamic table using coldfusion?

The script is fine for what it does. You just need to tighten up the SQL end. But let's get it working first, lol. Then I'll show you how to fix the security.

Edit:  Also try testing the handler script manually w/cfhttp:

<cfhttp url="http://yoursite.com/path/to/handler.cfm" method="post">
      <cfhttpparam name="table" type="FORMFIELD" value="ukLocationCodes">
      <cfhttpparam name="sColumns" type="FORMFIELD" value="idResource,name,description">
      <cfhttpparam name="sql" type="FORMFIELD" value="SELECT [idResource], [name], [description]">
      <cfhttpparam name="sEcho" type="FORMFIELD" value="">
      <cfhttpparam name="IDISPLAYSTART" type="FORMFIELD" value="1">
      <cfhttpparam name="IDISPLAYLength" type="FORMFIELD" value="100">

<strong>JSON Reponse or ERROR:</strong><br>
<cfoutput>#cfhttp.fileContent# </cfoutput>

<cfdump var="#cfhttp#" label="Full Response">
joedfuseAuthor Commented:
I am not getting any errors... seems to just stay at loading data from server. Where would I add the above script you posted?
You're probably getting an error, but the code suppresses them. ie It doesn't do anything to display errors.

Put the error code in the $ajax call. Right after "success": fnCallback,  like shown here.

If you have problems you can also debug with a cfhttp post. See example here. Then you can see any errors that are occurring.
Gurpreet Singh RandhawaWeb DeveloperCommented:
Please check your JSON response in the Firebug what you are getting. There might be an issue with the resultset being returned.

Please note if there is any ' or " is coming from the database, you might land in trouble.

I have written the modified code of this example and it works flawlessly

I will post the Code for your reference asap by evening my time

Lol, there's nothing to rewrite. It already works perfectly. You just need to use the *correct* table and column names: table-ukLocationCodes columns-idResource,name,description.  The on error code and cfhttp code posted earlier show that's the problem in the error message.

The only problem w/the original code was this line. It accidentally swaps the column *name* and title.  sName must the *name* of one of the columns in the SELECT list.  

           {"sName": "id", "sTitle": "idResource", "sWidth": "20%", "bSortable": "true"}

As you can see "id" is NOT a column in that list, but "idResource" is ...

            { "name": "sql", "value": "SELECT [idResource], [name], [description]" }

You simply need to swap the values.

           {"sName": "idResource", "sTitle": "id", "sWidth": "20%", "bSortable": "true"},

Once you get that working, then you can secure the SQL. (Edit) Though we need to know your which db type and version you're using for that part.
joedfuseAuthor Commented:
This script just isnt responding i made the changes as you said and see that what you say should work fine. I think the js process page is doing something wrong. I am going to repost the question with a different code. I will accept your solution for the time you put in please join me in the new question once i get it up as well.

joedfuseAuthor Commented:
I didnt get it to work but not due to expert solution as what he said was correct. So I am accepting this and will create new question with different script
Instead of accepting an answer that you say doesn't work for you with a "B", you should instead post your db type and table structure ie ddl.

          create table ukLocations (
              ... columns ... ?

Because there's something different on your end that we don't know about... I'd guess it's your table structure, given that I tested it with the names you provided and it works perfectly, with only the one change.

While I'm very confident the original code does work,  don't feel like you *have to* accept an answer. If something isn't right, post more details or ask follow up questions, until it does.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now