Yesterday in the ColdFusion chat room someone asked a question (a technical question at that - guess how rare that is?) about how they could use a JSON-encoded query. I whipped up a quick example that I thought others might like as well. This example does not use Ajax to load the JSON data - which is also pretty rare - but I wanted something that I could run all in one file. So here we go...
The first thing I did was get a query of data and serialize it using SerializeJSON:
<cfquery name="getstuff" datasource="blogdev" maxrows="5">
select id, title, posted
from tblblogentries
</cfquery>
<cfset jsondata = serializeJSON(getstuff)>
At this point, jsondata is a string. Here is what it looked like:
{"COLUMNS":["ID","TITLE","POSTED"],"DATA":[["905D9689-0130-1A16-62272F586A771C0C","mmm","May, 15 2007 10:31:00"],["905DAAED-E300-3B9D-7E25E43985CA9507","nn","May, 15 2007 10:31:00"],["905DE931-B8DB-B1AD-F77FC505851C2E9A","j","May, 15 2007 10:31:00"],["905E0C6A-99B8-97EC-4862D9064B9EC659","mmmmm","May, 15 2007 10:31:00"],["905E5910-0E2D-C566-5DCD39E6FD48ED06","NUMBER 11","May, 15 2007 10:32:00"]]}
I wanted to work with this on the client side (the whole point of this entry), so I needed to set this data to a JavaScript variable. The cool thing about JSON is that it can be evaled (think of this like ColdFusion's evaluate function) directly to a variable, so I used this code to assign it:
<script>
mydata = eval(<cfoutput>#jsondata#</cfoutput>)
For my demo, I built a quick form with a button. The idea is that you would hit the button, and I'd then run code that would loop over the query and display the contents. Here is the form I used and the 'area' I would use for output:
<input type="button" value="Show Data" onClick="showData()">
<div id="content" />
Ok, nothing complex yet. Now let's take a look at showData():
function showData() {
var output = document.getElementById('content');
var colMap = new Object();
//first - find my columns
for(var i = 0; i < mydata.COLUMNS.length; i++) {
colMap[mydata.COLUMNS[i]] = i;
}
for(var i = 0; i < mydata.DATA.length; i++) {
var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
output.innerHTML += str;
}
}
The first line of code simply creates a pointer to my div. I'll be writing out to that later. Now I need to loop over my query. The question is - how? If you look at the JSON string I output earlier, you will see that it is an object with two main properties - columns and data. The columns property is a list of columns. The data property contains my rows of data. Note though that it isn't indexed by column names. Instead - the first item in the first row of data matches the first column name. So what I need to do is figure out what my columns are. To do this I create a column map - i.e., a mapping of columns to indexes. The first FOR loop does this.
Once I have that - then it is a trivial matter to loop over my rows of data and pick the values I need. So for example, to get the title for row i, I use:
mydata.DATA[i][colMap["TITLE"]]
Make sense? I'll include the full source of the code below, but before I do, a few notes:
- The SerializeJSON function takes an optional second argument that is only used for queries. If set to true, the structure is pretty different than what I described above. I'll blog about that later today. (Someone will probably need to remind me.)
- While SerializeJSON is new to ColdFusion 8, ColdFusion 7 introduced the toScript function, which is another way to go from a ColdFusion variable to a JavaScript variable. (It is actually simpler than what I used above.)
Now for the complete template:
<cfquery name="getstuff" datasource="blogdev" maxrows="5">
select id, title, posted
from tblblogentries
</cfquery>
<cfset jsondata = serializeJSON(getstuff)>
<script>
mydata = eval(<cfoutput>#jsondata#</cfoutput>)
function showData() {
var output = document.getElementById('content');
var colMap = new Object();
//first - find my columnsco
for(var i = 0; i < mydata.COLUMNS.length; i++) {
colMap[mydata.COLUMNS[i]] = i;
}
for(var i = 0; i < mydata.DATA.length; i++) {
var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
output.innerHTML += str;
}
}
</script>
<input type="button" value="Show Data" onClick="showData()">
<div id="content" />
Archived Comments
I am so glad to see Coldfusion embrace JSON. It is my preferred format of choice for data in JS. Can't wait to start using some of these features more regularly.
I would also like to add that this might not be that benefical to CF8, but for those wanting to have some JSON in there previous CF instances.
CFJsonService on RiaForge is worth a look at too.
I like the data rendering speed aspect of the SerializeJSON function, however, I'm concerned about business logic implementation within the data output stream with this method, the js code would be a mess to read vs. cf code for the data output part.
Don Li - this example was a mash up so I could get everything on one page. Normally you would have a page request the JSON via an Ajax request.
How do you reformat the date to something any normal person would want to see, e.g. mm/dd/yyy?
You can do it in JS, but it can be a pain. I'd just do it server side with dateFormat.
Thanks. Could we get a quick snippet of that in action please?
dateformat? Please see the CF reference. It's pretty easy.
Yes, we all know how easy dateformat is to use. But how do you weld it into this JSON pallava. Just a quick example would do wonders and gain my utmost gratitude. Come on, it won't kill you:-)
But that's the thing - it doesn't need a quick example. If your query is being serialized into json, you would just use dateFormat on the query -before- your serialize it. Really - dateFormat is _extremely_ simple.
Yes, Raymond, dateformat is easy for simple date outputs in a different format sans JSON. Anyway, I've figured out how to weld it into your JSON example. Will post some code shortly for others needing an example.
Be sure to post a url here. I think I may be missing something as I don't see the need - so once you blog it will probably make more sense to me. :)
Is it possible to serialize a query to JSON and then deserialize it back to a query object again?
Yes, it is possible. When you deserialize it though, ensure strictMapping (second optional arg) is set to false.
Ah... nice.
Here is an example that worked:
<cfset myQuery = QueryNew("ID,Name","INTEGER,VARCHAR")>
<cfset temp = QueryAddRow(myQuery)>
<cfset temp = QuerySetCell(myQuery,"ID","1")>
<cfset temp = QuerySetCell(myQuery,"Name","Dave")>
<cfset myJSON = SerializeJSON(myquery)>
<cfset myNewResult = DeSerializeJSON(myJSON,false)>
<cfdump var="#myNewResult#">
Ray,
You mention at the top of this blog entry that this is a non-ajax example.
This helped open up a world of options for writing custom ajax renderers and validators and I just want to share one 'gotcha' I ran into after wresting with the AJAX version of this for a while.
When returning the JSON object from an AJAX call to a cfc, <cfreturn SerializeJSON(...) did not return the data in a format I needed for a column. The answer was to use returnformat="JSON".
After that subtle difference, I could parse the JSON string with a column map.
Here's a snippet of sample code from the javascript side:
var grid = ColdFusion.Grid.getGridObject("busforecast");
var gridFoot = grid.getView().getFooterPanel(true);
var gridFoot = grid.getView().getFooterPanel().setVisible(true); //false to remove paging TMS 9626 LLee 12/24/08
var ds = grid.getDataSource();
var forecastObj = new CFCs.ajaxForecastDAO;
<cfoutput>
var getBusmgrSummaryForecastAJAX = forecastObj.getBusmgrSummaryForecastAJAX('#dsn#','#curFY#',#S_OID#, '#qryBusmgrInitialForecast.forecast_level#',1,1);
</cfoutput>
var mydata = getBusmgrSummaryForecastAJAX;
var colMap = new Object();
//find my columns
for(var i = 0; i < getBusmgrSummaryForecastAJAX.COLUMNS.length; i++) {
colMap[getBusmgrSummaryForecastAJAX.COLUMNS[i]] = i;
}
for(var i = 0; i < getBusmgrSummaryForecastAJAX.DATA.length; i++) {
var prioractual = getBusmgrSummaryForecastAJAX.DATA[i][colMap["PRIORACTUAL"]];
var priorcert = getBusmgrSummaryForecastAJAX.DATA[i][colMap["PRIORCERT"]];
var estimatedusage = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ESTIMATEDUSAGE"]];
var actualusage = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ACTUALUSAGE"]];
var fullyearprojection = getBusmgrSummaryForecastAJAX.DATA[i][colMap["FULLYEARPROJECTION"]];
var acctmgrforecast = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ACCTMGRFORECAST"]];
var forecast = getBusmgrSummaryForecastAJAX.DATA[i][colMap["FORECAST"]];
//later in the script I add the var values to a 2nd footer of the grid using Ext.DomHelper.append..
//On the cfc side just use the usual access="remote" returntype="any" but be sure to add returnformat="JSON".
Hi,
I am using a dynamic java script table which accepts data in json format....I was able to loop through the query object in cfm code and then arrange my data in json like format.......I didnt loop through the object in javavscript nor did I used serialisejson.But my problem is for pagination I am unable to fetch the correct data.Since I am using inline JavaScript and then on click for pagination I am calling the same page but this JavaScript table is not loaded at all........I cannot write this code in external js as I need to use the cold fusion variable in the javascript function....Please advise
That doesn't really make much sense to me. I guess if I could see it it would.
Hi ,
I ahve modigied my code.
Please have a look at the below code.
Initially I am showing 30 records.
I used serializejson in an inline javascript.
<cfparam name="mxrow" default="30">
<cfquery name="myquery" datasource="testdsn" maxrows=#mxrow#>
select * from emp
</cfquery>
<cfoutput>
<script>
var getQuery=#serializejson(myquery) #;
alert(getQuery);
//I plan to write a java script table here below
dynamicTableLoad();
</script>
</cfoutput>
Alert is giving me the correct set of values for the query.
Now if wish to seethe next 30 records i.e from 31 to 60,
I intend to write a JavaScript function which again calls the same page through ajax.
But on ajax call the javascript is not loaded at all.
I'm sorry - I just don't quite get what you are doing here. Your front end code should be making a request to load data to the server. The server returns the data serialized in JSON. It should include information about the total # of results and the current page of results. Your code there isn't enough, nor should it be running any actual JS. It should just return JSON.
Another quick way is to run the response through an eval function and then parse it according to the column names.
For e.g.
var objdata = eval('('+responseFromServer+')');
(where responseFromServer is the JSON serialized query object in the cfc file)
Now, objdata.DATA.[column_name] would return the respective data.
For e.g. objdata.DATA.INTUSERID, objdata.DATA.STRFIRSTNAME etc (CAPS are essential since JSON in ColdFusion converts the names to all caps!)
Please note: This works only when you set the 'serializeQueryByColumns' in the SerializeJSON to "TRUE".
would you plz explain what the following line is doing (inside the first for look). To me it looks like its assigning int to an object.
colMap[myData.COLUMNS[i]]=i;
The variable myData.COLUMNS[i] is one column. It is a string, like Name. i is the index.
What this does is create a mapping by where the _name_ of the column has a value of the index.
I can then use that to get data by name and not index.
So imagine that AGE was the 3rd column. Instead of doing
data[3]
I can do
data[colMap.AGE]
Make sense?
We wrote a wrapper service to handle date formatting when converting query results to JSON string. Instead of using serializeJSON() function, we have a custom encode()function that will fix that dates is isDate(value) is true. Loop over the values and format them with a private formatting function - something like this:
private any function format(val) {
var value = arguments.val;
// escape certain characters in strings...
value = replace(value,'\','\\','all');
value = replace(value,'"','\"',"all");
value = replace(value,'/','\/',"all");
value = replace(value,"#chr(13)#","\r","all");
value = replace(value,"#chr(10)#","\n","all");
value = replace(value,"#chr(9)#","\t","all");
var newvalue = '"#value#"';
if (isNumeric(value)) {
if (variables.quotedNumbers == false) {
newvalue = value;
}
}
//if (value == 0 || value == 1) {
// newvalue = value;
// }
if (value == 'yes' || value == 'no' || value == 'true' || value == 'false' ) {
if (variables.convertBoolean == true) {
if (value == 'yes') { newvalue = true;}
if (value == 'no') { newalue = false; }
}
else {
if (value != 1 && value != 0 ) {
newvalue = '"#value#"';
}
}
}
if (isDate(value) && isNumeric(value) == 'NO' && variables.fixDates == true) {
newvalue = '"#dateFormat(value,"mmmm dd, yyyy ") & timeFormat(value,"hh:mm:ss")#"';
}
return newvalue;
}
Hi Raymond. I know you wrote this article 9 years ago, but it is still doing good work to this day. I wanted to thank you. For months, literally, I've been trying to figure out how to use AJAX to get data from a ColdFusion server and process it in the browser in JavaScript. Sounds like it should be so easy, and yet, for some reason it isn't. Or wasn't, until I read your article.
The one stumbling block for me, after getting rid of whitespace problems first, is that my AJAX call returned a string that looked exactly like what you started with in your article (in terms of structure). The problem, of course, was that what I got back wasn't a complex JavaScript object, it was a simple JSON string:
{"COLUMNS":["JID","NAME"],"DATA":[[13,"B2I"],[14,"B2II"],[15,"B2III"]]}
To get it from string to object was simply the use of JSON.parse().
Here is how I adapted your article to my AJAX call:
I just thought I'd post in case there is someone else out there who, like me, has been having trouble figuring out how to actually make that AJAX call and then do something with it.
If anyone is interested, I can leave another comment showing the server side of it too, since getting rid of that excess whitespace can be tricky.
In any case, thanks Raymond. Your words of 9 years ago are still alive and teaching today!
I'm happy this is still useful for you. :)
Glad this helped - I see it is a bit ugly formatting wise - going to fix now.
Almost 11 years later, and I too have found this article very helpful. A nice, simple explanation. Thank you.
Happy to help!