Wednesday, May 14, 2008

Using SPRY and JSON to Get and Display Query Data

1. Create a Coldfusion Component (cfc) to gather and package your data (See File 1)
2. Create a page to call the cfc and display the returned data. (See File 2)

--------------------------------------------
File 1. (/components/remote.cfc)
--------------------------------------------
<cfcomponent output="false">

<cffunction name="get" access="private" returntype="query" output="yes">

<cfset var q = "">
<cfquery name="q" datasource="#REQUEST.mysqldsn#">
SELECT *
FROM departments
</cfquery>

<cfreturn q>
</cffunction>

<cffunction name="queryToArray" access="private" returntype="array" output="No">
<cfargument name="queryIn" required="Yes" type="query">

<cfset var aResult = arrayNew(1)>
<cfset var q = ARGUMENTS.queryIn>
<cfset var stData = structNew()>

<cfloop query="q">
<cfset stData = structNew()>
<cfloop list="#q.columnList#" index="col">
<cfset stData["#col#"] = Evaluate('q.' & col)>
</cfloop>
<cfset arrayAppend(aResult,stData)>
</cfloop>

<cfreturn aResult>
</cffunction>

<cffunction name="getJSON" access="remote" output="yes" returntype="void">

<cfheader name="Cache-Control" value= "no-cache">
<cfheader name="Expires" value="0">
<cfheader name="Pragma" value="no-cache">

<!--- Get department data --->
<cfset departments = get()>

<!--- SPRY likes to work with an array of structures rather than a query object since those are unique to Coldfusion. --->
<cfoutput>#serializeJSON(queryToArray(departments))#</cfoutput>
</cffunction>

</cfcomponent>
--------------------------------------------

--------------------------------------------
File 2. (mySpryExample.cfm)
--------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfheader name="Cache-Control" value= "no-cache">
<cfheader name="Expires" value="0">
<cfheader name="Pragma" value="no-cache">
<html>
<head>
<title>SPRY At Work</title>
<script src="/SpryAssets/SpryData.js" type="text/javascript"></script>
<script src="/SpryAssets/SpryJSONDataSet.js" type="text/javascript"></script>

<script language="JavaScript">
var ds1 = new Spry.Data.JSONDataSet("/components/remote.cfc?method=getJSON",{useCache:false});
</script>
</head>

<body>

<div spry:region="ds1">
<table>
<tr>
<th spry:sort="DEPARTMENTID" style="cursor:pointer;">Dept ID</th>
<th spry:sort="DEPARTMENT" style="cursor:pointer;">Department</th>
</tr>
<tr spry:repeat="ds1" spry:even="altColor">
<td>{ds1::DEPARTMENTID}</td>
<td>{ds1::DEPARTMENT}</td>
</tr>
</table>
</div>

</body>
</html>
--------------------------------------------

--------------------------------------------
MySQL Table Construct
--------------------------------------------
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for departments
-- ----------------------------
CREATE TABLE `departments` (
`departmentid` int(11) NOT NULL auto_increment,
`department` varchar(45) default NULL,
PRIMARY KEY (`departmentid`),
UNIQUE KEY `deptid` (`departmentid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `departments` VALUES ('1', 'Sales');
INSERT INTO `departments` VALUES ('2', 'Accounting');
INSERT INTO `departments` VALUES ('3', 'Customer Service');
--------------------------------------------


This code has been tested on Firefox and I.E.


Fusecast Web Application Development and Web Hosting Services
www.fusecast.com

No comments: