(Brad) >>This is starting to sound like a bad multiple choice question from a college final... :)
Escpecially our college, Brad. ;-) (Claude) >> It may be a silly question, but why a SELECT * will brake because an unused column was dropped? To clarify, it is a SELECT * query with a CFQUERYPARAM in it. Because the first time the query is executed, an execution plan is built and cached. That execution plan extrapolates all the columns from the table since the * was used. Now, once the query is executed once, drop a column from the table and run the query again. It will fail because the column it is trying to reference (through the cached execution plan) is no longer there. Example table: Table name: user Columns: user_id, firstname, lastname, address1, address2, address3, city, state, zip CF code: <cfquery datasource="whatever" name="qGetUser"> SELECT * FROM user WHERE user_id = <cfqueryparam value="#url.user_id#" cfsqltype="cf_sql_integer"> </cfquery> Execute the CF Code - now the execution plan that is cached has a SELECT statement that looks something like this: SELECT user_id, firstname, lastname, address1, address2, address3, city, state, zip FROM user WHERE user_id = (param) Now, every time the query is run, it will use that execution plan until the query changes (not the parameter, the query). Okay, so now some db guy decides that address3 isn't in use and decides to drop it on a whim (we'll fire him later). Guess what, now every execution of your query will fail because 'address3' does not exist in the user table. It is true what someone said yesterday, that you could go into your code, change a space or new line, and rerun your code and now it will work (because the execution plan has been rebuilt). However, how many failed requests between the time the column was dropped and you change your code will occur? Who knows. One is too many, that's for sure. So, as I stated before, and to take a spin off of Ben's recent comment: It is always a good idea to never use SELECT * when using CFQUERYPARAM and since it is always a good idea to use CFQUERYPARAM then it is always a good idea to never use SELECT *. :) You can quote me on that. ;-) Dave Phillips ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309655 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
