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.

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.

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.

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

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.

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

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!

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?

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

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>

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.

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>

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

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

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?

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.

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.

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.

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

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.

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

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

I'm sure you could.