Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with:

ColdFusion: Error Occurred While Processing Request 10 >= 10

By Ben Nadel on
Tags: ColdFusion, SQL

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.


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.