cfqueryparam and SELECT *
Just recently, I have started using cfqueryparam in all my SQL queries. This method improves performance and security on top of adding validation to all your dynamic parameters. The Macromedia livedocs explain in detail how to use this tag.
This worked great until we had to add a column to a table that was accessed by several SELECT * queries. After adding the new column, these queries started throwing “Unable to convert data type” errors. After some investigation, it became obvious that the query was using the old list of columns. Using cfqueryparam must force CF (or the database, I’m not sure) to cache the query execution plan. For example if you have a table with the following 4 columns:
- title
- description
- price
- colour
You insert a new column in second position:
- title
- categoryID
- description
- price
- colour
Now, a SELECT * query would get the fields by position rather than by name and would return the categoryID as the description. This would cause the “Unable to convert data type” error.
The solution, of course, is to name your columns in the SELECT statement, which is good practice anyway.
June 17th, 2004 at 9:27 am
Bet that was a real bugger to track down. (Shame on you for using SELECT *!)
October 4th, 2005 at 10:27 am
I am generating the SQL statements dynamically,by constructing a string for SQL, depending on certain conditions.But when I insert the tag inside the string, and execute it within CFQUERY,it is resulting in error.I tried using Evaluate function as well.But no use.
March 25th, 2006 at 4:08 pm
I´ve got the same problem as Peter! But I need the string manipulation in the script-block and so I cant work with the cfqueryparam-tag, thats very bad.
any ideas???
March 27th, 2006 at 12:32 pm
Sigi: You can only use cfqueryparam within a cfquery tag. If you are building the SQL statement outside of the cfquery tag, you still need to insert the cfqueryparam tags within the cfquery tag. You can always build the dynamic statement within the cfquery tag. Send me your code if you still can’t figure it out. I’ll have a look and see what I can do. (don’t post the code as a comment, tags are stipped out)
March 27th, 2006 at 4:21 pm
Hi Martin, at first many thanks for the fast reply!
The code I use looks like this:
within a cfscript-block I build the sql-statement:
rep_sql = “select #application.NGMDProd["txt"]# PROD_TXT
from #application.Tables["prod"]#
Join #application.Tables["subs_pgrp"]# on #application.SubsPgrp["oid"]# = #application.Prod["subs_pgrp_oid"]#)
Join #application.Tables["prod_grp"]# on (#application.ProdGrp["oid"]# = #application.SubsPgrp["pgrp_oid"]#)
Join #application.Tables["prod_grp_name"]# on (#application.ProdGrpName["poid"]# = #application.ProdGrp["oid"]#)
Join #application.Tables["dept"]# on (#application.Dept["oid"]# = #application.ProdGrp["dept_oid"]#)
Join #application.Tables["dept_name"]# on (#application.DeptName["poid"]# = #application.Dept["oid"]#)
where #application.Prod["oid"]# =[CHAR]##EbDspData1[data_key_1].SUBSID_PROD_OID##[/CHAR]
AND #application.ProdGrpName["cabbr"]# = [CHAR]##session.app_language_iso##[/CHAR]
AND #application.DeptName["cabbr"]# = [CHAR]##session.app_language_iso##[/CHAR]“;
in the same block I create an object for executing this string:
objSql = createObject(”component”, “common.util.Database”);
get_data_3 = objSql.runPreparedQuery(rep_sql_3, “ng”);
and this is the relevant part of the executed function:
mySql = replace(arguments.sqlString, “[CHAR]“, chr(60)&”cfqueryparam value=”&chr(34), “All”);
mySql = replace(mySql, “[/CHAR]“, chr(34)&” cfsqltype=”&chr(34)&”cf_sql_varchar”&chr(34)&chr(62), “All”);
at last the created variable is executed within a cfquery-tag:
#preserveSingleQuotes(mySql)#
And on this point I get the error from the db2-engine
Thank in advance for your effort!