Wrangling QofQ Datatypes

I have a fairly complex report system in ColdFusion (v7) that takes one or more queries and joins them together in preparation for filtering and display. Some data comes from a database call and some columns are dynamically added to the query via queryAddColumn(), specifying the datatype as we go along like so:


<cfset queryAddColumn(data, "anotherCol", "VarChar", arrayNew(1))>


<cfloop query="data">
<cfset qryExtra = myCFC.getArbitraryData(id = uidMember)>
<cfif dspShared.recordCount>
<cfset querySetCell(data, "anotherCol", qryExtra.newCol, currentRow)>
</cfif>
</cfloop>

This seems to work, more or less, just fine. The problem is that occasionally, fields which are varchars are being interpreted an integers because the data is primarily numeric. Case in point: zip codes. Most of them are 5 digits but we have a sprinkling of either blank (“”) or Canadian zip codes (“V6Y 4K9″) that are also valid.

When ColdFusion comes to these values, it intermittently (based on what criteria, I’m not sure) bombs out trying to parse the value and throws an error like so:

The value "V6Y 4K9" cannot be converted to a number

You can tell from the top of the stack trace that the problem lies in validating the results and “inferMetaDataTypes” and then “guessColumnType”:

coldfusion.runtime.Cast$NumberConversionException: The value "V6Y 4K9" cannot be converted to a number at coldfusion.runtime.Cast._double(Cast.java:491) at
coldfusion.runtime.Cast._double(Cast.java:522) at
coldfusion.runtime.Cast._long(Cast.java:370) at
coldfusion.sql.imq.rttExpr.castTo(rttExpr.java:515) at
coldfusion.sql.imq.imqTable.guessColumnType(imqTable.java:453) at
coldfusion.sql.QueryTableMetaData.InferMetaDataTypes(QueryTableMetaData.java:350)
at coldfusion.sql.imq.TableList.validate (TableList.java:166) at
coldfusion.sql.imq.rttSelectExprSpec.validate(rttSelectExprSpec.java:498)
at coldfusion.sql.imq.rttSelectStmt.validate(rttSelectStmt.java:84) at
coldfusion.sql.imq.jdbcStatement.fetchResult (jdbcStatement.java:538) at coldfusion.sql.imq.jdbcStatement.execute(jdbcStatement.java:131) at
coldfusion.sql.Executive.executeQuery(Executive.java:719) at

It makes me nervous when computers “guess”.

In my case, I was able to debug slightly further and confirm the data types were the culprit using the following code:

<cftry>
<cfquery name="filteredData" dbtype="query">
SOME QUERY THAT FAILS
</cfquery>

<cfcatch type="any">
<cfdump var="#cfcatch#">
<cftry>
<cfdump var="#getMetaData(filteredData)#">
<cfcatch type="any"></cfcatch>
</cftry>
</cfcatch>
</cftry>

If the query bombs, the getMetaData() function will give you a lot of insight into the actual query object. While most varchars in my query had the type “VARCHAR”, my zip code field had a type of “[undefined struct element]“.

So, how do we fix it? From my post to the CFGURU list, QofQ is definitely a wacky beast. Pretty much everyone who uses it reports some irregularities with datatypes. There are (so far) two options:

1. CAST() every single column. This is a PITA, but it seems to resolve the problem:
   <cfquery name="filteredData" dbtype="query">
SELECT CAST(id AS INTEGER) AS id
,CAST(name AS VARCHAR) AS name
,CAST(zipcode AS VARCHAR) AS zipcode
FROM existingQuery
</cfquery>
2. The other way, discovered by Robert Munn in a project he’s working on, is to explicitly create the query with data types before performing any joins or queries.

Any other successful workarounds?