I am trying to create basic datatables.net and coldfusion server side example but it doesnt seem to works

Question

This example was based on this link
https://datatables.net/forums/discussion/40613/datatable-jquery-server-side-with-adobe-cold-fusion-and-sql-server

and i am using datatable version 1.10

On first page load, all data sucessfully loaded into #formsTable
But it wont work when clicking sorting,searching and paging.

It just hang with 'processing..'

pageA.cfm (only show scripting part here...)

<script type="text/javascript"> 
   $(document).ready(function(){        
        $('#formsTable').DataTable({
        processing:true,
        serverSide:true,
        ajax:{
           url:'pageB.cfm'
        },  
        columns:[
                {title: "id",data:'id'},
                {title: "Name",data:'name'},
                {title: "Emp.No",data:'empno'},
                {title: "IC",data:'ic'}
            ]

        })
   })
</script>

pageB.cfm (server-side)

<cfcontent reset="true">

<cfset listColumns = "id,emp_no,emp_name,number_id2" />

<cfset sIndexColumn = "id" />

<cfparam name="draw" default="1" type="integer" />

<cfparam name="start" default="0" type="integer" />

<cfparam name="length" default="10" type="integer" />

<cfparam name="url.sSearch" default="" type="string" />

<cfparam name="url.iSortingCols" default="0" type="integer" />

<!--- query data --->
<cfquery datasource="hrms" name="qFiltered">
    select id,emp_no,emp_name,number_id2 from employee
    <cfif len(trim(url.sSearch))>
        Where
        (
        <cfloop list="#listColumns#" index="thisColumn">
          <cfif thisColumn neq listFirst(listColumns)>
          OR
          </cfif>
          #thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" />
        </cfloop>
        )
    </cfif>

    <cfif url.iSortingCols gt 0>
    ORDER BY
    <cfloop from="0" to="#url.iSortingCols-1#" index="thisS">
      <cfif thisS is not 0>, </cfif>
        #listGetAt(listColumns,(url["iSortCol_"&thisS]+1))#
      <cfif listFindNoCase("asc,desc",url["sSortDir_"&thisS]) gt 0>
        #url["sSortDir_"&thisS]#
      </cfif>
    </cfloop>
    </cfif>

</cfquery>

<!--- query data count --->
<cfquery dbtype="query" name="qCount">
    SELECT COUNT(#sIndexColumn#) as total
    FROM qFiltered
</cfquery>

<!--- Output --->
{"draw": <cfoutput>#val(draw)#</cfoutput>,
"recordsTotal": <cfoutput>#qCount.total#</cfoutput>,
"recordsFiltered": <cfoutput>#qFiltered.recordCount#</cfoutput>,
"aaData": [
<cfoutput query="qFiltered" startrow="#val(start+1)#" maxrows="#val(length)#">

<cfif currentRow gt (start+1)>,</cfif>
{
"id":#SerializeJSON(qFiltered.currentrow)#,
"name":#SerializeJSON(qFiltered.emp_name)#,
"empno":#SerializeJSON(qFiltered.emp_no)#,
"ic":
<cfif trim(qFiltered.number_id2) neq '[empty string]'>
#SerializeJSON(qFiltered.number_id2)#
<cfelse>
""
</cfif>
}
</cfoutput> ] }

I am stuck here , it seems that something is missing on pageB.cfm ?

Edited: thanks for @user12031119 pointing out that sent parameter from pageA.cfm to pageB.cfm is different now with v1.10, so basically these parameters should be change :-

sEcho : draw
iDisplayStart : start
iDisplayLength : length
iTotalRecords : recordsTotal
iTotalDisplayRecords : recordsFiltered

with that change i can now click on next/prev page (paging) but still search and sorting dont work

You need to look into SerializeJSON(qFiltered, "struct"). It is a much quicker way to build JSON data. Also use Postman to see if the data is coming out as expected. — James A Mohler
there is no error on json format data, it can load and fill table with data on first page load — AhmadZuwari
Never trust client provided data. That kind of dynamic sql is vulnerable to sql injection. See learncfinaweek.com/course/index/section/Security/item/Injection . — SOS

user12031119 user12031119 · Accepted Answer · 2020-06-04T16:35:19

Yes, unfortunately the code sample you're viewing on datatables.net is for legacy datatables. Under datatables 1.10.x, you will have to use the upgraded keys which version 1.10 expects in your returned json structure. It looks like you already updated pageA.cfm with the new parameters, however you also need to update your server-side return parameters in pageB.cfm.

Here's a guide to upgrade to version 1.10 https://datatables.net/upgrade/1.10

Here's a guide for the new parameters for server-side 1.10 https://datatables.net/manual/server-side

Edit 1 thanks to James A Mohler

Edit 2 per issue noticed by myself

Apparently I was mistaken that datatables only accepts an array of arrays when returned from the server. Apparently it also accepts an array of structs, which helps simplify my answer. However, this will require aliasing the columns in the select statement to match the column definitions defined in pageA.cfm. With that said, here are the changes needed.

The first code modification to pageB.cfm will be to alias the columns in your select statemen to match their definition in pageA.cfm.

<cfquery datasource="hrms" name="qFiltered">
    select id as id, emp_no as empno, emp_name as name, number_id2 as ic from employee

Once that's done, change the following in your <cfoutput> block from pageB.cfm

Change sEcho to draw
Change iTotalRecords to recordsTotal
Change iTotalDisplayRecords to recordsFiltered
Change aaData to data
Use serializeJson() with the "struct" option to return an array of structs with the key values matching up to how you defined them in pageA.cfm. If you're using ACF instead of lucee, then you might have to change your column definitions in pageA.cfm to uppercase since ACF doesn't preserve case and uppercases key names.

<!--- Output --->
<cfoutput>
{
    "draw": #val(url.sEcho)#,
    "recordsTotal": #qCount.total#,
    "recordsFiltered": #qFiltered.recordCount#,
    "data": #serializeJson(qFiltered, "struct")# 
}
</cfoutput>

I am trying to create basic datatables.net and coldfusion server side example but it doesnt seem to works

3 Answers

Edit 1 thanks to James A Mohler

Edit 2 per issue noticed by myself