ColdFusion: Error Occurred While Processing Request 10 >= 10

People are always posting questions about this form of ColdFusion SQL error on the House of Fusion CF-Talk list. It can be a really frustrating error if you have never seen it before since you look at it an you are like "What is this crap?!? 10 IS greater than or equal to 10!" The numbers will change from time to time, but once you've seen the error, it never surprises you.

It's a matter of query structure caching. I am not talking from textbook knowledge here (these are my assumptions - call me an ass if you will), but, SQL server caches the form of your query so that it can optimize data retrieval. That's fine. Actually it's really good, so long as your SQL code is well thought out. Most people run into this problem when they use "SELECT *".

SELECT * does not call any columns explicitly, but instead asks the SQL server to return all columns. This has huge potential to conflict with the query structure caching. Let's say that you have a table with columns [ A(int), B(int), C(int) ] and you run a SELECT * query. The SQL server caches the structure of your query for later use. Then you go in and change the table structure to have columns [ A(int), B(int), Z(char), C(int) ] and run the SELECT * query again. Suddenly you get the error:

ColdFusion: Error Occurred While Processing Request N >= N

... where (N) is some random number, usually an integer. Wait, what happened??? The problem is that when you ran the second query, suddenly the third column is a different name AND datatype. From your standpoint, it shouldn't matter since you just wanted everything, but from the SQL server's standpoint, suddenly something doesn't line up, but it's not sure what (since it's working off of a cached structure).

There are two fixes for this issues:

1. The BAD Fix

Go into the ColdFusion Administrator, select the appropriate datasource, view the advanced options, and uncheck the box that tells ColdFusion to maintain connections. This forces the SQL server to NOT use cached query structures. If you then go back to the page and run it again, no more errors. This is a HACK.

2. The REALLY BAD Fix

Since you know that it's a structure caching issue, if you are going to use SELECT *, always add new table columns to the end of the table (if using Enterprise Manager). If you always add columns to the end of the table, then while the cached structure doesn't line up, columns won't mis-match (since physical locations do not change).

3. The GOOD Fix

Don't use SELECT *. It's just not good query technique. You should always write out your queries with explicit column names. The benefits of this are many-fold, so just take my word for it; from maintenance, to readability, to performance... always always always write out your column names.