Always Define Your ColdFusion Query Column Types
Posted May 25, 2007 at 3:26 PM
It used to be in the pre-ColdFusion MX 7 days that there was no way to manually create a query and tell ColdFusion what Java data type you wanted the column to be. With the introduction of ColdFusion MX 7, that all changed. Now, QueryNew() and QueryAddColumn() both allow for explicit data type declarations:
QueryNew( columnlist [, columntypelist] )
QueryAddColumn( query, column-name[, datatype], array-name )
The problem is, either people don't know about this or they are just not inclined to use it. That's OK if you don't really use manually constructed queries all that much, but in my experience, declaring a column data type is of crucial importance. If you don't do this, ColdFusion tries to be smart and guess what data type you are trying to use. While this sounds like a good feature, it actually causes very irritating and hard to work around problems. Things of this nature can be seen by the people who have trouble getting my POIUtility.cfc to work on manually constructe queries.
To better see what I am talking about, we are going to create a ColdFusion query object from scratch without defining the column data type and then populate it with 100 numeric values. Once it is populated with numeric values, we are going to change one value and then try to copy the query:
Launch code in new window » Download code as text file »
- <!---
- Create an ID query. This will hold a single value
- which is a NUMBER stored as a VARCHAR value. However,
- we are not going to tell the ColdFusion what column
- data type we are actually using.
- --->
- <cfset qID = QueryNew( "id" ) />
-
-
- <!---
- We are going to populate the query with 100 IDs.
- Let's add 100 rows to the query now so that we don't
- have to do it when we set the IDs.
- --->
- <cfset QueryAddRow( qID, 100 ) />
-
-
- <!--- Populate the query with ID values. --->
- <cfloop
- index="intID"
- from="1"
- to="#qID.RecordCount#"
- step="1">
-
- <!---
- Set the ID. Remember, we are storing this values
- as VARCHAR values (even though they are numeric).
- Therefore, we must cast them to string for the
- underlying Java data type.
- --->
- <cfset qID[ "id" ][ intID ] = JavaCast( "string", intID ) />
-
- </cfloop>
-
-
- <!---
- Now that we have the ID values in, lets go in and
- change one of the values in the middle to be a
- non-numeric ID value. Keep in mind, we are STILL
- storing the actual value as a string.
- --->
- <cfset qID[ "id" ][ 75 ] = JavaCast( "string", "ID-75" ) />
-
-
- <!---
- Loop over the query to prove that we are indeed
- working with a query whose values were set properly.
- --->
- <cfloop query="qID">
-
- <!--- Check for comma. --->
- #qID.id#<cfif NOT qID.IsLast()>,</cfif>
-
- </cfloop>
Notice that all of the cell value setting is done with ColdFusion's JavaCast() method. This is to ensure that ColdFusion doesn't mess up when trying to convert the typeless ColdFusion data into the strongly typed Java data. And, in doing so, we are converting all of the values to Java strings. Running the above code, we get:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, ID-75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100
Notice that the query iterates properly and that "ID-75" is in the data output.
Now, let's perform a very simple ColdFusion query of queries on the query we just created:
Launch code in new window » Download code as text file »
- <!---
- Now, let's copy the query by selecting all of its
- contents into another query object.
- --->
- <cfquery name="qIDCopy" dbtype="query">
- SELECT
- id
- FROM
- qID
- </cfquery>
If you run that, you get the following ColdFusion error:
The value "ID-75" cannot be converted to a number.
What gives? When setting the column values, we cast them all to string, why would it be trying to convert it to a number? Since we did not define the ColdFusion data type for that column, in the query of queries, ColdFusion examines the first 50 records of the originating query and "determines" that it contains all numeric data. From this conclusion, it then tries to convert every data value coming out of that column into a number before it inserts it into the resultant query of the QoQ.
To alleviate this, all you have to do is define the ColdFusion query column type in the QueryNew() call:
Launch code in new window » Download code as text file »
- <!--- Define the query with column names and types. --->
- <cfset qID = QueryNew( "id", "CF_SQL_VARCHAR" ) />
If you do that, the whole demo will run without a hitch.
To be safe, I would recommend always setting the column types. There is really no need to ever not use them unless you are doing something with the query object that is very sneaky (such as using it as an iterator of Complex objects). Unfortunately, I have run into cases where I use 100% column type information and ColdFusion still cannot handle the query of queries properly. Hopefully some more of these bugs will be worked out in ColdFusion 8 (Scorpio).
Download Code Snippet ZIP File
Comments (5) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Testing ColdFusion Session Cookie Acceptance
Creating Multiple Instances From The Same Java Class In ColdFusion
Reader Comments
call me stupid, be isn't it:
<cfset qID = QueryNew( "id", "VARCHAR" ) />
Posted by Tony Petruzzi on May 25, 2007 at 4:47 PM
@Tony,
It's actually both. Both forms are acceptable. I use the CF_SQL_VARCHAR style because that is what I am used to using in my CFQueryParam tags. But it doesn't make a difference as far as I know.
Posted by Ben Nadel on May 25, 2007 at 5:15 PM
Another awfull thing is queries of queries.
ColdFusion seems to guess the column types in stead of adapting them from the query object.
For example:
If you have a resultset from let's say 200 records with a numeric column where the first 100 records are NULL and you do a WHERE statement: WHERE numericColumn = 5 you will get an error. Invalid type for string. If the first record is a number, the error doesn't occur.
It seems to guess the data types by the first n records.
Really annoying!
Posted by Jorrit Janszen on May 29, 2007 at 8:01 AM
Yeah, that's why I love them AND I hate them :)
Posted by Ben Nadel on May 30, 2007 at 6:09 PM
I came across a really weird one: All my columns were cast to proper datatypes, but CF ignored them anyway! (~200 so rows in I had a FF0000 hex color). Had to prepend 0x to colors, then strip it out, for the QoQ to work...
Posted by Dylan Miyake on Jul 19, 2007 at 2:07 PM
