Posted in JavaScript, ColdFusion | Posted on 09-20-2007 | 18,775 views
Yesterday in the ColdFusion chat room someone asked a question (a technical question at that - guess how rare that is?) about how they could use a JSON-encoded query. I whipped up a quick example that I thought others might like as well. This example does not use Ajax to load the JSON data - which is also pretty rare - but I wanted something that I could run all in one file. So here we go...
The first thing I did was get a query of data and serialize it using SerializeJSON:
2select id, title, posted
3from tblblogentries
4</cfquery>
5
6<cfset jsondata = serializeJSON(getstuff)>
At this point, jsondata is a string. Here is what it looked like:
{"COLUMNS":["ID","TITLE","POSTED"],"DATA":[["905D9689-0130-1A16-62272F586A771C0C","mmm","May, 15 2007 10:31:00"],["905DAAED-E300-3B9D-7E25E43985CA9507","nn","May, 15 2007 10:31:00"],["905DE931-B8DB-B1AD-F77FC505851C2E9A","j","May, 15 2007 10:31:00"],["905E0C6A-99B8-97EC-4862D9064B9EC659","mmmmm","May, 15 2007 10:31:00"],["905E5910-0E2D-C566-5DCD39E6FD48ED06","NUMBER 11","May, 15 2007 10:32:00"]]}
I wanted to work with this on the client side (the whole point of this entry), so I needed to set this data to a JavaScript variable. The cool thing about JSON is that it can be evaled (think of this like ColdFusion's evaluate function) directly to a variable, so I used this code to assign it:
2mydata = eval(<cfoutput>#jsondata#</cfoutput>)
For my demo, I built a quick form with a button. The idea is that you would hit the button, and I'd then run code that would loop over the query and display the contents. Here is the form I used and the 'area' I would use for output:
2<div id="content" />
Ok, nothing complex yet. Now let's take a look at showData():
2 var output = document.getElementById('content');
3 var colMap = new Object();
4
5 //first - find my columns
6 for(var i = 0; i < mydata.COLUMNS.length; i++) {
7 colMap[mydata.COLUMNS[i]] = i;
8 }
9
10 for(var i = 0; i < mydata.DATA.length; i++) {
11 var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
12 output.innerHTML += str;
13 }
14}
The first line of code simply creates a pointer to my div. I'll be writing out to that later. Now I need to loop over my query. The question is - how? If you look at the JSON string I output earlier, you will see that it is an object with two main properties - columns and data. The columns property is a list of columns. The data property contains my rows of data. Note though that it isn't indexed by column names. Instead - the first item in the first row of data matches the first column name. So what I need to do is figure out what my columns are. To do this I create a column map - i.e., a mapping of columns to indexes. The first FOR loop does this.
Once I have that - then it is a trivial matter to loop over my rows of data and pick the values I need. So for example, to get the title for row i, I use:
Make sense? I'll include the full source of the code below, but before I do, a few notes:
- The SerializeJSON function takes an optional second argument that is only used for queries. If set to true, the structure is pretty different than what I described above. I'll blog about that later today. (Someone will probably need to remind me.)
- While SerializeJSON is new to ColdFusion 8, ColdFusion 7 introduced the toScript function, which is another way to go from a ColdFusion variable to a JavaScript variable. (It is actually simpler than what I used above.)
Now for the complete template:
2select id, title, posted
3from tblblogentries
4</cfquery>
5
6<cfset jsondata = serializeJSON(getstuff)>
7
8<script>
9mydata = eval(<cfoutput>#jsondata#</cfoutput>)
10
11function showData() {
12 var output = document.getElementById('content');
13 var colMap = new Object();
14
15 //first - find my columnsco
16 for(var i = 0; i < mydata.COLUMNS.length; i++) {
17 colMap[mydata.COLUMNS[i]] = i;
18 }
19
20 for(var i = 0; i < mydata.DATA.length; i++) {
21 var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
22 output.innerHTML += str;
23 }
24}
25</script>
26
27<input type="button" value="Show Data" onClick="showData()">
28<div id="content" />
CFJsonService on RiaForge is worth a look at too.
Here is an example that worked:
<cfset myQuery = QueryNew("ID,Name","INTEGER,VARCHAR")>
<cfset temp = QueryAddRow(myQuery)>
<cfset temp = QuerySetCell(myQuery,"ID","1")>
<cfset temp = QuerySetCell(myQuery,"Name","Dave")>
<cfset myJSON = SerializeJSON(myquery)>
<cfset myNewResult = DeSerializeJSON(myJSON,false)>
<cfdump var="#myNewResult#">
You mention at the top of this blog entry that this is a non-ajax example.
This helped open up a world of options for writing custom ajax renderers and validators and I just want to share one 'gotcha' I ran into after wresting with the AJAX version of this for a while.
When returning the JSON object from an AJAX call to a cfc, <cfreturn SerializeJSON(...) did not return the data in a format I needed for a column. The answer was to use returnformat="JSON".
After that subtle difference, I could parse the JSON string with a column map.
Here's a snippet of sample code from the javascript side:
var grid = ColdFusion.Grid.getGridObject("busforecast");
var gridFoot = grid.getView().getFooterPanel(true);
var gridFoot = grid.getView().getFooterPanel().setVisible(true); //false to remove paging TMS 9626 LLee 12/24/08
var ds = grid.getDataSource();
var forecastObj = new CFCs.ajaxForecastDAO;
<cfoutput>
var getBusmgrSummaryForecastAJAX = forecastObj.getBusmgrSummaryForecastAJAX('#dsn#','#curFY#',#S_OID#, '#qryBusmgrInitialForecast.forecast_level#',1,1);
</cfoutput>
var mydata = getBusmgrSummaryForecastAJAX;
var colMap = new Object();
//find my columns
for(var i = 0; i < getBusmgrSummaryForecastAJAX.COLUMNS.length; i++) {
colMap[getBusmgrSummaryForecastAJAX.COLUMNS[i]] = i;
}
for(var i = 0; i < getBusmgrSummaryForecastAJAX.DATA.length; i++) {
var prioractual = getBusmgrSummaryForecastAJAX.DATA[i][colMap["PRIORACTUAL"]];
var priorcert = getBusmgrSummaryForecastAJAX.DATA[i][colMap["PRIORCERT"]];
var estimatedusage = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ESTIMATEDUSAGE"]];
var actualusage = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ACTUALUSAGE"]];
var fullyearprojection = getBusmgrSummaryForecastAJAX.DATA[i][colMap["FULLYEARPROJECTION"]];
var acctmgrforecast = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ACCTMGRFORECAST"]];
var forecast = getBusmgrSummaryForecastAJAX.DATA[i][colMap["FORECAST"]];
//later in the script I add the var values to a 2nd footer of the grid using Ext.DomHelper.append..
//On the cfc side just use the usual access="remote" returntype="any" but be sure to add returnformat="JSON".
I am using a dynamic java script table which accepts data in json format....I was able to loop through the query object in cfm code and then arrange my data in json like format.......I didnt loop through the object in javavscript nor did I used serialisejson.But my problem is for pagination I am unable to fetch the correct data.Since I am using inline JavaScript and then on click for pagination I am calling the same page but this JavaScript table is not loaded at all........I cannot write this code in external js as I need to use the cold fusion variable in the javascript function....Please advise
I ahve modigied my code.
Please have a look at the below code.
Initially I am showing 30 records.
I used serializejson in an inline javascript.
<cfparam name="mxrow" default="30">
<cfquery name="myquery" datasource="testdsn" maxrows=#mxrow#>
select * from emp
</cfquery>
<cfoutput>
<script>
var getQuery=#serializejson(myquery) #;
alert(getQuery);
//I plan to write a java script table here below
dynamicTableLoad();
</script>
</cfoutput>
Alert is giving me the correct set of values for the query.
Now if wish to seethe next 30 records i.e from 31 to 60,
I intend to write a JavaScript function which again calls the same page through ajax.
But on ajax call the javascript is not loaded at all.
For e.g.
var objdata = eval('('+responseFromServer+')');
(where responseFromServer is the JSON serialized query object in the cfc file)
Now, objdata.DATA.[column_name] would return the respective data.
For e.g. objdata.DATA.INTUSERID, objdata.DATA.STRFIRSTNAME etc (CAPS are essential since JSON in ColdFusion converts the names to all caps!)
Please note: This works only when you set the 'serializeQueryByColumns' in the SerializeJSON to "TRUE".
colMap[myData.COLUMNS[i]]=i;
What this does is create a mapping by where the _name_ of the column has a value of the index.
I can then use that to get data by name and not index.
So imagine that AGE was the 3rd column. Instead of doing
data[3]
I can do
data[colMap.AGE]
Make sense?
[Add Comment] [Subscribe to Comments]