Posted in ColdFusion | Posted on 02-23-2007 | 7,248 views
A while ago I promised a demo showing how to use Spry's new PagedView feature to combine client and server side paging. Taking the "walk before you run" lesson to heart - I decided to first show a demo of how to do server side paging with Spry. This would be useful for huge datasets.
Let's first start with the server side code. I wanted something you guys could download and play with, so the server side code is not using a database. Anyway - here is the code:
2<cfparam name="url.start" default="1">
3<cfparam name="url.end" default="100">
4
5<cfset total = 1000>
6
7<cfif url.start gt total>
8 <cfset url.start = total>
9</cfif>
10
11<cfif url.end gt total>
12 <cfset url.end = total>
13</cfif>
14
15<cfcontent type="text/xml"><cfoutput><?xml version="1.0" encoding="UTF-8"?><people><cfloop index="x" from="#url.start#" to="#url.end#"><person><id>#x#</id><name>Name #x#</name></person></cfloop></people></cfoutput>
I begin with a cfsetting to reduce the whitespace. This is important when returning XML. I then have two cfparams. One for the start row and one for the end row. I default these to sensible values.
Next I have a "total" value. This wouldn't be hard coded but would rather come from the database. (Don't forget my MySQL tip on how you can get a total and a page all in one query.)
Since I know how many rows of data I have - I can then do sanity checks on start and end. Now I'm ready to generate my XML. Since I'm not using a database I've done a simple cfloop that "fakes" my data. I'm assuming folks have no questions on this so I'll move on.
The client side is a bit more complex than a simple Spry dataview. As before I'll show you the code than walk through what I did:
2<script type="text/javascript" src="/spry/SpryData.js"></script>
3
4<script type="text/javascript" >
5var start = 1;
6var perdatapage = 20;
7var end = start + perdatapage;
8
9var baseurl = "testdata.cfm?";
10
11function getURL() {
12 var s = baseurl;
13 s+="start="+start;
14 s+="&end="+end;
15 return s;
16}
17
18var mydata = new Spry.Data.XMLDataSet(getURL(),"//people/person");
19
20Spry.Data.Region.debug=false;
21
22function goBack() {
23 start=start-perdatapage;
24 if(start < 1) start=1;
25 end = start+perdatapage;
26 mydata.setURL(getURL());
27 mydata.loadData();
28}
29
30function goForward() {
31 start=start+perdatapage;
32 end = start+perdatapage;
33 mydata.setURL(getURL());
34 mydata.loadData();
35}
36</script>
37
38<div id="mydata" spry:region="mydata">
39
40 <table border="1" width="500">
41 <tbody spry:repeat="mydata">
42 <tr>
43 <td>ds_RowID={ds_RowID}</td>
44 <td>id from db={id}</td>
45 <td>{name}</td>
46 </tr>
47 </tbody>
48 <tr align="right">
49 <td colspan="3">
50 <a href="javaScript:goBack()"><< Previous</a> |
51 <a href="javaScript:goForward()">Next >></a>
52 </td>
53 </tr>
54 </table>
55</div>
Ok, lots to cover here. I'm going to skip over the things that I assume most folks know - like the first two script tags that include the main Spry libraries. Let's begin by looking at how we track where we are in the dataset:
2var perdatapage = 20;
3var end = start + perdatapage;
4
5var baseurl = "testdata.cfm?";
6
7function getURL() {
8 var s = baseurl;
9 s+="start="+start;
10 s+="&end="+end;
11 return s;
12}
These lines initialize my start, end, and page size variables. Because my server side code accepts a start and end value, I created a helper function, getURL(), that will generate my URL for me. This was just for convenience. I use this in my dataset creation line:
So skipping ahead a bit - you can see where I use a simple table to output the values from the XML. Notice I show both the ID, which comes from the data, and Spry's ds_RowID value. I do this just to show you the difference between the row in the dataset on the client side and the server side's row ID.
To enable paging, I added two links at the bottom of the table that call JavaScript functions. Let's now take a look at these - first the code to move to the next page:
2 start=start+perdatapage;
3 end = start+perdatapage;
4 mydata.setURL(getURL());
5 mydata.loadData();
6}
I begin with some simple arithmetic. Then I use setURL() on the dataset. This lets me update the URL, again I use my getURL function. Then loadData() is called which refreshes the dataset. That's it. The code to handle moving backwards is the same - except for some logic to ensure we don't go below 1.
For a demo of this, go here: http://ray.camdenfamily.com/demos/spryserverpaging/
You may also download the code attached to this entry. This code isn't perfect though. It will gladly let you go past the 1k limit. How can we fix that? I'll answer that in the next post.
I see how your JavaScript functions work for getting more XML formatted data from your testdata.cfm file. However, I would really like to see an example of having the server/database return pages of data using the start/end values you create in the JavaScript.
I use SQL Server 2000 and 2005 and wonder if there is a start and maxrows function I could use in the SQL to get a "page" of records in a sorted query.
If no one has an answer, I'll research this further.
I'm trying to avoid using ColdFusion to cache the query results and then using cfloop's start and end, etc...
select *
from tableA
where rowId in (select top 10 rowId from tableA where rowId >= <cfqueryparam value="#url.start#"> order by rowId)
Technically you could do this:
where rowId between #url.start# and #url.end#
but that would not accommodate possible deletions.
This should work for fixed size chunks. You may be able to declare a variable in sql and make the page size (top n) dynamic but I haven't tested that.
Oh yeah - this example assumes incremental auto numbered id's. Google for sql paging and you'll find a lot of different methods.
and http://labs.adobe.com/technologies/spry/samples/da... to help me solve my issue. Thanks Ray.
Just curious but is there any known issues with this process being slow in firefox and fast in IE?
If not, is there any reason why it would be? Possibly my Firefox setup or something with it?
I have it loading from a database with 32k records and in IE it's like instant when I click next but in Firefox it's ungodly slow.
Any tips would be appreciated.
Thanks!
Very odd!
Oh well, I'll keep digging.
Thanks,
Josh
just change the baseurl to something like
var baseurl = "sprytest.cfc?method=myFunction&";
hence calling your function to of the cfc then in the server side code.
next just do a normal cfc access as remote and returntype as xml
<cfcomponent>
<cffunction name="myFunction" access="remote" returntype="xml">
set the params just like before
<cfparam name="url.start" default="1">
<cfparam name="url.end" default="100">
<cfset total = 1000>
<cfif url.start gt total>
<cfset url.start = total>
</cfif>
<cfif url.end gt total>
<cfset url.end = total>
</cfif>
then setup our query
<cfquery name="Getdata" datasource="data">
SELECT *
FROM data
</cfquery>
the next part we are going to use cfxml and create xmlObject
and build our xml
<cfxml variable="xmlObject" casesensitive="yes">
<MyData>
<cfloop query="data" startrow="#url.start#" endrow="#url.end#">
<data>
<ID><cfoutput>#XmlFormat(ID)#</cfoutput></ID>
<Name><cfoutput>#XmlFormat(name)#</cfoutput></Name>
<Location><cfoutput>#XmlFormat(LOCATION)#</cfoutput></Location>
</data>
</cfloop>
</mydata>
</cfxml>
<!---We then out put our xmlObject to the browser--->
<cfset xmldata = xmlObject>
<cfsetting showdebugoutput="no">
we then return it back
<cfcontent type="text/xml"><cfreturn xmldata>
</cffunction>
</cfcomponent>
hope that can help someone if they are having problems with that.
If the database returns null, spry returns null to the client. How do I get it to just not say anything? or return or something instead of the word NULL
It's really causing me problems...
Any ideas would be awesome!
Thanks!
[Add Comment] [Subscribe to Comments]