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

Tuesday, May 13, 2008

Getting Internet Explorer to Release Dataset Cache

Getting I.E. to STOP caching Spry datasets.

This help can be applied to all languages (PHP, ASP, Coldfusion) but since I am primarily a Coldfusion developer, I'll demonstrate how I solved this issue using CFML and JavaScript.

--------------------------------------------------------------------------
File 1. remote.cfc
This file basically returns the XML I will call from Spry
--------------------------------------------------------------------------
<cfcomponent>
<cffunction name="qGetData" access="remote" output="No" returntype="any">
<cfargument name="chatid" required="Yes">

<cfquery name="qGetData" datasource="#DSN#">
SELECT *
FROM chat
WHERE chatid = #ARGUMENTS.chatid#
</cfquery>

<!--- !!!!! IMPORTANT !!!!! --->
<!--- START :: THIS BLOCK OF CODE IS REQUIRED!!! THIS IS WHAT TELLS SPRY TO NOT CACHE THE INCOMING DATA --->
<cfcontent type="text/xml"><!--- I.E. NEEDS THIS --->
<cfheader name="Cache-Control" value= "no-cache">
<cfheader name="Expires" value="0">
<cfheader name="Pragma" value="no-cache">
<!--- END :: THIS BLOCK OF CODE IS REQUIRED!!! THIS IS WHAT TELLS SPRY TO NOT CACHE THE INCOMING DATA --->
<!--- !!!!! IMPORTANT !!!!! --->
<cfset rtrn = "">
<cfxml variable="rtrn">
<chat>
<topic><cfoutput>#qGetData.topic#</cfoutput></topic>
<cfoutput query="qGetData">
<thread>
<date>#DateFormat(qGetData.dtCreated,"m/d/yyyy")#</date>
<time>#TimeFormat(qGetData.dtCreated,"h:mm:ss tt")#</time>
<firstName>#qGetData.first_name#</firstName>
<lastName>#qGetData.last_name#</lastName>
<content><![CDATA[#qGetData.content#]]></content>
</thread>
</cfoutput>
</chat>
</cfxml>

<cfreturn rtrn>
</cffunction>
</cfcomponent>
--------------------------------------------------------------------------

--------------------------------------------------------------------------
File 2. Calling page (chat.cfm)
This file uses Spry to get my dataset from File 1.
--------------------------------------------------------------------------

<cfheader name="Cache-Control" value= "no-cache">
<cfheader name="Expires" value="0">
<cfheader name="Pragma" value="no-cache">
<html>
<head><title>My Chat</title></head>
<script src="/SpryAssets/xpath.js" type="text/javascript"></script>
<script src="/SpryAssets/SpryData.js" type="text/javascript"></script>
<body>
<cfoutput><script language="JavaScript">
//!!!!! IMPORTANT !!!!!
//This variable is appended to the query string to ensure that a cached dataset isn't returned.
var d = new Date().valueOf();
//!!!!! IMPORTANT !!!!!
threads = new Spry.Data.XMLDataSet("/components/remote.cfc?method=qGetData&chatid=#URL.chatid#&cacheBuster=" + d.toString(), //!!!!! IMPORTANT !!!!!
"chat/thread",
{
distinctOnLoad:true,
loadInterval:2000,
useCache:false //this ONLY works in Firefox.
}
);
//The content column needs to be defined as HTML. What a bugger this was to figure out!
threads.setColumnType("content", "html");
</script></cfoutput>

<!--- Output your data! --->
<div spry:region="threads">
<div spry:repeat="threads">
{threads::firstName} says: {threads::time}<br>
<div>{threads::content}</div>
</div>
</div>

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

That should do it. EVERYWHERE you see !!!!! IMPORTANT !!!!! you must be sure to do.

Good luck!

Steve Holland
Fusecast