Andrew, I disagree.
Although cfqueryparam performs vailidation, it is not the reason you should be using it. cfqueryparam makes the database engine use parameter binding on your queries. For example your query below would look different to the db engine every time it is run eg: Select * from Employees where EmployeeId = 1 Select * from Employees where EmployeeId = 2 Select * from Employees where EmployeeId = 3 so your database engine builds different query execution plans for each of these queries (in addition to validating the query, checking\casting the parameter types etc) etc But with parameter binding the databse engine is executing something that looks more like a stored procedure, Select * from Employees where EmployeeId = @var1 Here is an example from a SQL profile trace on my server: declare @P1 int set @P1=30 exec sp_prepexec @P1 output, N'@P1 decimal(38,0)', N'SELECT IsNull(COUNT(ITEM.SB_INVOICE_ITEM_ID),0) AS CNT, IsNull(SUM(ITEM.SB_INVOICE_COST),0) AS TOT_COST, IsNull(SUM(CASE WHEN ITEM.SB_ITEM_STATUS_CODE = ''ER'' THEN 1 ELSE 0 END),0) AS ERR_COUNT FROM SB_INVOICE_ITEM ITEM WHERE ITEM.SB_INV_BATCH_ID = @P1 ', 1387 select @P1 >>> Andrew Scott <[EMAIL PROTECTED]> 02/03/2007 11:57 am >>> Duncan, The query is only cached when you tell it to be cached. But are you using any other framework like reactor or transfer or even MG:U, or even have this in a cfc that might be stored in a session or application scope. One other thing I would like to point out that a lot of people do not take for granted, but tend to just do it anyway. I chose not to, but that is my choice. If the query is in a cfc, and that function uses arguments that will be used in the query for example <cffunction name="getEmployee"> <cfargument name="EmployeeId" type="numeric" required="true" /> <cfset var Record = '' /> <cfquery name="Record" datasource=""> Select * from Employees where EmployeeId = #Arguments.EmployeeId# </cfquery> </cffunction> I will never ever use the cfqueryparam, and the reason being is that the function itself will take care of the validation for me. However, although I did say never a string is a different story and will use it for a string. I know this has nothing to do with your problem, but just wanted to make that statement because I still see people use the cfqueryparam in places I know it is not necessary to use. If you would like to post a more detailed example on how you are using this, whether it is in any of my original methods then we can help you further but one thing to also take into consideration is the caching of the coldfusion class files too, this should never be switched on for development purposes. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -----Original Message----- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, 2 March 2007 11:23 AM To: cfaussie Subject: [cfaussie] @#$!! queryparam I think its to do with the binding or the caching of the query plan but when we change a table in the DB, like remove a column or change the length of a varchar, all the queries that use that table and have a queryparam break. We get messages like [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. on queries as simple as a Select * from table where id = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> and we didnt even change the id column (obviously) At the moment the only way we can find of making it all work again is to remove all the params or to restart cf or sql. All of which are bad in their own way even though its only on the dev box. Strangely I havent come across this before, previous installations havent had this, but I have just moved to inherit some software and dev boxes and I cant work out why this is going on. All the settings in the cfadmin db connection are the defaults. Anyone else have this problem / have a solution? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---