DashCapper – Fred Anderson

In the beginning of January I gave a jQuery presentation to the local ColdFusion User Group here in the Twin Cities. I ran short on time and did not go into detail about using CFC's with jQuery. I decided to piggy-back that topic with this post which I have been planning to write for a while. So, you will be getting two-for-one today. Using jQuery with ColdFusion CFCs and jQuery Pagination with ColdFusion. [Edit: I have broken this up into a couple of posts, this post covers setting up the DB with close to a million records]

To Get started, let's make sure you have the files you need.

• jQuery Javascript Files:  http://jquery.com/ (I am using versio0n 1.3.1)
• jQuery Pagination Plugin: http://plugins.jquery.com/project/pagination 

My example is going to use a Data Table in MySQL with about a million records, it will help to demostrate the benefit of Query Caching when doing pagination. To setup the Table either choose an existing Datasource or Create a new Datasource and Run this Build Table script to add out new table.

	SET FOREIGN_KEY_CHECKS=0;
	-- ----------------------------
	-- Table structure for listings
	-- ----------------------------
	CREATE TABLE `listings` (
	  `listingID` int(11) NOT NULL,
	  `listingPrice` int(11) NOT NULL,
	  `bedrooms` int(11) NOT NULL,
	  `bathrooms` int(11) NOT NULL,
	  PRIMARY KEY  (`listingID`),
	  KEY `id_index` (`listingID`),
	  KEY `id_price` (`listingPrice`),
	  KEY `id_beds` (`bedrooms`),
	  KEY `id_baths` (`bathrooms`)
	)

After you Have the Table in Place you will need to populate it. I have created a Stored proc to generate a bunch of records and fill it with semi random data. Add this stored proc to your database.

	CREATE PROCEDURE fillListingData()
	BEGIN
		DECLARE v1 INT DEFAULT 100000;
 
		WHILE v1 <= 900000 DO
			INSERT INTO listings(listingID,listingPrice,bedrooms,bathrooms)
			VALUES(v1,
					FLOOR(50000 + (RAND() * 950000)),
					FLOOR(1 + (RAND() * 6)),
					FLOOR(1 + (RAND() * 4)));
			SET v1 = v1 + 1;
		END WHILE;
	END

Last step for working with Data is to run that Stored Proc we just wrote. It loads up almost a million records so it could take up to a minute to execute.

	CALL fillListingData()

That should have us all setup in the Database Portion of this tutorial.

I will follow up tomorrow with the ColdFusion code, stay tuned.

This entry was posted on Tuesday, February 3rd, 2009 at 7:31 am and is filed under AJAX, ColdFusion, jQuery. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.