ColdFusion Query of Queries Unexpected Data Type Conversion : Kinky Solutions : A Student's Perspective by Ben Nadel

ColdFusion Query of Queries Unexpected Data Type Conversion

Posted November 1, 2006 at 4:03 PM

I cam across a really strange ColdFusion query of query error this morning involving some unexpected data type conversion. The query of query seems to be ignoring BOTH the data type passed in as well as the target field's data type and converts automatically to a date. Let me demonstrate.

First, let me build a simple query using ColdFusion's QueryNew() method:

Launch code in new window » Download code as text file »

<!---
Create new query. Set up column data types
to both be VARCHAR.
--->
<cfset qGirl = QueryNew(
"name, date",
"VARCHAR, VARCHAR"
) />
<cfset QueryAddRow( qGirl, 3 ) />
<!---
Set row/cell data values. Use JavaCast() to make
sure ColdFusion doesn't have to guess at data types.
--->
<cfset qGirl[ "name" ][ 1 ] = JavaCast( "string", "Libby" ) />
<cfset qGirl[ "date" ][ 1 ] = JavaCast( "string", "2006-10-01" ) />
<cfset qGirl[ "name" ][ 2 ] = JavaCast( "string", "Sarah" ) />
<cfset qGirl[ "date" ][ 2 ] = JavaCast( "string", "2006-09-01" ) />
<cfset qGirl[ "name" ][ 3 ] = JavaCast( "string", "Cindy" ) />
<cfset qGirl[ "date" ][ 3 ] = JavaCast( "string", "2006-08-01" ) />

At this point, I have a 3-row query that has two columns, both of which are VARCHAR fields. When I set the data into the cells, I do so using ColdFusion's JavaCast() method. This takes out any guessing that ColdFusion has to do. We are explicitly telling ColdFusion to use STRING values in the underlying Java record set.

Nothing magical yet. But now, let's try to perform a ColdFusion query of queries getting the first girl based on the date column (NOTE: the date column is a VARCHAR field with String data):

Launch code in new window » Download code as text file »

<cfquery name="qFirstGirl" dbtype="query">
SELECT
name,
[date]
FROM
qGirl
WHERE
[date] =
<cfqueryparam
value="#qGirl.date#"
cfsqltype="CF_SQL_VARCHAR"
/>
</cfquery>

Notice that I am not touching any data myself. I am only passing in the data that is already stored in the query (via qGirl.date). This should return one record containing the first girl in the original query, but, in fact, it returns an empty record set.

So what's the problem. Well, I thought maybe it was JavaCast() call. Even though that is a best practice, I thought maybe it was confusing the record set. So I removed. This did not fix the error. Then, I thought maybe it was the CFQueryParam tag - maybe there was something about it that was messing up the data. So I tried removing it and putting in a literal string:

Launch code in new window » Download code as text file »

<cfquery name="qFirstGirl" dbtype="query">
SELECT
name,
[date]
FROM
qGirl
WHERE
[date] = '#qGirl.date#'
</cfquery>

This also did NOT fix it. So, what the heck was going on?!? As a last ditch effort, I tried echoing both the original data field and the passed in data to see what was going on:

Launch code in new window » Download code as text file »

<cfquery name="qEcho" dbtype="query">
SELECT
[date],
(
<cfqueryparam
value="#qGirl.date#"
cfsqltype="CF_SQL_VARCHAR"
/>
) AS target_date
FROM
qGirl
</cfquery>

Notice that I am selecting the existing data (in the qGirl.date column) and creating a calculated column based on the CFQueryParam of the first records date value. This gives me the following CFDump:

Very interesting! It seems the ColdFusion query of query is totally ignoring the data type that the CFQueryParam is requesting. But, while I can't see it visually, it must also be ignoring the [date] data type as well (otherwise we would be getting a match). It seems to automatically convert the value to a Date/Time stamp just because it "looks" like date/time object.

To test this, I tried passing in data that did not look exactly like a date:

Launch code in new window » Download code as text file »

<cfquery name="qEcho" dbtype="query">
SELECT
[date],
(
<cfqueryparam
value="-#qGirl.date#-"
cfsqltype="CF_SQL_VARCHAR"
/>
) AS target_date
FROM
qGirl
</cfquery>

Notice that the value attribute of the CFQueryParam tag now has "-" in it. This gives us the following CFDump:

Interesting! Why is it doing this? Why is it ignoring my requests and converting data on it own?

To get around this, I tried to cast the passed in CFQueryParam value to a VARCHAR. But that DIDN'T WORK. What is going on! Finally, I got the whole thing to work if I CAST both values to date:

Launch code in new window » Download code as text file »

<cfquery name="qFirstGirl" dbtype="query">
SELECT
name,
[date]
FROM
qGirl
WHERE
CAST( [date] AS DATE ) =
CAST(
<cfqueryparam
value="#qGirl.date#"
cfsqltype="CF_SQL_VARCHAR"
/>
AS DATE
)
</cfquery>

This finally gives us the desired output:

This seems like a really whacky behavior.

Download Code Snippet ZIP File

Related Blog Entries

Always Define Your ColdFusion Query Column Types

ColdFusion Data Types From Different Sources (And How ColdFusion Sees Them)

Getting The Requested URL From The Page Request Object (Servlet) Without Using CGI

Reader Comments

QofQ has some nice uses and it makes certain things easier, but it is also probably the source of more unexpected behavior and frustration than any other feature.

Posted by Brian Kotek on Nov 1, 2006 at 11:11 PM

Weird. But none of this surprises me of QoQ, really: it really doesn't take much effort to confuse it.

Have you raised it as a bug?

Good investigation, btw.

--
Adam

Posted by Adam Cameron on Nov 2, 2006 at 4:37 AM

Bug has been reported.

Yeah, ColdFusion query of queries does have some strange things in it, but gosh darnit, it's one of the most bad-ass features of the ColdFusion MX family.

Posted by Ben Nadel on Nov 2, 2006 at 7:23 AM

If it worked properly, you'd be right. However it's so flaky that it's really only useful for doing the most basic things. It feels like it's only a beta (which is sad, given it's been around since... when did CF5 come out... 2001?).

So I agree than the words "bad" and "ass" (although I'd say "arse") are suitable to describe it. Just not quite the way you meant ;-)

--
Adam

PS: much prefer your "De-spam" approach to all that "captcha" nonsense that's the current trend.

Posted by Adam Cameron on Nov 2, 2006 at 8:28 AM

It would be interesting to see a getMetaData() for your examples just to confirm the data conversions you are seeing.

Posted by todd sharp on Nov 2, 2006 at 8:57 AM

Adam,

Glad you like the de-spamming technique.

Posted by Ben Nadel on Nov 2, 2006 at 12:58 PM

This may sound really trite... but did you try changing the query equivalency?

from

[date] = '#qGirl.date#'

[date] like #qGirl.date#

It's just a thought, because date types are not like other data types. They are weird objects, so it's possible that using the = operator doesn't work with them.

Posted by pblecha on Apr 13, 2007 at 2:21 PM

I did not try that [no suggestion is trite ;) ]. I am not sure it can work, though, I cannot be sure until I test it.

Posted by Ben Nadel on Apr 13, 2007 at 2:26 PM

This is just what I needed! I retrieved a query and modified it using QueryAddColumn:
<cfset ArraySet(MyArray,1,themembers.recordcount,JavaCast("boolean",true))>
<cfset QueryAddColumn(themembers,"Signer","bit",MyArray)>

Notice that I used JavaCast AND set the column type in QueryAddColumn. That wasn't enough. To pull data out I had to do:

<cfquery dbtype="QUERY" name="thequery">
Select * from this.members
where cast(signer as bit) = cast(<cfqueryparam value="true" cfsqltype="cf_sql_bit"> as bit)
</cfquery>

Otherwise it kept telling my column didn't exist.

Posted by Steve Hammonds on Mar 31, 2008 at 3:37 PM

@Steve,

It's probably because you were using SELECT *. If you took out the * and wrote in the column names, it probably would work fine without the casting. It's just caching the query structure (my guess).

Posted by Ben Nadel on Apr 1, 2008 at 9:00 AM

Post Comment | Ask Ben

ColdFusion Query of Queries Unexpected Data Type Conversion : Kinky Solutions : A Student's Perspective by Ben Nadel