Server Processing with Coldfusion
Server Processing with Coldfusion
radicalease
Posts: 1Questions: 0Answers: 0
I have used DataTables within my Adobe Coldfusion projects for quite some time and really love the plugin. That being said, I've recently run into a bit of a problem. The DataTables documentation provides PHP code for a serverside service that handles the data processing for DataTables content serverside but in my research I can't seem to find a similar example in Coldfusion. I have some PHP knowledge so I procceeded to translate the PHP example into Coldfusion. I think it's pretty well correct, the one thing I'm not too sure about is the cfswitch at the bottom which in the PHP example is a function. I am currently getting the Javascript error "sData is undefined" in firebug. I have seen this error before and understand that it occurs when DataTables is applied to a table with no data. I have checked my query and it returns results so I am not quite sure what to do next.
Here's my work so far translating the server_processing.php file into coldfusion:
[code]
<!--- Paging --->
<!--- Ordering --->
<!--- 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
--->
SELECT firstName, lastName, clientID
FROM clients
#trim(sWhere)#
#trim(sOrder)#
#trim(sLimit)#
SELECT COUNT(clientid) AS fullCount
FROM clients
"sEcho": #sEcho#,
"iTotalRecords": #iTotal#,
"iTotalDisplayRecords": #iFilteredTotal#,
"aaData": [ '
[
"#clientID#",
"#lastName#, #firstName#"
],
']
#trim(sOutput)# #activeCol#
[/code]
Here's my work so far translating the server_processing.php file into coldfusion:
[code]
<!--- Paging --->
<!--- Ordering --->
<!--- 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
--->
SELECT firstName, lastName, clientID
FROM clients
#trim(sWhere)#
#trim(sOrder)#
#trim(sLimit)#
SELECT COUNT(clientid) AS fullCount
FROM clients
"sEcho": #sEcho#,
"iTotalRecords": #iTotal#,
"iTotalDisplayRecords": #iFilteredTotal#,
"aaData": [ '
[
"#clientID#",
"#lastName#, #firstName#"
],
']
#trim(sOutput)# #activeCol#
[/code]
This discussion has been closed.
Replies
Good stuff on the translation so far! I've never actually used ColdFusion, so I might be of more hindrance than help (!), but let's see how far we get ;-)
Firstly the error "sData is undefined" crops up when DataTables is trying to perform a sorting function on an invalid (or null) data type - this is usually caused by an incorrect number of columns or and invalid data type detection (i.e. trying to perform a string operation on a number). So the first thing to check would be that the data being send back is of the expected number of columns, and that it's valid data (jsonlint.com).
Secondly the function fnColumnToField() which I use in the PHP version is used to "translate" a column integer into an SQL column name (for example column 2 is sourced from the SQL table column "platform"). So yes, I think a switch would probably do the trick quite nicely here. I've only needed to use this function for the individual column filtering, so if you don't need that functionality, then you can probably just drop that. Otherwise, just making sure that the SQL statements are valid and correct would be the thing to try here.
Hope this helps - let us know how you get on!
Allan
have you figured out how to make the processing site correct in Coldfusion? I also need to translate this php code to Coldfusion but unfortunately I'm not an advanced user of CF:( It doesn't work for me at above version.
Could you please share working CF version? It would be great and very helpful. Many thanks in advance!
@Allan,
in above CF version, I receive such error from debugger: "500 Variable iSortCol_1 is undefined" when I fire the site (and the table is empty). A part of the request sent by main page is: "iDisplayStart = 0" ,when in your version it is "10"- is it correct? What might be wrong in the code regarding the errors? Thanks in advance for advice.
iDisplayStart is the point in the records at which the display should start displaying. So on the first page it will be 0, On the second it will be 10 (assuming 10 entry pagnation) etc. So I think this is okay - certainly my example is working okay.
Perhaps best to ask in a cold fusion forum.
Regards,
Allan
Is there a chance that I can reach radicalease to ask for any help? Thanks.
I've afraid that if radicalease doesn't reply to this thread then probably not (needless to say I won't give out any e-mail addresses or anything like that :-) ). As I say, I suspect a ColdFusion specialist forum will probably be your best bet.
If you do have any luck with it, perhaps you would be willing to share your fix. I'm looking to built up a library of server-side scripts to help people such as your server get started with DataTables in anything other than PHP... ( http://datatables.net/forums/comments.php?DiscussionID=937 ).
Regards,
Allan
let me know if you still need help.
Matt
In general, one MUST output data to JavaScript in CF using the function JSStringFormat(). Assuming your code is correct (I havenot scrutinized it), unescaped strings may crahs the script at runtime.
Hugo, CF specialist
Allan
Can you post the ColdFusion code, or a link to download your project
thx
Thank you so much for your help! I have been struggling with this for some time now. If I could just figure out how to slip a dynamic link in one or all of the cells (like www.mypage?ContactID=#ContactID#) I would be great. Being that the output section is looping through a list of columns I can't figure out both where to put an html like like above or how to pull the dynamic variable out of the query properly. Thank you for any advice you could give the above fnRender seems to complicated for me and my skill set :)
//////cfm file that works (url parameter page)/////
[code]
<!--- 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 queriesGet data to display --->
<!--- Data set after filtering --->
SELECT SQL_CALC_FOUND_ROWS #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])#"]
] }
[/code]
Can you also post your client side code, to see how do you parse the aaData?
[code]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
DataTables example
@import "../../media/css/demo_page.css";
@import "../../media/css/demo_table.css";
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/My-Coldfusion.cfm",
"iDisplayStart": 10
} );
} );
DataTables server-side processing example
Preamble
Live example
CONTACT ID
FIRST NAME
LAST NAME
ADDRESS
CITY
Loading data from server
Contact Id
First Name
Last Name
Address
City
DataTables © Allan Jardine 2008-2010
[/code]
[code] "#jsStringFormat(""""" & Filtered[thisColumn][qFiltered.currentRow] & """"")#" [/code]
[code]
<!--- Output --->
{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [
,
[,"-""#jsStringFormat(version)#"""#jsStringFormat(""""" & Filtered[thisColumn][qFiltered.currentRow] & """"")#"]
] }
[/code]
Try this instead, worked for me, modify for your code:
[code]"#jsStringFormat('' & myQuery[thisColumn][myQuery.currentRow] & '')#"[/code]
What I have so far that you have helped with (thank you so much again)
[code]
<!--- Output --->
{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [
,
[,"-""#jsStringFormat(version)#""#jsStringFormat('' & qFiltered[thisColumn][qFiltered.currentRow] & '')#"
]
] }
[/code]
[code]
<!--- Output --->
{"sEcho": #val(url.sEcho)#,
"iTotalRecords": #qCount.total#,
"iTotalDisplayRecords": #qFiltered.recordCount#,
"aaData": [
,
[,"-""#jsStringFormat(version)#""#jsStringFormat('' & qFiltered[thisColumn][qFiltered.currentRow] & '')#""#jsStringFormat(qFiltered[thisColumn][qFiltered.currentRow])#"
]
] }
[/code]
I can't thank you enough... thank you, thank you.
Would you be interested in doing a datatables using the "individual column filtering example" with server side coldfusion as you did above. I would be happy to pay you for the code or your time :)
Jason
SELECT DateDiff('d',STARTDATE, ENDDATE) AS DUEDAYS
will produce a response:
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'DUEDAYS'
Which would make the "Processing..." to show up but not effects be displayed