I don't know about you, but February is going to be a very busy month for me. Not only is there plenty of CF work to keep me busy, but my wife, my son, and I are eagerly awaiting the birth of baby boy number two! Nevertheless, I have questions to answer and one revision to make on a previous answer. I hope you find it all worthwhile.

First let's deal with the revision of my answer back in December (Vol. 4, issue 12) regarding the use of a placeholder in a query. The question was really centered around writing dynamic queries and how to handle the question of what should go immediately after the WHERE clause. In my example (see Listing 1), I suggested "WHERE Name = 'PlaceHolderText'". I rationalized that nobody's real name is actually "PlaceHolderText" and therefore it would have no impact on the query except to allow for the dynamically generated OR clauses that followed.

Many of you were kind enough to send me notes about a much better way to do the same thing and I agree with all of you. My only excuse was pure laziness in not wanting to address the subject of using constants in SQL since the person who originally asked the question had very little knowledge of SQL. Nevertheless, the subject is worth addressing.

The problem with using my example of creating a placeholder is that it actually does search through the database table to see if someone has the name "PlaceHolderText". This uses unnecessary resources and may actually (though I certainly hope not) return a record if that were indeed somebody's name.

The better way to do this is to use a comparison of two constants. In our example, we want a placeholder that would not return any records (these would be returned by the data in our OR clauses), so it would be best to start with "WHERE 0=1" (see Listing 2). Both 0 and 1 are constants. 0 will never equal 1 and therefore will not return any records. It also creates no interaction with the database, thus saving resources. If we were running a query that needed to initially return all rows and then filter out results through dynamically generated AND clauses (see Listing 3), you would simply use "WHERE 0=0".

This is a very handy trick to keep in your bag of SQL tips and tricks if you didn't already know it. Thanks again to the many of you who caught my laziness and didn't tolerate it! It's good to know somebody out there is reading and keeping me humble!

Q: I have an application that sends out a lot of e-mail. My problem is I have to check the server a couple of times a day to drag mail from the undeliverable folder back into the spool folder to resend it. When I look at the log file, there are entries that say "No connection to mail server" but there is nothing wrong with the e-mail itself. When I drag them back to the spool folder they usually go out with no problem. Any ideas about this, or is there a way to automatically make CF respool undeliverable mail?

A: I feel your pain! I have had the very same problem with CF for a long time. I'm not sure what causes it to not connect and I'm not aware of any way to get CF to automatically respool the mail, but there is a way to accomplish the same thing. What I've done is this: I created a simple CF page that checks the mail.log file to see if there are any entries that contain the phrase "No connection to mail server". If there are any entries, I locate the file in the "Undelivr" folder and move it back to the "Spool" folder. See Listing 4 for an example of my Respool_Email.cfm page. Then, I added this page to the Scheduler in the CF administrator to execute every 10 minutes. Now if there are any failures to send the mail due to a connection problem with the mail server, those messages are automatically respooled.

Q: I need to retrieve records from our SQL Server database and randomize the order in which the records are displayed. I'm sure I could figure out a way to do this in CF by manipulating the result set, but is there a way to get SQL Server to randomize the results that it gives CF?

A: Yes, there is a very easy method that I have been using that works well for SQL Server queries. All you need to do is add "ORDER BY NewID()" to your query. The NewID() function (this is a SQL Server function, not a CF function!) generates a unique identifier (like CF's CreateUUID() function) for every record in the result set, then sorts by that unique ID. For example, Listing 5 shows a query that would randomly select 10 products from my products table.

Please send your questions about ColdFusion (CFML, CF Server, or CF Studio) to AskCFDJ@sys-con.com. Please visit our archive site at www.NetsiteDynamics.com/AskCFDJ.

• Source Code