Hello folks, I had a pretty strange experience with CFQueryParam today. Basically, I have an innocent-looking query: <cfquery datasource="#application.myDB#" name="qGetUser"> *SELECT* username, password *FROM* someTable *WHERE* someID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value= "#cookie.theID#" maxlength="6"> </cfquery>
This query had worked just fine for nearly a year (since I cfqueryparamed it last summer) until this morning, when it broke. The error messages read: *Message:* Error Executing Database Query. 12 May 2009 01:49:21 PM EDT Diagnostics: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '521636a' to a column of data type int. The error occurred on line 35. That error points to the WHERE statement, and it complains the cookie.theID is not an integerbut it is!!! *Even the error messages themselves which contains a CFDump on Cookie scope say it is an integer, of 6 digits!!!* Even more strangely, the error messages always say converting the varchar value 521636a , no matter what the actual cookie.theID value is. Since this query is called from a template that logs users in, I got dozens of error reports, all with this identical error message, even though the actual theID values were very different. When I switched the cfqueryparam cfsqltype to varchar, the error went away!! Of course, the actual data type of the id table column IS varchar, but I had purposefully made it integer in the cfqueryparam, for code readability and more control over the incoming value. L The moral of the story, of course, is that the cfsqltype should probably always match the receiving database table columns data type. But I am just curious why were my values being converted to 521636a ? 6-digit integer/varchar values are certainly not out of bounds for SQL int data type, so I simply cannot fathom why conversion was done and why it was necessary. A ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322436 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
