JSON, jQuery and ColdFusion
April 14, 2008
In the past I have used the excellent CFJSON to generate my JSON to use with jQuery. Having upgraded from ColdFusion MX to ColdFusion 8 I figured it was about time that I used the in-built capabilities of ColdFusion to generate the JSON I call from my webservice cfcs.
This post has been updated after some feedback from Ray Camden.
CFC Using CFJSON (CFMX)
<cfcomponent extends="json" output="false" hint="I return data as JSON using cfjson">
<cffunction name="GetProducts" output="true" access="remote" returntype="string">
<cfargument name="keyword" type="string" required="yes" />
<cfset var qryExample = QueryNew("id,title") />
<cfset var ndx = "" />
<cfloop from="1" to="2" index="ndx">
<cfset QueryAddRow(qryExample) />
<cfset QuerySetCell(qryExample, "id", ndx) />
<cfset QuerySetCell(qryExample, "title", RepeatString(Chr(64 + ndx), 3)) />
</cfloop>
<!---
In ColdFusion MX a cfc called directly returned wddx.
To stop this output directly instead of cfreturn and append cfabort
--->
<cfcontent reset="yes" /><cfoutput>#Trim(encode(qryExample))#</cfoutput><cfabort />
</cffunction>
</cfcomponent>
Client side Javascript (Using JQuery)
function GetProducts(){
$.getJSON(
'http://www.mydomain.com/cfc/MyWebservice.cfc?wsdl',
{ method : 'GetProducts' },
/*
When the JSON data has returned, fire this
callback function and pass in the JSON data
as it's argument.
*/
ShowProducts
);
}
function ShowProducts(qProducts){
// matches json.cfc implementation of json, not CF8 implementation...
// example:
// {"recordcount":2,"columnlist":"id,title","data":{"id":[1,2],"title":["AAA","BBB"]}}
if (qProducts.recordcount==0) {
alert('Sorry, no matches found');
}
else {
for (var i=0; i<qProducts.recordcount; i++) {
// loop through JSON recordset...
// we can reference the fields like this...
nId = qProducts.data.id[i];
sTitle = qProducts.data.title[i];
}
}
}
This all worked beautifully. So, now to convert the CFC so that it uses the new ColdFusion features to create the JSON.
Updated CFC (CF8)
<cfcomponent output="false" hint="I return data as CF native JSON">
<cffunction name="GetProducts" output="false" access="remote">
<cfset var qryExample = QueryNew("id,title") />
<cfset var ndx = "" />
<cfloop from="1" to="2" index="ndx">
<cfset QueryAddRow(qryExample) />
<cfset QuerySetCell(qryExample, "id", ndx) />
<cfset QuerySetCell(qryExample, "title", RepeatString(Chr(64 + ndx), 3)) />
</cfloop>
<cfreturn qryExample />
</cffunction>
</cfcomponent>
The bit that gave me the biggest headache was figuring out that you have to add returnformat=json and queryformat=column to your Javascript call or else you get some odd results.
Examples:
without returnformat=json returns:
<wddxPacket version='1.0'><header/><data><string>{"ROWCOUNT":2,"COLUMNS":["ID","TITLE"],"DATA":{"id":[1.0,2.0],"title":["AAA","BBB"]}}</string></data></wddxPacket>
with returnformat=json returns:
{"ROWCOUNT":2,"COLUMNS":["ID","TITLE"],"DATA":{"id":[1.0,2.0],"title":["AAA","BBB"]}}
There are a few more gotchas to look out for
- ColdFusion uses uppercase. So make sure that you update your JavaScript accordingly.
- The recordcount property that CFJSON returns needs to be replaced with ROWCOUNT
- The columnlist property that CFJSON returns needs to be replaced with COLUMNS
- If you are getting an "Invalid label" in FireBug, then you need to upgrade jQuery to version 1.2.3 or higher
So, my client side javascript now looks like this:
function GetProducts(){
$.getJSON(
'http://www.mydomain.com/cfc/MyWebservice.cfc?wsdl',
{ method : 'GetProducts', returnformat : 'json', queryformat : 'column' },
/*
When the JSON data has returned, fire this
callback function and pass in the JSON data
as it's argument.
*/
ShowProducts
);
}
function ShowProducts(qProducts){
// matches CF8 implementation of JSON...
// example:
// {"ROWCOUNT":2,"COLUMNS":["ID","TITLE"],"DATA":{"id":[1,2],"title":["AAA","BBB"]}}
if (qProducts.ROWCOUNT==0) {
alert('Sorry, no matches found');
}
else {
for (var i=0; i<qProducts.ROWCOUNT; i++) {
// loop through JSON recordset...
// we can reference the fields like this...
nId = qProducts.DATA.id[i];
sTitle = qProducts.DATA.title[i];
}
}
}
- Posted in:
- jQuery
- ColdFusion
22 comments
Leave a comment
If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)
1) "pre ColdFusion 8 a cfc called directly will always convert to wddx."
This isn't true. If your return type is XML, then in CF7, it returned the data as a plain string. It would not WDDX encode your XML. Not helpful for JSON, but I wanted to be clear here in case others read your post.
2) Why in the heck would you make the json yourself and use returnformat=plain? Why didn't you just cfreturn the query and use returnformat=json in your JavaScript? Change
'www.mydomain.com/cfc/MyWebservice.cfc?wsdl',
to
'www.mydomain.com/cfc/MyWebservice.cfc?method=getproducts&returnformat=json',
This would make your code quite a bit simpler.
3) You also don't need returnType. When using returnFormat=JSON, you can use queryFormat=row or queryFormat=column.
Comment by Raymond Camden – April 15, 2008
In answer to your points:
1) I did jump straight from MX to CF8 so I didn't know that about CF7.
2) I was trying to convert code that worked and tried to solve the problems in the CFC rather than in the Javascript. When I got it to work I'm afraid I just left it, but I concede that adding the &returnformat=json is certainly simpler (if only I'd know that!).
3) adding returnType to functions is a (good?) habit of mine.
I don't pretend to be an expert, I couldn't find anything online using jQuery with CF8, so when I got it to work I thought it would be useful to someone. With your comments, hopefully it is useful and accurate :)
Comment by John Whish – April 15, 2008
To be fair, it was NOT documented well. I mean it was documented, but you had to dig for it.
> 3) adding returnType to functions is a (good?) habit of mine.
Well in general, you want your CFC methods to be abstract and let the caller determine the format of the result. :)
Comment by Raymond Camden – April 15, 2008
Comment by Mark – April 16, 2008
Comment by Eric Knipp – May 28, 2008
Glad you found the article useful. Credit must also go to Raymond Camden for pointing out a few improvements to my original post!
Comment by John Whish – May 29, 2008
I'm at the same place you are. I'm a CF 8 user, jQuery user, and just getting my feet wet with AJAX.
I can run the AJAX / CFC query and use returnFormat="json" and get serialized data in my response without a problem.
I just don't know how to use the data once I've got it back on the calling page.
Based on your tutorial, it seems that I will have to just javascript to output the data. Is that correct?
Is there some way to put this javascript data back into ColdFusion form so I can use Cf code to access and output it to the page?
Thanks!
Comment by Rick Faircloth – February 04, 2009
As for how to use the data - that's up to your application.
Comment by Raymond Camden – February 04, 2009
docs.jquery.com/Ajax/jQuery.getJSON
You may also want to read up on the serializeArray jQuery method:
docs.jquery.com/Ajax/serializeArray
Comment by John Whish – February 04, 2009
I'll check out the jQuery docs on .getJSON and serialize.
Is using .getJSON going to be different from using .ajax and returnformat=json ?
Comment by Rick Faircloth – February 04, 2009
Yes, getJSON is different to .ajax and returnformat=json.
The returnformat=json bit is just to tell ColdFusion what format to return the data in. It doesn't tell jQuery what format the data will be in. If you are expecting JSON then use getJSON.
Comment by John Whish – February 04, 2009
I've got a problem with getting the ROWCOUNT property back...
I'm calling my CFC's as follows:
<cfajaxproxy cfc="#application.stApplicationData.sComponents#.cPlangroupData" jsclassname="jsPlangroupobj" />
<script language="Javascript">
var cfcPGAsAjax = new jsPlangroupobj();
var LoadPlangroups = function() {
var qJSPlangroups = cfcPGAsAjax.getJSPlangroups();
}
But I don't know how to add the returnformat and queryformat to this statement, therefore I don't get the ROWCOUNT property :/
Any suggestions?
Comment by Chris Pijl – May 11, 2009
There is a bit more info here:
livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_a-b_3.html#3984959
although it doesn't have any examples.
I hope that helps :)
Comment by John Whish – May 11, 2009
Well thanks to you I knew to search for 'setQueryFormat' and that helped a lot!
I just needed to add:
cfcPGAsAjax.setQueryFormat("column");
to my code and voilà! :)
Thanks a bundle!
Comment by Chris Pijl – May 12, 2009
Comment by John Whish – May 12, 2009
Thanks,
James
Comment by James white – June 17, 2009
If you're not getting ROWCOUNT then you probably aren't passing queryformat=column.
Comment by John Whish – June 18, 2009
Just to let you know I took your example for my blog po.chassay.net/blog/post.cfm/railo-jquery-json because Railo implement it finally like that.
Thanks
Comment by Pierre-Olivier Chassay – July 12, 2009
Comment by John Whish – July 13, 2009
the result i am getting back from my call is:
{"ROWCOUNT":1,"COLUMNS":["STAFFNAMEFIRST"],"DATA": {"STAFFNAMEFIRST":["Lexia"]}}
for the life of me, I cannot figure out how to get to the data for the stafffirstname (my full query returns much more, but i have simplified it so that I can figure it out)
I have tried all sorts of things, from what I have read here, I am lead to believe that using... "data.DATA.STAFFNAMEFIRST[0]" (without the quotes) but i keep getting "data.DATA is undefined" infirebug.
jquery I am trying to use with no luck:
$('#details p').text(data.DATA.STAFFNAMEFIRST[0]);
if I just use "data", it dsiplays the above result with no dramas.
jquery that works to dump the JSON:
$('#details p').text(data);
I am lost and ANY sort of help or pointers would be greatly appreciated.
Thanks again for the great article mate.
Comment by Sam Frysteen – February 03, 2010
www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/json-case-issue-between-cf8-and-cf9-252
If you're still stuck feel free to ping me on this email address :)
Comment by John Whish – February 03, 2010
Turns out I am an idiot and had a wrong setting in the "datatype" (I am using $.ajax).... I some how copy and pasted some random code there, so even though I was getting JSON back, jquery didn't think it was JSON and told me off - rightfully so.
I had already looked over the other post you sent me the link for (more great stuff by the way), funny thing is I am CF8 and all my JSON comes back in all CAPS.
Another thing while I have you though, I am having NO luck at all using $.getJSON using a cfc, or $.ajax using a cfc for that matter. I am having to use normal .cfm files to get my data from. any ideas why this would be?
I even tried copying and pasting your code from the link you send me, and still no luck getting it to work. Is there a setting in coldfusion or something that I might be missing? The same thing has happened with another persons example i tried to run on my server too. Just won't let me get anything back from cfc's.
Anyways... thanks again for your help and your time.
Sam.
Comment by Sam Frysteen – February 04, 2010