Always Define Your ColdFusion Query Column Types
Posted May 25, 2007 at 3:26 PM by Ben Nadel
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:
- <!---
- 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:
- <!---
- 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:
- <!--- 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).
Reader Comments
call me stupid, be isn't it:
<cfset qID = QueryNew( "id", "VARCHAR" ) />
@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.
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!
Yeah, that's why I love them AND I hate them :)
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...
hi ben
- or other cfml gurus out their
when i create my querynew struct - is it stored in the users comp or the server. And if its stored on the server is it available to everyone or does every user get their own.
@James,
It is stored in the server. It's availability depends on where on the server you store it. Unless you do anything special, its for the given page request only (available only to the user who requested that page). If, however, you store it in the APPLICATION scope, or something, then it can be used by other page requests.
how do you do a case insensitive search in querynew object ?.
The following example is not case-sensitive; it uses the LOWER() function to treat 'Sylvester', 'sylvester', 'SYLVESTER', and so on as all lowercase, and matches them with the all lowercase string, `sylvester':
SELECT dog_name
FROM Dogs
WHERE LOWER(dog_name) LIKE 'sylvester';
never mind got it !_!
it took me a while to find this so delete it if you want
@James,
Yeah, I believe that is the only way to do it.
YES!
i had constructed a query. CFDUMP was happy with it, but whenever i performed a query of query operation i got a null pointer exception. i found the row that was causing the problem, but when that was the only row in the table it was fine. i set the datatype, no more error.
thanks.
It's amazing that when I Google an issue I always get led back here. Thanks Ben!
I was tearing my hair out on this. I was getting "The value "whatever" cannot be converted to a number". I tried debugging by changing values and the error would come and go seemingly at random.
So I defined the column type as "CF_SQL_VARCHAR" but it threw the following error:
Comparison Exception: While executing "="
Unsupported Type Comparison Exception: Comparator operator "=" does not support comparison between following types:
Left hand side expression type = "STRING".
Right hand side expression type = "LONG".
I had to redefine that column as "Integer" and then it worked.
@Andrew,
Glad to help. Unfortunately, in Query of Queries, there are times when not even setting the data type can help! But, 99% of the time, it will do the trick.
@Robert,
Awesome!
I've been playing around with using JQuery to pass complex queries and strings all contained in a structure via AJAX. I've exprimented with sending the structure as a WDDX packet and as a JSON string. The WDDX method does an "ok" job at preserving the query column data types whereas the JSON method strips the data types completely (as Ben noted in another blog post).
My question is this:
Is there a way to reset or manually set the column dat types in an already existing query object? Or do you have to completely rebuild the query with a QueryNew() statement and set the data types that way?
@David,
Sorry if this is a "cop out" answer, but I definitely don't like sending queries as data types. I would rather convert values into structures or raw HTML. I've never been happy with the way queries come down; maybe it's an emotional issue, I don't know. But, I just don't feel that queries and AJAX mix for some reason.
@Ben
Definitely not a cop-out answer. You should see the pile of hair on my desk from trying to wrestle with this. I'm going to look like Mr. Clean in a few days. ;)
Unfortunately the ColdFusion experiment I'm building requires me to send query objects via AJAX to another template and its very important that the column types remain intact. I'm still playing with both WDDX and JSON to figure out the best way. My current approach is to send the query meta data as a separate structure along with the serialized query and then rebuild it manually using QueryNew().
Just had an idea...
What if instead of passing a query object via AJAX you simply pass the SQL statement itself.
You could always run the query in your calling template using MaxRows=0 and Result="result" to make sure it executes quickly and stores the SQL in a structure named "result". Then when you're serializing your data all you have to do is include the SQL statement (i.e. result.sql).
Then in the template that receives the serialized data, you can run the SQL directly from the database server and retrieve 100% accurate column types. This method might also be more efficient with large amounts of data.
Thoughts?
@David,
I think you want to be very careful with passing around SQL statements; I'm not a security expert by any means, but that feels like a big security risk. The last thing you want is for someone to somehow mess with your SQL and then you just run it on the receiving template.
How are you making this call? If you are running the SQL on the calling template, then it would have to be on the CF server, correct? Maybe I misunderstood your architecture. Can you explain a bit more about what you are trying to do?
THANKS BEN! Even in 2013 this post is still helpful! Love your blog. Owe you a beer.
Thanks,
Spencer
