Solved

Coldfusion Server Side Data tables.... once again :P

Posted on 2013-02-04
32
1,573 Views
Last Modified: 2013-02-15
Hello again,

I posted a question in the past about this and basically gave up trying to work on this. Wouldn't you know it comes up again so I REALLY need to figure out how to get this working and how to work with this since its always coming up. I'm going to post everything I have for this.... Its funny because im not getting any errors its not even loading the jquery library.... These are located in the included header. I know its seeing the header properly as the style of the text is there. Thanks in advance ... I REALLY need some help understanding how this works.

ssDatatables.cfm

<cfinclude template="header.cfm">

<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
    $('#displayData').dataTable( {
    "bProcessing": true,
    "bStateSave": true,
    "bServerSide": true,
    "sAjaxSource": "handler.cfm",
    "aoColumns": [ 
{"sName": "clientid", "sTitle": "ID", "sWidth": "20%", "bSortable": "true"},
{"sName": "firstname", "sTitle": "FirstName", "sWidth": "40%", "bSortable": "true"},
{"sName": "lastname", "sTitle": "LastName", "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": "tblActivity" },
            { "name": "sql", "value": "SELECT [clientid], [firstname], [lastname]" }
            );

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


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

<h2>Data Tables Example</h2>

<p>This is the front end template for a data Tables example. It is handling the data(Json) from an AJAX post, and displaying it in a tabular view below. 
    All changes are made inline, so there are no refreshes.</p>
<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


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.dsnLogin#" username="#request.dsnuLogin#" password="#request.dsnpLogin#">
    SELECT COUNT(*) AS fullCount
    FROM #form.table#
</cfquery>

<cfquery name="rResult" datasource="#request.dsnLogin#" username="#request.dsnuLogin#" password="#request.dsnpLogin#">
    #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

2-4-2013-7-02-52-AM.jpg
0
Comment
Question by:joedfuse
  • 20
  • 7
  • 3
  • +1
32 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 38851464
I think you're so used to the issue, that you forgot to actually ask a question.  

Your question title is about database tables, but you mention not loading jquery... what is the issue you're having?
0
 

Author Comment

by:joedfuse
ID: 38851515
Ha, my bad

The code I submitted doesn't work. The jQuery table doesnt load at all. All I get is whats in the screen shot. I use datatables all the time. Simply by wrapping an output tag around a row. However im getting into serious load issues on huge query results. So I made an attempt at using the data table server side script. Thats where we are at now. How can I get the above script to work. A big issue is I dont even get an error ... nothing works as you can see in the screen shot

Thanks, sorry for the run on before
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38851860
@gdemaria - I don't have time to work on this, but here's the original thread he alluded to fwiw
0
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 38853848
I cannot Try your code, until you can show the table contents you are using:

I want to see the JSOn resunted from the Code, you can use firebug to see what JSON is returned. Also Use the JSStringFormat Function in the returned values to it can skip the single quotes or double characters as it should not break the code, Please show me the JSOn response or a Link where i can see its Working so i can Check Properly

Thanks
0
 

Author Comment

by:joedfuse
ID: 38854727
The data structure is ... example below obviously not real values. Also the table has a lot more columns but im just trying to get it working using a few.

clientid     |       firstname      |       lastName
1                             joe                           smith
2                             joe                           smith
3                             joe                           smith
4                             joe                           smith
5                             joe                           smith

a url is Click here for datatable page
0
 

Author Comment

by:joedfuse
ID: 38854755
I found another technique to get datatables to work with coldfusion. I modified the columns datasource etc... code and error below

cfData.cfm

<cfinclude template="header.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/
 --->
<!--- 
    Easy set variables
 --->
  
<!--- table name --->
<cfset sTableName = "tblActivity" />
 
<!--- list of database columns which should be read and sent back to DataTables --->
<cfset listColumns = "firstname,lastname" />
 
<!--- Indexed column --->
<cfset sIndexColumn = "clientid" />
  
<!--- ColdFusion Datasource for the MySQL connection --->
<cfset coldfusionDatasource = "#request.dsnLogin#"/>
 
<!--- 
If you just want to use the basic configuration for DataTables with ColdFusion server-side, there is no need to edit below this line
 
Note: there is additional configuration below for the "version" column in query and output 
 --->
 
<!--- 
    ColdFusion Specific Note: I handle Paging, Filtering and Ordering a bit different than some of the other server side versions
 --->
  
<!--- 
    Paging 
--->
<!--- 
    ColdFusion Specific Note: I am handling paging in the cfoutput statement instead of limit.   
 --->
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
 
<!--- 
    Filtering 
    NOTE: this does not match the built-in DataTables filtering which does it
        word by word on any field. It's possible to do here, but concerned about efficiency
        on very large tables, and MySQL's regex functionality is very limited
 --->
<!--- 
    ColdFusion Specific Note: 
    I am handling this in the actual query call, because i want the statement parameterized to avoid possible sql injection
 --->
<cfparam name="url.sSearch" default="" type="string" />
 
<!--- 
    Ordering
 --->
<cfparam name="url.iSortingCols" default="0" type="integer" />
 
<!--- 
    SQL queries
    Get data to display
 --->
 
<!--- Data set after filtering --->
<cfquery datasource="#coldfusionDatasource#" name="qFiltered">
    SELECT count(#listColumns#)
        FROM #sTableName# 
    <cfif len(trim(url.sSearch))>
        WHERE <cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></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>
 
<!--- Total data set length --->
<cfquery datasource="#coldfusionDatasource#" name="qCount">
    SELECT COUNT(#sIndexColumn#) as total
    FROM   #sTableName#
</cfquery>
 
<!--- 
    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><cfif thisColumn is "version"><cfif version eq 0>"-"<cfelse>"#jsStringFormat(version)#"</cfif><cfelse>"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"</cfif></cfloop>]
    </cfoutput> ] }
 

Open in new window



error

The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The count function requires 1 argument(s).
 
The error occurred in C:\inetpub\wwwroot\jdonato\csedny\cfData.cfm: line 79
77 :     </cfif>
78 :     <cfif url.iSortingCols gt 0>
79 :         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>
80 :     </cfif>
81 : </cfquery>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38854954
To solve that error, I think you need to look at the SQL that is created from the CF code.   It seems that the generation of the SQL code may have a "count()" function in there somewhere, which is missing a parameter.

If it's not in the order-by clause, then look at the SELECT clause
0
 

Author Comment

by:joedfuse
ID: 38854988
Here is the query in question... the count is supposed to use the list I defined at the top

<cfquery datasource="#coldfusionDatasource#" name="qFiltered" username="#request.dsnuLogin#" password="#request.dsnpLogin#">
    SELECT count(#listColumns#)
        FROM #sTableName# 
    <cfif len(trim(url.sSearch))>
        WHERE <cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></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>

Open in new window

0
 

Author Comment

by:joedfuse
ID: 38854997
Here is what the query looks like in the debugger

 SELECT count(firstname,lastname) FROM tblActivity

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38855008
Just saw your code, I suspect the issue is here

 SELECT count(#listColumns#)

When ListColumns has no value, you need to put in a default value, it can't be empty.  It could be just set to *
0
 

Author Comment

by:joedfuse
ID: 38855056
ok so I set listColumns to *

Now its throwing this error... I don't see anywhere in the code where I need a query string

Element SECHO is undefined in URL.
 

91 :  --->
92 : <cfcontent reset="Yes" />
93 : {"sEcho": <cfoutput>#val(url.sEcho)#</cfoutput>,
94 : "iTotalRecords": <cfoutput>#qCount.total#</cfoutput>,
95 : "iTotalDisplayRecords": <cfoutput>#qFiltered.recordCount#</cfoutput>,
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38855151
I don't see sEcho used anywhere in your code, if you're copying this from someplace, then perhaps they needed it for something that you don't.   In any case, it looks like it's just an ouput/display value so I would delete this line:

"sEcho": <cfoutput>#val(url.sEcho)#</cfoutput>,
0
 

Author Comment

by:joedfuse
ID: 38855158
Ok so I modified some code to combine some of both examples and I got rid of the secho error. Its gets to a a styled table see attached screen shot

It just sits at processing.... doesn't actually load anything I will post the new code for noth pages and the debugger output below. Thanks again I think we are getting there

cfdata_backend.cfm

<!--- table name --->
<cfset sTableName = "tblActivity" />
 
<!--- list of database columns which should be read and sent back to DataTables --->
<cfset listColumns = "*" />
 
<!--- Indexed column --->
<cfset sIndexColumn = "clientid" />
  
<!--- ColdFusion Datasource for the MySQL connection --->
<cfset coldfusionDatasource = "#request.dsnLogin#"/>
 
<!---
If you just want to use the basic configuration for DataTables with ColdFusion server-side, there is no need to edit below this line
 
Note: there is additional configuration below for the "version" column in query and output
 --->
 
<!---
    ColdFusion Specific Note: I handle Paging, Filtering and Ordering a bit different than some of the other server side versions
 --->
  
<!---
    Paging
--->
<!---
    ColdFusion Specific Note: I am handling paging in the cfoutput statement instead of limit.  
 --->
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
 
<!---
    Filtering
    NOTE: this does not match the built-in DataTables filtering which does it
        word by word on any field. It's possible to do here, but concerned about efficiency
        on very large tables, and MySQL's regex functionality is very limited
 --->
  
<!--- ColdFusion Specific Note: I am handling this in the actual query call, because i want the statement parameterized to avoid possible sql injection --->
<cfparam name="url.sSearch" default="" type="string" />
 
<!--- Ordering --->
<cfparam name="url.iSortingCols" default="0" type="integer" />
 
<!--- SQL queriesGet data to display --->
 
<!--- Data set after filtering --->
<cfquery datasource="#coldfusionDatasource#" name="qFiltered">
    SELECT count(#listColumns#)
        FROM #sTableName#
    <cfif len(trim(url.sSearch))>
        WHERE <cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></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>
 
<!--- Total data set length --->
<cfquery datasource="#coldfusionDatasource#" name="qCount">
    SELECT COUNT(#sIndexColumn#) as total
    FROM   #sTableName#
</cfquery>
 
<!--- 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><cfif thisColumn is "version"><cfif version eq 0>"-"<cfelse>"#jsStringFormat(version)#"</cfif><cfelse><cfif thisColumn eq "FirstName">"#jsStringFormat('<a href=test.cfm?ContactID=' & qFiltered["ContactID"][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>')#"<cfelse>"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"</cfif>
</cfif></cfloop>]
    </cfoutput> ] }

Open in new window


cfdata2.cfm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
         
        <title>DataTables example</title>
        <style type="text/css" title="currentStyle">
            @import "datatables/media/css/demo_page.css";
            @import "datatables/media/css/demo_table.css";
        </style>
        <script type="text/javascript" language="javascript" src="datatables/media/js/jquery.js"></script>
        <script type="text/javascript" language="javascript" src="datatables/media/js/jquery.dataTables.js"></script>
        <script type="text/javascript" charset="utf-8">
            $(document).ready(function() {
                $('#example').dataTable( {
                    "bProcessing": true,
                    "bServerSide": true,
                    "sAjaxSource": "cfdata_backend.cfm",
                    "iDisplayStart": 10
                } );
            } );
        </script>
    </head>
    <body id="dt_example">
        <div id="container">
            
             
            <h1>Live example</h1>
            <div id="dynamic">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
    <thead>
        <tr>
            <th width="10%">Client ID</th>
            <th width="10%">FIRST NAME</th>
            <th width="10%">LAST NAME</th>
          
        </tr>
    </thead>
    <tbody>
        <tr>
            <td colspan="5" class="dataTables_empty">Loading data from server</td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <th>Client Id</th>
            <th>First Name</th>
            <th>Last Name</th>
           
        </tr>
    </tfoot>
</table>
            </div>
             
             
            
        </div>
    </body>
</html>

Open in new window


debug out put
tableexample.jpg
0
 

Author Comment

by:joedfuse
ID: 38855180
The debugger isn't even showing any of the queries from these scripts
0
 

Author Comment

by:joedfuse
ID: 38855200
Removing the secho line leaves this error... I thing secho is required by the script to run through the data rows ... nto sure though

[Table (rows 1 columns COMPUTED_COLUMN_1): [COMPUTED_COLUMN_1: coldfusion.sql.QueryColumn@60274f7e] ] is not indexable by *

 
: line 108

106 :     <cfoutput query="qFiltered" startrow="#val(url.iDisplayStart+1)#" maxrows="#val(url.iDisplayLength)#">
107 :         <cfif currentRow gt (url.iDisplayStart+1)>,</cfif>
108 :         [<cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)>,</cfif><cfif thisColumn is "version"><cfif version eq 0>"-"<cfelse>"#jsStringFormat(version)#"</cfif><cfelse>"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"</cfif></cfloop>]
109 :     </cfoutput> ] }
110 :
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 38855350
By changing the value of listColumns to *, it messes up the where clause.

Look at the cfloop in the where clause, it loops the column list, which you have set to *   so then the where clause will be:   WHERE * = 'something'  

My comment before was to ensure that listColumns was not empty.  Change it to ensure it has a value that is one or more column names

<cfquery datasource="#coldfusionDatasource#" name="qFiltered">
    SELECT count(#listColumns#)
        FROM #sTableName#
    <cfif len(trim(url.sSearch))>
        WHERE <cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></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>

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:joedfuse
ID: 38857419
Ok so sorry about the confusion in all the code samples......

Look below code in the 2 pages I got it further than I have thus far... I think .... The attached file is the screen shot of the specific json error im getting

cfdata3.cfm

<cfsetting showdebugoutput="true">
<!-- DATATABLES CSS -->
<style type="text/css" title="currentStyle">
            @import "datatables/media/css/demo_page.css";
            @import "datatables/media/css/demo_table.css";
        </style>
        <script type="text/javascript" language="javascript" src="datatables/media/js/jquery.js"></script>
        <script type="text/javascript" language="javascript" src="datatables/media/js/jquery.dataTables.js"></script>
<script type="text/javascript"> 
    $(document).ready(function() {
        $('#formsTable').dataTable( {
            "bProcessing": true,
            "bServerSide": true,
            "bRetrieve": true,
            "sAjaxSource": "cfdata3_back.cfm",
            "sPaginationType": "full_numbers",
            "aaSorting": [[1,'asc']],
            "iDisplayStart": 10             
         });
    } );
</script> 



<!-- DATATABLES CSS END -->

                <h1 class="page-title">Forms</h1>
                <div class="container_12 clearfix leading">
                    <div class="grid_12">
                        <div id="allForms" class="clearfix"> 
							<div align="right" style="padding-right:15px;"><a href="#add_form.cfm"><img src="images/navicons/add_icon.jpg" title="Add Form"/></a></div>
                            <table class="display" id="formsTable"> 
                                <thead> 
                                    <tr>  
                                        <th width="25%">ClientID</th> 
                                        <th width="30%">first name</th> 
                                        <th width="20%">last name</th> 
										<th width="15%">Actions</th>
                                    </tr> 
                                </thead> 
								<tbody>
								    <tr><td colspan="4" class="dataTables_empty">Loading data from server</td></tr>
								</tbody>
                            </table> 
                        </div>
                        <br>
						<div align="right" style="padding-right:15px;"><a href="#add_form.cfm"><img src="images/navicons/add_icon.jpg" title="Add Form"/></a></div>  
                    </div>
                </div>

Open in new window


cfdata3_back.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 <a href="http://www.adobe.com/products/coldfusion/" target="_blank" rel="nofollow">http://www.adobe.com/products/coldfusion/</a>
        or try out the open source railo cfml engine from <a href="http://www.getrailo.org/" target="_blank" rel="nofollow">http://www.getrailo.org/</a> --->
<!--- Easy set variables --->
   
<!--- table name --->
<cfset sTableName = "tblActivity" /> 
<!--- list of database columns which should be read and sent back to DataTables --->
<cfset listColumns = "clientid,firstname,lastname" /> 
<!--- Indexed column --->
<cfset sIndexColumn = "clientid" />  
<!--- ColdFusion Datasource for the MySQL connection --->
<cfset coldfusionDatasource = "csedny"/>
  
<!--- If you just want to use the basic configuration for DataTables with ColdFusion server-side, there is no need to edit below this line
      Note: there is additional configuration below for the "version" column in query and output --->
  
<!--- ColdFusion Specific Note: I handle Paging, Filtering and Ordering a bit different than some of the other server side versions --->
  
<!--- Paging --->
<!--- ColdFusion Specific Note: I am handling paging in the cfoutput statement instead of limit. --->
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
  
<!--- Filtering
      NOTE: this does not match the built-in DataTables filtering which does it word by word on any field. It's possible to do here, 
        but concerned about efficiency on very large tables, and MySQL's regex functionality is very limited
      ColdFusion Specific Note:
        I am handling this in the actual query call, because i want the statement parameterized to avoid possible sql injection
 --->
<cfparam name="url.sSearch" default="" type="string" />
  
<!--- Ordering --->
<cfparam name="url.iSortingCols" default="0" type="integer" />
  
<!--- SQL queries Get data to display ---> 
<!--- Data set after filtering --->
<cfquery datasource="#coldfusionDatasource#" name="qFiltered">
    SELECT #listColumns# FROM #sTableName#
    <cfif len(trim(url.sSearch))>
        WHERE <cfloop list="#listColumns#" index="thisColumn"><cfif thisColumn neq listFirst(listColumns)> OR </cfif>#thisColumn# LIKE <cfif thisColumn is "version"><!--- special case ---><cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#val(url.sSearch)#" /><cfelse><cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(url.sSearch)#%" /></cfif></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>
  
<!--- Total data set length --->
<cfquery datasource="#coldfusionDatasource#" name="qCount">
    SELECT COUNT(#sIndexColumn#) as total FROM  #sTableName#
</cfquery>
  
<!--- 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>
			<cfif thisColumn is "version">
				<cfif version eq 0>
					"-"
				<cfelse>
					"#jsStringFormat(version)#"
				</cfif>
			<cfelse>
				"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"
			</cfif>
			<cfif thisColumn eq listlast(listcolumns)></cfif>"	</cfloop>,"#jsStringFormat("<a href=""##edit_form.cfm?id=#Evaluate(sIndexColumn)#""><img src=""icons/edit_icon.jpg"" height=""20px"" width=""20px"" title=""Edit Form""></a> <a href=""delete_form.cfm?id=#Evaluate(sIndexColumn)#""><img src=""images/delete_icon.jpg"" height=""20px"" width=""20px"" title=""Delete Form""></a>")#"]
    </cfoutput> ] }

Open in new window

tableexample.jpg
0
 

Author Comment

by:joedfuse
ID: 38857666
Ok so I think im getting even closer. This code was designed for a MySQL db. I used a test database in mysql

I added the SQL_CALC_FOUND_ROWS back in as it was original and I get no database erros. now it seems to be a json problem with the client code. the error im getting is

DataTables warning (table id = 'example'): DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error
here is the firebug info

_      1360108250385
bRegex      false
bRegex_0      false
bRegex_1      false
bRegex_2      false
bSearchable_0      true
bSearchable_1      true
bSearchable_2      true
bSortable_0      true
bSortable_1      true
bSortable_2      true
iColumns      3
iDisplayLength      10
iDisplayStart      10
iSortCol_0      0
iSortingCols      1
mDataProp_0      0
mDataProp_1      1
mDataProp_2      2
sColumns      
sEcho      1
sSearch      
sSearch_0      
sSearch_1      
sSearch_2      
sSortDir_0      asc

{"sEcho": 1,
"iTotalRecords": 200,
"iTotalDisplayRecords": 200,
"aaData": [
   
        ["<a href=test.cfm?t=11>11</a>"
,"<a href=test.cfm?t=ZERO>ZERO</a>"
,"<a href=test.cfm?t=CAGE>CAGE</a>"
]
    ,
        ["<a href=test.cfm?t=12>12</a>"
,"<a href=test.cfm?t=KARL>KARL</a>"
,"<a href=test.cfm?t=BERRY>BERRY</a>"
]
    ,
        ["<a href=test.cfm?t=13>13</a>"
,"<a href=test.cfm?t=UMA>UMA</a>"
,"<a href=test.cfm?t=WOOD>WOOD</a>"
]
    ,
        ["<a href=test.cfm?t=14>14</a>"
,"<a href=test.cfm?t=VIVIEN>VIVIEN</a>"
,"<a href=test.cfm?t=BERGEN>BERGEN</a>"
]
    ,
        ["<a href=test.cfm?t=15>15</a>"
,"<a href=test.cfm?t=CUBA>CUBA</a>"
,"<a href=test.cfm?t=OLIVIER>OLIVIER</a>"
]
    ,
        ["<a href=test.cfm?t=16>16</a>"
,"<a href=test.cfm?t=FRED>FRED</a>"
,"<a href=test.cfm?t=COSTNER>COSTNER</a>"
]
    ,
        ["<a href=test.cfm?t=17>17</a>"
,"<a href=test.cfm?t=HELEN>HELEN</a>"
,"<a href=test.cfm?t=VOIGHT>VOIGHT</a>"
]
    ,
        ["<a href=test.cfm?t=18>18</a>"
,"<a href=test.cfm?t=DAN>DAN</a>"
,"<a href=test.cfm?t=TORN>TORN</a>"
]
    ,
        ["<a href=test.cfm?t=19>19</a>"
,"<a href=test.cfm?t=BOB>BOB</a>"
,"<a href=test.cfm?t=FAWCETT>FAWCETT</a>"
]
    ,
        ["<a href=test.cfm?t=20>20</a>"
,"<a href=test.cfm?t=LUCILLE>LUCILLE</a>"
,"<a href=test.cfm?t=TRACY>TRACY</a>"
]
     ] }
0
 

Author Comment

by:joedfuse
ID: 38857742
Ok so I figured it out .... you have to turn coldfusion debugging off or it causes json error.

Now it works but really the problem is it works for MySQL .... how can I change my code to support MS SQL which is my original question
0
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 38858233
It does work with SQL Server and Oracle, What issues you are facing

I read the Whole Thread and i have even made it work with mysql, sql, oracle databases, Does not seems to break
0
 

Author Comment

by:joedfuse
ID: 38861894
When I change the query to point to the ms sql dsn I get

NetworkError: 500 Error Executing Database Query. - cfdata5_back.cfm?sEcho=1&iColumns=3&sColumns=&iDisplayStart=10&iDisplayLength=10&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&iSortCol_0=0&sSortDir_0=asc&iSortingCols=1&bSortable_0=true&bSortable_1=true&bSortable_2=true&_=1360190459689"
0
 

Author Comment

by:joedfuse
ID: 38863862
Ok I figured it out... I removed the sql_row_count part of the first sql query and it pulls from ms sql.

Now I am getting a jSon formatting error when I add a date to the listoffields. I will add the json formatting below

Also I noticed every value has a hyperlink. Only the client id should be a link. I will post the code below where this is added

output code

<!--- 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><cfif thisColumn is "version"><cfif version eq 0>"-"<cfelse>"#jsStringFormat(version)#"</cfif><cfelse>"#jsStringFormat('<a href=test.cfm?t=' & qFiltered[thisColumn][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>')#"
</cfif></cfloop>]
    </cfoutput> ] }

Open in new window


json response

{"sEcho": 1,
"iTotalRecords": 46742,
"iTotalDisplayRecords": 46742,
"aaData": [
   
        ["<a href=test.cfm?t=26860>26860</a>"
,"<a href=test.cfm?t=name1>name1</a>"
,"<a href=test.cfm?t=name1>name1</a>"
,"<a href=test.cfm?t={ts \'1965-05-05 00:00:00\'}>{ts \'1965-05-05 00:00:00\'}</a>"
]
    ,
        ["<a href=test.cfm?t=26861>26861</a>"
,"<a href=test.cfm?t=name2>name2</a>"
,"<a href=test.cfm?t=name2>name2</a>"
,"<a href=test.cfm?t={ts \'1964-08-17 00:00:00\'}>{ts \'1964-08-17 00:00:00\'}</a>"
]

     ] }
0
 

Author Comment

by:joedfuse
ID: 38865571
Hey there experts... Just checking if this issue has been abandoned. Should I post in a different category or something? Thanks
0
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 38867756
Hi,

This thing:

"#jsStringFormat('<a href=test.cfm?t=' & qFiltered[thisColumn][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>'

it is creating the href for all now you have to use <cfif condition here like this:

"#jsStringFormat('<cfif qFiltered[thisColumn] IS 'Name'><cfelse> <a href=test.cfm?t=' & qFiltered[thisColumn][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>'</cfif>

he Code above has errors but this is just a way of doing it. This way yuo can keep links for what they are needed and remove for those which are not needed.

Post your code after this change and we will see how it works

Regards
0
 

Author Comment

by:joedfuse
ID: 38867891
Thanks for the response. I used a little tweak on what you suggested and took care of the linking issues. I also got the date to work by changing the database type to datetime from date. My problem now is I have no idea how to format the date in the output to eliminate the 00:00:00 and make the date mm/dd/yyyy. I will post a copy and paste of the table results so you can see what the date looks like now.

Also, do you see any reason why when the page loads for first time it starts at page 2 of data ???

Thanks, see attached file for screen shot of table and the output code below

<!--- 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><cfif thisColumn is "version"><cfif version eq 0>"-"<cfelse>"#jsStringFormat(version)#"</cfif><cfelse><cfif thisColumn eq "clientID">"#jsStringFormat('<a href=editActivity.cfm?clientID=' & qFiltered["clientid"][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>')#"<cfelseif thisColumn eq "firstname">"#jsStringFormat('<a href=editClient.cfm?clientID=' & qFiltered["clientid"][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>')#"<cfelse>"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"</cfif>
</cfif></cfloop>]
    </cfoutput> ] }

Open in new window

tablescreenshot2.jpg
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38868459
You can use another CFIF statement to see if it's the date column and then wrap the variable in  dateFormat(xxxx,"m/d/yyyy")   where xxxx is the variable string, etc
0
 

Author Comment

by:joedfuse
ID: 38868527
I actually tried that but couldn't figure out the correct syntax with the integrated javascript. There must be something with the jsStringFormat im missing
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38868625
You have to put the dateFormat() function around the variable that displays the date only, you don't want to include the link or anything else.

Alternatively, you could format the date in your query using SQL syntax (depending on your database).  Then you just treat it like a string from then on..
0
 

Author Comment

by:joedfuse
ID: 38868857
Here is what I did... Im getting a 500 error with it saying string not closed.

<cfelseif thisColumn eq "dob">"#jsStringFormat('#dateFormat(' & qFiltered["dob"][qFiltered.currentRow] & ', 'mm/dd/yyyy')#"

Open in new window


Here is the full if statement

<cfif version eq 0>"-"<cfelse>"#jsStringFormat(version)#"</cfif><cfelse><cfif thisColumn eq "clientID">"#jsStringFormat('<a href=editActivity.cfm?clientID=' & qFiltered["clientid"][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>')#"<cfelseif thisColumn eq "firstname">"#jsStringFormat('<a href=editClient.cfm?clientID=' & qFiltered["clientid"][qFiltered.currentRow] & '>' & qFiltered[thisColumn][qFiltered.currentRow] & '</a>')#"<cfelseif thisColumn eq "dob">"#jsStringFormat('#dateFormat(' & qFiltered["dob"][qFiltered.currentRow] & ', 'mm/dd/yyyy')#"<cfelse>"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"</cfif>

Open in new window

0
 

Author Comment

by:joedfuse
ID: 38878317
Hello again,
I'm trying to close this up but still cant figure out how to format the date. For some reason the coldfusion I write in the cfelsif keep causing json formatting.

Any ideas?
0
 

Author Closing Comment

by:joedfuse
ID: 38889787
Ok I think This question got pretty fragmented. The expert helped me get to just about the right answer. I will repost the date thing in a clean post so we can just focus on that. Thanks a lot
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38893539

<cfset variables.fieldlist=form.sColumns>
...
SELECT #listColumns# FROM #sTableName#

That leaves your db vulnerable all kinds of sql injection. Never trust user input or use input in raw sql.  Validate it first.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 77
Jquery Typeahead - where is the source set? 1 15
gif overlay 3 32
ajax form sumbit 3 25
Introduction Chart.js, used properly, can visually add a difference to your charting applications. It engages your visitors and allows them to interact with data they otherwise wouldn't be able to without expensive and complicated systems. For this…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now