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];
    }
  }
}

 

6 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

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.