Stillnet Studios

In our application we create some large queries using QueryNew(). Before we started specifying the datatypes we would run into strange bugs occasionally. When you don’t specify the data type ColdFusion makes a guess. So if we had some code like this:

<cfset myQuery = queryNew('name,age,address,code') />

Then populate that query with data from another system (in our case, its often SAP) everything usually works fine. But ColdFusion is guessing the datatype based on the first 50 records, which can sometimes cause problems.

If for example, the first 50 records contain a ‘code’ value that is all integers, ColdFusion will give that column a datatype of integer. Then if the 51st record contains a varchar value, ColdFusion will blow up.

The solution is to declare the data types when creating the query, like this:

<cfset myQuery = queryNew('name,age,address,code' , 'varchar,integer,varchar,varchar') />

This can get a little harder to manage when the query has many columns in it. We have some with over 40 columns and it’s hard to keep track of which datatype is for which field.

I wrote this little helper function to make it easier:

<cffunction name="typedQueryNew" access="public" returntype="query">
	<cfargument name="columnData" type="struct">
	
	<cfset var columnname = "" />
	<cfset var stringofColumns = "" />
	<cfset var stringofTypes = "" />
	<cfset var counter = 0>
	
	<cfloop collection="#arguments.columnData#" item="columnName">
		<cfset counter++>
			
		<cfset stringOfColumns &= columnName>
		<cfset stringOfTypes &= arguments.columnData[columnName]>
		<cfif counter NEQ StructCount(arguments.columnData)>
			<cfset stringofColumns &= ", ">
			<cfset stringofTypes &= ", ">
		</cfif>
	</cfloop>

	<cfreturn queryNew(stringofColumns,stringofTypes)>

</cffunction>

You can create a typed query much cleaner using this function:

<cfset myQuery = typedQueryNew( {
   name = 'varchar',
   age = 'integer',
   address = 'varchar',
   code = 'varchar'
   } ) />

You can verify the data types are set using the getMetaData function:

<cfdump var="#getMetaData( myQuery )#" />

This has helped clean up our code and makes it easier to create typed queries which cuts down on bugs.