Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with:

Always Define Your ColdFusion Query Column Types

By Ben Nadel on
Tags: ColdFusion

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).



Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

@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.

Reply to this Comment

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!

Reply to this Comment

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...

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

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?

Reply to this Comment

@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.

Reply to this Comment

@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().

Reply to this Comment

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?

Reply to this Comment

@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?

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.