dans.blog: Using queryName.columnName shorthand can generate errors with <cfqueryparam />

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.

Categories: HTML/ColdFusion

2 Comments

Michael Zock
Sep 26, 2012 @ 1:10 PM

Did you try simply looping over QUERY.GetColumnNames() or QUERY.ColumnList (former returns correct order, latter returns alphabetically ordered list)?
Undocumented methods like the former example can be found at http://www.zrinity.com/developers/mx/undocumentati...

If anything, it's those metadata methods which I've had some bad experience with and from the looks of it your example is not quite doing what the paragraph above it says.

Something like

loop over query {
__ loop over columns {
____ work with query[column][query.currentRow]
__ }
}

would be closer.
Adam Cameron
Sep 26, 2012 @ 6:32 PM

When using square-bracket notation to reference a query column (ie: without the row reference too), it is actually a reference to the entire column, not simply the first record's value for that column.

In situations where it is unambiguous that a string is required (like when outputting it), then CF will automatically return the first row's value instead of the whole column. In situations where the whole column might be a correct "fit" for the situation - like when being passed to an array function - then it will be treated as the entire column. In other situations where the casting requirement is not so unambiguous... I guess things can go wrong (like you were seeing).

You mention you were getting the problem in a <cfqueryparam> tag, but don't actually quote that code... it'd be interesting to see this code too.

The bottom line is you're dead right: best to be unambiguous and if you mean the first row, then specify it.

Cheers.

--
Adam

Comments for this entry have been disabled.