Friday, January 28, 2005

Primer on ColdFusion Parsing XML

The ColdFusion documentation, although useful and thorough, is not perfect (yet). The writeups on XML functions lack a simple, easy to follow example of how to parse/manipulate an XML document.

I found the following article useful http://www.findarticles.com/p/articles/mi_m0MLU/is_8_5/ai_106770602. It explains how to parse an RSS feed using ColdFusion. I found that by reading thru the article and observing the sample code, I was able to learn how to parse XML in about 5 minutes.

Thursday, January 06, 2005

SQL: Limiting Queried Rows

The Problem
Where I work, we have a few databases with some pretty large tables. Record counts into the hundreds of thousands are a dime a dozen, and you'll even encounter the occasional table with multiple millions or records. Though I dearly love the SQL keyword TOP, sometimes it is inadequate. As a web programmer, how often have I wished that there were a way to tell a SQL Server to only return rows 50 to 100? Doing so would make result pagination a breeze.

I posted a question to a Cold Fusion Message Board that I frequently visit, and my main man Lance M. (http://www.webasics.net/) came to the rescue by posting three possible solutions that he had read about in a SQL book.

The Solution
While there doesn't really appear to be a way to specify which results to return short of using a stored procedure, there are several possible workarounds. The workaround that I liked best is both simple and elegant. I will post it here. If you are dieing (spelling?) to know the other two, make a comment, and I will post them as well

If you want to get records 1-50 from the fictitious table "author" you would, of course, just do the following:

SELECT TOP 50 *
FROM author
ORDER BY last_name;

However, if you wanted to get records 51-100, you can do this:

---------------------------
SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC;
---------------------------

Presto!
What you end up with is records 51-100 (albeit in reverse alphabetical order). This query essentially gets the first hundred authors, reverse alphabetizes them (ORDER BY last_name DESC), and then returns the first 50 from the upside down result. Granted, this method is not as efficient as just being able to specify a STARTROW and an ENDROW, but it is a LOT more efficient than having the database return a coupl'a hundred thousand records, and then just picking out the chunk of 50 that you want.

If you want to re-alphabetize the result, just add the following wrapper:

---------------------------
SELECT *
FROM
(SELECT TOP 50 *
FROM
(SELECT TOP 100 *
FROM author
ORDER BY last_name ASC)
ORDER BY last_name DESC)
ORDER BY last_name ASC;
----------------------------

Very cool, Lance. Thanks for the tip.

Tuesday, January 04, 2005

Christmas Tree Preservative

I know that Christmas is over. However, I thought that I would post this here anyway. The following recipe is for a Christmas tree preservative. For the last few years, rather than putting regular water in our Christmas tree stand, we have put this mixture in. It seems to keep the Christmas tree fresher for much longer. My in-laws used it last year, and it kept their tree fresh for over a month. This year we used it, and it worked well until we forgot to refill the stand for a few days (about two weeks after we got the tree). We have heard anecdotes about people using this preservative and its tendency to make a tree more fire resistant.

Following is the recipe for the preservative. Disclaimer: I make no gaurantee whatsoever that this preservative will actually work or make your Christmas tree fresher or last longer or more resistant to fire. I just suggest that you try it for yourself and see if it works. If it doesn't, well, sorry. It did for us.
2 C karo syrup
2 oz liquid bleach
2 pinches epsom salt
1/2 tsp borax
2 gal hot water

Mix together in bucket, make fresh cut on tree trunk, soak tree
overnight. Use this solution to refill tree stand as needed.
There you go. Hopefully it will work for you as well as it has for us.

Monday, January 03, 2005

Using ColdFusion Components--Properly

Just read a very interesting article by Ben Forta. In the article, Ben suggests that CFCs should not be allowed to output anything to the screen. He suggests that CFCs should only be allowed to get/set/add/update/delete data and such, but that they should not be allowed to actually output anything to the screen. An interesting way of looking at things.

We have been dabbling with CFCs for a couple of months now, and have been trying to draw that line between what should be done by a CFC, and what should be done by simple CFML/custom tags/etc. Ben's article merits at least a look.