datatables2.cfm

<!--- on initial request, run query --->
<cfset "session.#attributes.myactionfile#" = "" />

<cfsavecontent variable="datasorter_js">
		<style type="text/css" title="currentStyle">
			@import "css/datatables_table.css";
		</style>
		<script type="text/javascript" language="javascript" src="js/jquery.dataTables.min.js"></script>
		<script type="text/javascript" charset="utf-8">
			$(document).ready(function() {
				$('#<cfoutput>#attributes.myactionfile#</cfoutput>').dataTable( {
					"bProcessing": true,
					"bServerSide": true,
					"sPaginationType": "full_numbers",
					"sAjaxSource": "<cfoutput>#attributes.myactionfile#</cfoutput>.cfm"
				} );
			} );
		</script>
</cfsavecontent>

<cfhtmlhead text = "#datasorter_js#">

xxx_datatables_action.cfm

<!--- sets filename as session variable --->
<cfset filename = evaluate("replace(GetFileFromPath(GetCurrentTemplatePath()),'.cfm','')")>
<cfparam name="session.#evaluate("filename")#" default="">

<!--- if sessions not set on server or initial request, the variable will not be a query --->
<cfif not IsQuery(evaluate("session.#filename#"))>
	<!--- initial data set for query of queries --->
        <cfquery name="qrydatatable" datasource="#application.DSN#">
	<!--- 
        query goes here
        NO ORDER BY NEEDED HERE WILL BE DONE BY QoQ USING #sIndexColumn#
        --->
        </cfquery>
        
	<!--- SQL queries Get data to display --->
	<cfset "session.#filename#" = qrydatatable>
</cfif>

<!--- initial sorting column --->
<cfset sIndexColumn = "funding_opportunity_num" />

<!--- generic processing (no need to touch)--->
<cfinclude template="xxx_datatables_processing.cfm" />

xxx_datatables_processing.cfm

<!--- 
	Script:    DataTables server-side script for ColdFusion (cfm) and MySQL	
	License:   GPL v2 or BSD (3-point)	
	Notes:
		tested with DataTables 1.6.1 and jQuery 1.2.6+, Adobe ColdFusion 9 (but should work fine on at least 7+)
		
		to work with pre 1.6 datatables replace both occurances of sSortDir_ with iSortDir_ 
		
		Get a free developer version of ColdFusion from http://www.adobe.com/products/coldfusion/
		or try out the open source railo cfml engine from http://www.getrailo.org/
		or try out the open source openbd cfml engine from http://www.openbluedragon.org/
 --->

<!--- turn off debugging output --->
<cfsetting showDebugOutput="No">
<cfset listColumns = "" />
<cfset listColumnsCast = "" />
<cfset flag_cast = 0 />

<cfset arrayMetaData = getMetaData(evaluate("session.#filename#")) />
<cfset qOriginal = replace(de('session.#filename#'),'"','','all') />

<cfloop index="thisColumn"  from="1" to="#arraylen(arrayMetaData)#">
	<cfset listColumns = ListAppend(listColumns, arrayMetaData[thisColumn]["Name"]) />
	<cfset listColumnsCast = ListAppend(listColumnsCast, "CAST(#arrayMetaData[thisColumn]["Name"]# as VARCHAR) as #arrayMetaData[thisColumn]["Name"]#") />

	<cfif arrayMetaData[thisColumn]["TypeName"] NEQ "VARCHAR">
		<cfset flag_cast  = 1 />
	</cfif>
</cfloop>

<!--- set query of queries --->
<cfset objAttributes.dbtype = "query"/>

<cfif flag_cast>
<!--- set columns to varchar --->
<cfset objAttributes.name="queryObject" />
	<cfquery attributecollection="#objAttributes#">
		SELECT #listColumnsCast# 
		FROM #qOriginal#
	</cfquery>
</cfif>
	
<!--- query name for filtering data set--->
<cfset objAttributes.name="qFiltered" />
		
<!--- 
	Paging 
--->
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
<cfparam name="url.sIndexColumn" default="1" />

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

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

<!--- Data set after filtering --->
<cfquery attributecollection="#objAttributes#">
	SELECT #listColumns# 
	FROM #qOriginal#
	
	<!--- setup where clause --->
	<cfif len(trim(url.sSearch))>
		WHERE 
		1 = 0
		<!--- filter --->
		<cfloop index="thisColumn"  from="1" to="#arraylen(arrayMetaData)#">
			OR upper(#arrayMetaData[thisColumn]["Name"]# )
			LIKE	<cfqueryparam value="%#ucase(trim(url.sSearch))#%" cfsqltype="CF_SQL_#arrayMetaData[thisColumn]["TypeName"]#">
		</cfloop>
	</cfif>
	
	<!--- Ordering --->
	ORDER BY 
    <cfif url.iSortingCols gt 0>
		<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>
	<cfelse>
		#sIndexColumn#		
	</cfif>
</cfquery>

<!--- set length for the initial unfiltered data set --->
<cfset qCount.total = evaluate("session.#filename#.recordcount")>

<!--- Output --->
<cfcontent reset="Yes" />
{"sEcho": <cfoutput>#val(url.sEcho)#</cfoutput>, 
"iTotalRecords": <cfoutput>#qCount.total#</cfoutput>, 
"iTotalDisplayRecords": <cfoutput>#qFiltered.recordCount#</cfoutput>, 
"aaData": [ 
	<cfoutput query="qFiltered" startrow="#val(url.iDisplayStart+1)#" maxrows="#val(url.iDisplayLength)#">
		<cfif currentRow gt (url.iDisplayStart+1)>,</cfif>
		[<cfloop list="#listColumns#" index="thisColumn">
			<cfif thisColumn neq listFirst(listColumns)>,</cfif>
			#serializeJSON(qFiltered[thisColumn][qFiltered.currentRow])#
		</cfloop>
		]
	</cfoutput> 
	] }

xxx_server_side.cfm

<!--- APPLICATION.GetApplicationSettings().SessionManagement --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>

	<cf_datatables2 myactionfile="xxx_datatables_action">

	<body id="dt_example">
		<div id="container">
			<div id="dynamic">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="xxx_datatables_action">
	<thead>
		<tr>
			<th>Rendering engine</th>
			<th>Browser</th>
			<th>Platform(s)</th>
			<th>Engine version</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td colspan="4" class="dataTables_empty">Loading data from server</td>
		</tr>
	</tbody>
	<tfoot>
		<tr>
			<th>Rendering engine</th>
			<th>Browser</th>
			<th>Platform(s)</th>
			<th>Engine version</th>
		</tr>
	</tfoot>
</table>
	
		</div>
	</body>
</html>

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, ...http://www.datatables.net/

need to scope all variables

need to use cast in query of having user in query set datatype.

    <cfset listColumnsCast = ListAppend(listColumnsCast, "CAST(#arrayMetaData[thisColumn]["Name"]# as VARCHAR) as #arrayMetaData[thisColumn]["Name"]#") />
    <!--- set columns to varchar --->
    <cfset objAttributes.name="queryObject" />
    <cfquery attributecollection="#objAttributes#">
        SELECT #listColumnsCast# 
        FROM queryObject
    </cfquery>