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

  1. ColdFusion uses uppercase. So make sure that you update your JavaScript accordingly.
  2. The recordcount property that CFJSON returns needs to be replaced with ROWCOUNT
  3. The columnlist property that CFJSON returns needs to be replaced with COLUMNS
  4. 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];
}
}
}

 


22 comments

  1. I haven't had my coffee yet, but I see some serious issues with this post.

    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
  2. Thanks for the comments Ray - feedback is always good!

    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
  3. > 1) I did jump straight from MX to CF8 so I didn't know that about CF7.

    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
  4. I would like to see an example of this using extJS 2

    Comment by Mark – April 16, 2008
  5. Thanks, this is a great article. I am just getting started with CF8/JSON and as a jQuery user really appreciated the easy intro.

    Comment by Eric Knipp – May 28, 2008
  6. Hi Eric,
    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
  7. Hi, John, and thanks for the tutorial.

    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
  8. If you use the jQuery getJSON method then jQuery will automatically convert the JSON to native JS data, which is, afaik, just running eval() on the string.

    As for how to use the data - that's up to your application.

    Comment by Raymond Camden – February 04, 2009
  9. @Rick. As Ray said, getJSON is doing all the work for me on the client side (look at the javascript GetProducts() function). Also check out the docs:
    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
  10. Thanks Ray and John...

    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
  11. Hi Rick,

    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
  12. Hi John,

    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" />

    &lt;script language=&quot;Javascript&quot;&gt;
      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
  13. Hi Chris, I don't use cfajaxproxy myself but the JavaScript proxy object that cfajaxproxy creates has some built in methods you can use. The one you want is setQueryFormat("column"), there is also a setReturnFormat() method although this is set to JSON by default.

    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
  14. Hi John,

    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
  15. Hi Chris, glad you've got it working. Always glad to help (if I can!)

    Comment by John Whish – May 12, 2009
  16. Good article. My problem is that I am having a hard time using the return values. If I use an alert to display the response I get back from the call to the CFC, I get data. However, when I try to use any of the values (i.e. ROWCOUNT, COLUMN, or DATA), the values are undefined. For instance, if I try alert(response.ROWCOUNT) or alert(response.ROWCOUNT[0]) I get an error or nothing. Any ideas on where I can go from here?

    Thanks,

    James

    Comment by James white – June 17, 2009
  17. Hi James, if you're not using Firebug (an add-on for Firefox) then I recommend you check it out as it will really help you when working with JSON. getfirebug.com/

    If you're not getting ROWCOUNT then you probably aren't passing queryformat=column.

    Comment by John Whish – June 18, 2009
  18. Hi John,
    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
  19. Hey Pierre - thanks for the credit, I appreciate it. Nice to hear that Railo has added support for this.

    Comment by John Whish – July 13, 2009
  20. Great article... helped out heaps... BUT.... I am having dramas trying to dig the results out of my JSON.

    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
  21. Hi Sam, bit hard to tell what the problem is without looking at the code. It may be that the returned JSON has some whitespace or something? I did more recent post on CF/AJAX which you can read here which might help:
    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
  22. Thanks for your help John,
    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

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.