asked on

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 !!!!

Handler.cfm

<cfsilent>
<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)>
</cfif>

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

<cfquery name="rResult" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
    #preservesinglequotes(form.sql)#
    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>
</cfloop>
    )
</cfif>

<cfif isdefined('form.iSortCol_0')>
    ORDER BY
<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>
</cfloop>

</cfif>
</cfquery>

<!--- 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 --->
"#rResult[variables.i][rResult.currentRow]#"
<cfif variables.i is not listLast(variables.fieldlist)>, </cfif>
</cfloop>]

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

</cfloop>
            ]
}</cfoutput></cfsavecontent>
</cfprocessingdirective>
</cfsilent>
<cfoutput>#variables.sOutput#</cfoutput>

Open in new window

temp.cfm

<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";
</style>

<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 ) {
        aoData.push(
            { "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)} );
                        }
                } );
            } );
</script>

<h2>Data Tables </h2>
<br/>

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

Open in new window

erikTsomik

in Firefox try using firebug it will tell you does it fail

_agx_

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

_agx_

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

joedfuse

ASKER

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

_agx_

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

joedfuse

ASKER

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

_agx_

> 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);
}

_agx_

> 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">
</cfhttp>

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

<cfdump var="#cfhttp#" label="Full Response">

joedfuse

ASKER

I am not getting any errors... seems to just stay at loading data from server. Where would I add the above script you posted?

_agx_

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.

Coast Line

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

Cheers

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joedfuse

ASKER

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.

Thanks

joedfuse

ASKER

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

_agx_

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.