CFQueryParam problems after you change a query
On CF-Talk today, an interesting comment about CFQueryParam was made by Adam Howitt. He mentioned that sometimes you will run into database errors because of using cfqueryparam. He wasn't saying not to use it, I want to get that out up front. However, when you use cfqueryparam, ColdFusion creates a prepared statement (precompiled) to send back to the database. This makes things faster. However, sometimes CF will keep these prepared SQL statements in memory, even after you make a change to a query. So the user runs a page with a changed query, and ColdFusion tries to run the prepared statement from cache, error occurs. Adam has a work around, temporarily change your query to force the CF server to recreate your prepared statement. For example, add 'and 1=1' at the end, which doesn't change the result set.
I just wanted to post this here for future reference, because I always remember things better when I write it down. If anybody has any problems with the above scenarios, please leave a comment to enlighten me. :)
Jake Munson
36 Yrs old
Adam
Coldusion caches the order of the columns with a numeric index when selected with * and will not realize that the cache needs to be updated when you change the columns in your database or view. This is a particularly big problem when you add a column not at the end of the table or view, but in between other columns (as the types will then fail).
I have never seen this happen in any other case and I cfqueryparam every single variable I pass into my databases with no exceptions.
That's an interesting observation and it certainly fits with my experience. I have run into problems with queries using "SELECT *" when changing the columns in my table.
That is one of the reasons I avoid "SELECT *" and encourage developers with whom I work to do the same. (Ben Nadel details the other in the related discussion on CF-Talk).
This explanation would also be consistent with the problem reported on CF-Talk. Perhaps more experimentation is needed to verify?
For reference:
http://houseoffusion.com/cf_lists/messages.cfm/for...
I'm willing to claim that I've sufficiently verified it over about 3 years. I haven't used a select * in my final code in about 1.5 years now or more.
As an experiment try this:
Create a table with the following columns:
testID (int)
firstName (varchar),
lastName (varchar),
age (tinyint)
Enter some test rows and CFQuery this with a select * and at least one cfqueryparam in the where clause. Works great.
Now, add a column in between any of the others...This will almost always cause an SQL error that reports a type mismatch in the select statement, due to the order of the columns not matching the order of types that CF expects from the pooled statement.
Type the column names in and it all goes away no matter what you do to the table.
Anyway, I hope this is helping someone out. I need to get out on the lists more than just my local CFUG, but I haven't mastered the Sean Corfield "dual presence" technique that allows me to learn at lightning speed with a seperate thread dedicated to sharing haha.
Take Care,
Mike.
My suggestion is to use data validation before the query to prevent malicious code from being entered into your query.