Code from ColdFusion Boot Camp, and Tip about ColdFusion 8 and JSON

coldfusion (This post is more than 2 years old.)

I just gave a quick CF Boot Camp at MAX on using ColdFusion to send data to Spry. It covers going from static to CF6, CF7, and CF8 dynamic data. I've zipped up the code below. Note that it makes use of a database you won't have (I'll try to use the art gallery from now on) and I've removed the personal pictures of my kids.

So during the boot camp I talk about how cool it is that you can now call a CFC and ask for JSON back. No need for my toXML .cfc anymore. No need for a 'proxy' CFC. You just call your CFC and get your data back. Sweet.

This is cool but as I've blogged before, there are two ways a query can be represented in JSON:

  1. An array of column names and an array of data arrays.
  2. A object with a value for the number of rows, an array of column names, and an object where each key is a column name and the value is an array of data.

These two options return different types of data and impact how you would use them in Spry. The SerializeJSON function lets you toggle these two options. However - when using returnFormat="JSON" in a CFC call - I wasn't aware of how to toggle this. Todd Sharp pointed out to me that another new URL parameter you can pass to a cfc is:

queryFormat

This takes two values, row and column. The row version matches option 1 above, and column matches option 2. From the zip, here is how I called my CFC from Spry:

var mydata = new Spry.Data.JSONDataSet("/presentations/cfspry/product.cfc?method=getProducts&returnFormat=json&queryFormat=column", {path:"DATA", pathIsObjectOfArrays:true}); </script>

Download attached file.

Hire Me!

I'm currently looking for my next role in developer evangelism and advocacy. I have a long history of helping companies work with developers and love to write, create demos, and present at conferences. You can find my resume to learn more and drop me an email (raymondcamden@gmail.com) to reach out.

Support this Content!

If you like this content, please consider supporting me. You can become a Patron, visit my Amazon wishlist, or buy me a coffee! Any support helps!

Want to get a copy of every new post? Use the form below to sign up for my newsletter.

Archived Comments

Comment 1 by Justin posted on 10/4/2007 at 6:53 AM

Hey Ray,

What a coincidence, I've been trying to get JSON working with a few problems. The XML version is working fine.

<cfsprydataset name="pubs" type="json" bind="CFC:pubs.getAllPubs()"> (function returns a query)

My spry region isn't putting out any of the data and I'm not getting any errors.

Isn't cfsprydataset supposed to handle all of the conversion of coldfusion objects to JSON SPRY datasets automatically?

I just tried using your code and got a SPRY error:
Caught exception in JSONDataSet.loadDataIntoDataSet: SyntaxError: invalid flag after regular expression

Hope you are living every geeks dream.

Comment 2 by Justin posted on 10/4/2007 at 7:01 AM

Hmm, after playing with that URL queryFormat=column vs. row and looking at the JSON string I see some /r/n characters coming from the Database.

Crap, that might be the problem.

Comment 3 by Justin posted on 10/4/2007 at 8:06 AM

On a final note.

AHHHHHHHHHHHHHHHHHHHHHHHHHHHH

ColdFusion debugging was messing up my cfc call results! Perhaps this is well known, but I didn't realize that the json string continued into the debugging output. Bleh. Oh, even after turning it off, without 'whitespace management' enabled, the /r/r/r/r/r/r at the top of the page was messing it up (firebug showed that).

Anyhow, if it weren't for you giving me motivation to play with the URL params, I never would have got it. Well, that and firebug.

Comment 4 by Raymond Camden posted on 10/4/2007 at 4:46 PM

If your cfc method has output=false, you should get no whitespace unless you add some yourself, by doing like <cfoutput> space </cfoutput>

Comment 5 by Justin posted on 10/4/2007 at 5:18 PM

I didn't have whitespace management turned on in my DEV box.

I'd really like to see a demo of using cfsprydataset that uses JSON and acceses a Query.

Although I see the the data is being returned just fine, because it always uses the default of queryFormat=row, I'm not sure how I can actually use the data in a spry region unless I manipulate it in JS first. This seems wrong since the tag is supposed to simplify things.

Comment 6 by Raymond Camden posted on 10/5/2007 at 8:03 PM

So as we discussed on cf-talk, you can't use CFC: for your bind, you must use URL:

Comment 7 by Justin posted on 10/5/2007 at 8:33 PM

Ray,

Could you verify one thing for me? Is Spry assigning your rows rowIDs (ds_RowID)? All of my rows are coming out with ds_RowIDs of zero.

When using this style that you provided above:
var mydata = new Spry.Data.JSONDataSet("/presentations/cfspry/product.cfc?method=getProducts&returnFormat=json&queryFormat=column",
{path:"DATA", pathIsObjectOfArrays:true});

Thanks!

Comment 8 by Raymond Camden posted on 10/5/2007 at 11:25 PM

I don't have my laptop in front of me so I can't test the code I wrote earlier. Can I see your code onlne somewhere?

Comment 9 by Justin posted on 10/6/2007 at 12:53 AM

Sorry, it's all privatized. Take your time though, I won't be looking at it again until Tuesday.

Comment 10 by Thomas posted on 10/31/2007 at 6:37 PM

Ray, I'm lost with this stuff. Trying to get a return from a real basic cfc that requires a db name, username and password.

I tried the regular
Spry.Data.XMLDataSet

and the
Spry.Data.JSONDataSet

but cant get any data from the cfc

<cfcomponent>
<cffunction name="UserFaves" access="remote" returntype="query">
<cfargument name="DBname" required="yes">
<cfargument name="DBuser" required="yes">
<cfargument name="DBpass" required="yes">
<cfargument name="DBsub" required="yes">
<cfset var qCurrentFaves="">
<cfquery name="qCurrentFaves"
datasource="#DBname#"
username="#DBuser#"
password="#DBpass#"
cachedwithin="#CreateTimeSpan(0,0,0,0)#">
SELECT Title, URL
from #DBsub#.QuickLinks

</cfquery>
<cfreturn qCurrentFaves>
</cffunction>
</cfcomponent>

Comment 11 by Raymond Camden posted on 10/31/2007 at 7:06 PM

Well first off - if you want to use a CFC method that does NOT return XML, you MUST use CF8, and returnFormat=json. As that will tell CF to convert your query to JSON. First change to that. In my zip, the 4th example is the one you want to look at.

Comment 12 by Michael Damian posted on 4/29/2008 at 3:29 PM

Raymond, please help. I love (and need) spry, but it seems to have some bad habits or maybe it's me. Adapting your Dynamic4 example to my own requirements, I get the error:

Caught exception in JSONDataSet.loadDataIntoDataSet: [object Error]

My code is below.

It's weird cos I built other master details using JSON and, at first, it output some db rows but not others (as if it didn't like what was in certain fields), then suddenly it output nothing and gave the above error. So, I tried your example, and the error repeated. I'm pretty sure I haven't changed any settings or anything that might be causing the weirdness.

jsoncfc.cfm
-----------
<html>
<head>
<title>Cars</title>
<script type="text/javascript" src="../SpryAssets/includes/xpath.js" ></script>
<script type="text/javascript" src="../SpryAssets/includes/SpryData.js"></script>
<script type="text/javascript" src="../SpryAssets/includes/SpryJSONDataSet.js"></script>

<script type="text/javascript">
var mydata = new Spry.Data.JSONDataSet("cars.cfc?method=getCars&returnFormat=json&queryFormat=column",
{path:"DATA", pathIsObjectOfArrays:true});
mydata.setColumnType("picFront","image");
</script>
</head>

<body>

<div spry:region="mydata">

<table>
<tr>
<th onClick="mydata.sort('make','toggle');" style="cursor: pointer;">Make</th>
<th onClick="mydata.sort('model','toggle');" style="cursor: pointer;">Model</th>
<th onClick="mydata.sort('year','toggle');" style="cursor: pointer;">Year</th>
</tr>
<tr spry:repeat="mydata" onClick="mydata.setCurrentRow('{ds_RowID}');" spry:select="red" spry:hover="hot">
<td style="cursor: pointer;">{make}</td>
<td style="cursor: pointer;">{model}</td>
<td style="cursor: pointer;">{year}</td>
</tr>
</table>
</p>
</div>

<span spry:detailregion="mydata">
<h2>{make}</h2>
Model: {model}<br>
Year: {year}<br>
Description:<br>
{comments}
</span>

</body>
</html>

cars.cfc
=========

<cfcomponent output="false">

<cffunction name="getCars" returnType="query" access="remote" output="false">
<cfset var q = "">

<cfquery name="q" datasource="#dsn#">
select *
from cars
order by make asc
</cfquery>

<cfreturn q>
</cffunction>

</cfcomponent>

Comment 13 by Raymond Camden posted on 4/29/2008 at 5:23 PM

Not to sound like a broken record - but have you tested with Firefox to see what the response really was?

Comment 14 by Michael Damian posted on 4/30/2008 at 1:38 PM

I think I have Justin's issues. It all sounds exactly the same. Pain in the ass.

Comment 15 by Michael Damian posted on 4/30/2008 at 1:57 PM

I, too, have the \r\n\r\n's in the JSON output. I can't figure out from comments whether this is an issue or not. What exactly is not allowed to be returned in the JSON output?

Comment 16 by Tankerbell posted on 5/16/2008 at 1:04 AM

FYI...I had the same error as Michael:

Caught exception in JSONDataSet.loadDataIntoDataSet: [object Error]

I got the error in both IE and Firefox. Turns out I had my workstation signed up for Debugging in CF Administrator. As soon as I removed my IP from the "Debugging IP Addresses" list it worked.

Comment 17 by Tankerbell posted on 5/16/2008 at 9:43 PM

Another FYI...I got the second issue too. The data was being returned (I could see it in Firefox), but it was not displaying. The table was expanding to the 200 rows from my database, but they where blank rows. Turns out that the variables are case sensitive. I changed all the field names from lower to upper case and the data displayed.

So here's what I did to make this work:
Used the dynamic4 example.
Re-pathed the links to the spry js files.
Re-pathed the URL in the Spry.Data.JSONDataset line (pointed to where I had put the product.cfc).
Changed the getProducts query to point to a database I had (just changed the cfquery SQL statements).
Changed all the variable references to match the fields from my database (for example changed {name} to {SERIALNUM}). Note the upper case here, if I used lower case I got a table with blanks.
Make sure that CF debugging is off.

Comment 18 by Chad Fraser posted on 6/17/2008 at 1:12 AM

Just a follow up for "Tankerbell"...

In your spry region be sure to use ALL CAPS when naming your columns or your data will not be displayed:

INSTEAD OF {name} use {NAME} etc..

JSON must by default generate column names in ALL CAPS.

Comment 19 by Raymond Camden posted on 6/17/2008 at 1:14 AM

@Chad: Note that it depends on how you generate your JSON.

Comment 20 by Chad Fraser posted on 6/17/2008 at 2:06 AM

This may help the cfsprydataset (CF8) question for JSON:

<cfsprydataset name="mydata" type="JSON"
bind="URL:/presentations/cfspry/product.cfc?method=getProducts&returnFormat=json&queryFormat=column"
options='{path:"DATA", pathIsObjectOfArrays: "true"}'>

Hope it helps.

Comment 21 by Scott Jibben posted on 3/21/2010 at 6:33 AM

Append this "&_cf_nodebug=true" to your bind URL to eliminate the debugging output.

Comment 22 by Phillip Senn posted on 9/7/2011 at 11:07 PM

I think you can put cfparam name="url.queryformat" default="column" at the top of your cfc, just like you can with url.returnformat.

Comment 23 by Raymond Camden posted on 9/7/2011 at 11:14 PM

I'm sure you could.