Yesterday I ran into a very strange bug with ColdFusion 9 and I thought it worth blogging about. I think this probably affects earlier versions of the product, but I haven't tested to confirm.
What was happening is whenever I tried executing a specific query, I was seeing the following error:
Invalid data coldfusion.sql.QueryColumn@540350 for CFSQLTYPE CF_SQL_INTEGER
The error had me very perplexed, because the variable that it was complaining about I knew was an integer. When displayed on screen, it showed as an integer. It would even return true when passed to the isNumeric() function. After spending way to much time on the issue, I finally track down the root problem.
What was happening is the variable's value was coming from a ColdFusion query, that I was converting to a structure. Since the query was designed to return at most a single row, I was using the shortcut notation of queryName.columnName to update the variable. I've used this shorthand plenty in the past, because it will either display the value in the first row (when not inside a <cfoutput query=""> or <cfloop query="">) or it will display an empty string if the query returned no rows. This has generally worked fine for me, but apparently this ends up storing a reference to the query object, instead of copying the value directly—which <cfqueryparam /> did not like.
The fix was pretty straightforward, all I need to do was to change my code to reflect grabbing the value from the first row of the dataset: queryName.columnName[1].
My original code looked like this:
<cfquery name="data" attributeCollection="#Application.dsn.getAttributes()#"> select Name, Email, Phone from Employee where EmployeeId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.EmployeeId#" /> </cfquery> <!---// get the column names from the query //---> <cfset columns = getMetaData(data) /> <!---// return the preferences as a struct //---> <cfloop index="column" array="#columns#"> <cfset results[column.Name] = data[column.Name] /> </cfloop>
All I did was change the code to:
<cfquery name="data" attributeCollection="#Application.dsn.getAttributes()#"> select Name, Email, Phone from Employee where EmployeeId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.EmployeeId#" /> </cfquery> <!---// get the column names from the query //---> <cfset columns = getMetaData(data) /> <!---// return the preferences as a struct //---> <cfloop index="column" array="#columns#"> <cfset results[column.Name] = data[column.Name][1] /> </cfloop>
NOTE:You'll notice the only change is the [1] after data[column.Name].
What I learned from this is that I shouldn't trust using the queryName.columnName shorthand—at least not outside a cfoutput/cfloop query block. Instead I need to make sure to reference an actual row from the query.