JSON validation error (Coldfusion)
JSON validation error (Coldfusion)
HELP! I keep getting the error message "DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error."
I have validated my JSON using http://www.jsonlint.com/ and its all ok. I am using jquery 1.4.2.
Here is the coldfusion "getdata" script
[code]
<!---
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 --->
<!--- list of database columns which should be read and sent back to DataTables --->
<!--- Indexed column --->
<!--- ColdFusion Datasource for the MySQL connection --->
<!--- 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. --->
<!--- 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
--->
<!--- Ordering --->
<!--- SQL queries Get data to display --->
<!--- Data set after filtering --->
SELECT #listColumns# FROM #sTableName#
WHERE OR #thisColumn# LIKE <!--- special case --->
ORDER BY , #listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# #url["sSortDir_"&thisS]#
<!--- Total data set length --->
SELECT COUNT(#sIndexColumn#) as total FROM #sTableName#
<!--- Output --->
{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [
,
[,"-""#jsStringFormat(version)#""#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#","#jsStringFormat("edit change owner")#"]
] }
[/code]
I have validated my JSON using http://www.jsonlint.com/ and its all ok. I am using jquery 1.4.2.
Here is the coldfusion "getdata" script
[code]
<!---
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 --->
<!--- list of database columns which should be read and sent back to DataTables --->
<!--- Indexed column --->
<!--- ColdFusion Datasource for the MySQL connection --->
<!--- 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. --->
<!--- 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
--->
<!--- Ordering --->
<!--- SQL queries Get data to display --->
<!--- Data set after filtering --->
SELECT #listColumns# FROM #sTableName#
WHERE OR #thisColumn# LIKE <!--- special case --->
ORDER BY , #listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# #url["sSortDir_"&thisS]#
<!--- Total data set length --->
SELECT COUNT(#sIndexColumn#) as total FROM #sTableName#
<!--- Output --->
{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [
,
[,"-""#jsStringFormat(version)#""#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#","#jsStringFormat("edit change owner")#"]
] }
[/code]
This discussion has been closed.
Replies
[code]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
UI/Asynchronous Examples
<!--[if IE 6]><![endif]-->
<!--[if IE 7]><![endif]-->
<!--[if lt IE 7.]><![endif]-->
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "getData.cfm",
"sPaginationType": "full_numbers",
"aaSorting": [[1,'asc']],
"iDisplayStart": 10
} );
} );
Database - Datagrid 1
1. Title
ID
LogTime
Target
Operation
TranslatedPath
SessionToken
ACTIONS
Loading data from server
ID
LogTime
Target
Operation
TranslatedPath
SessionToken
[/code]
I would greatly appreciate any help. thanks in advance
For the record, you don't have to disable debugging, but just add
to the top of your page you do not want debugging output to appear upon
I'm new to datatables and also a ColdFusion programmer. I tried to do what was stated above, but no data shows up and no JSON erorr occurs. Plus, i have no idea how to get the JSON to debug it.
This is my data script:
[code]
<!---
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 --->
<!--- list of database columns which should be read and sent back to DataTables --->
<!--- Indexed column --->
<!--- ColdFusion Datasource for the MySQL connection --->
<!--- 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. --->
<!--- 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
--->
<!--- Ordering --->
<!--- SQL queries Get data to display --->
<!--- Data set after filtering --->
SELECT #listColumns# FROM #sTableName#
WHERE OR #thisColumn# LIKE <!--- special case --->
ORDER BY , #listGetAt(listColumns,(url["iSortCol_"&thisS]+1))# #url["sSortDir_"&thisS]#
<!--- Total data set length --->
SELECT COUNT(#sIndexColumn#) as total FROM #sTableName#
<!--- Output --->
{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [
,
[
,
"-"
"#jsStringFormat(version)#"
"#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"
" ,"#jsStringFormat(" ")#"]
] }
[/code]
[code]
<!-- DATATABLES CSS -->
$(document).ready(function() {
$('#formsTable').dataTable( {
"bProcessing": true,
"bServerSide": true,
"bRetrieve": true,
"sAjaxSource": "form_data.cfm",
"sPaginationType": "full_numbers",
"aaSorting": [[1,'asc']],
"iDisplayStart": 10
});
} );
<!-- DATATABLES CSS END -->
Forms
Form Name
Rerefence
Date Created
Actions
Loading data from server
[/code]
Use Firebug :-)
I can't help much as I don't know ColdFusion, and this thread is more than a year old, so there might not be a huge amount of feedback available on it. But most certainly use Firebug to view the XHR (and thus the JSON response) and then use http://jsonlint.com to validate the return.
Allan