ColdFusion Query of Queries Unexpected Data Type Conversion

Posted November 1, 2006 at 4:03 PM

Tags: ColdFusion

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 »

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 »

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 »

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 »

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 »

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 »

This finally gives us the desired output:

This seems like a really whacky behavior.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

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.

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

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.

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.

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

Adam,

Glad you like the de-spamming technique.

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

from

[date] = '#qGirl.date#'

to

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

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.

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.

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

Can I just say that this absolutely saved me! Thank you so much for posting this! This little bug was driving me nuts!

I just stumbled upon this entry after a google search. I was having a single digit integer (which was stored on the database as a varchar) being called a double in a QoQ.

I tried pblecha's suggestion to use a like and unfortunately that did not work. However, I then tried to CAST as VARCHAR and that did work.

Here is the original query:

SELECT frmtm, totm
FROM ProfileStruct.DOWControls
WHERE dow = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DayValue#">
AND seq = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="2">;

And I changed it to:

SELECT frmtm, totm
FROM ProfileStruct.DOWControls
WHERE CAST(dow AS VARCHAR) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DayValue#">
AND CAST(seq AS VARCHAR) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="2">;

I was actually having problems with both conditions of the predicate and now they work fine with CAST. For the record, all the fields are stored as VARCHAR2 on an Oracle database. dow is a 3 character representation of the weekday (MON, TUE, WED, ...) and seq is either 1 or 2.

It just shows that there is still a bunch of flakiness with CF7's QoQ.

@Frank,

Yeah, I would like going forward for QoQ to enforce more appropriate data types. The problem with that, however, is that it is currently quite flexible (you can put *anything* into a query). Forcing more data constraints might break a lot of code. We'll see. At least this error only pops up from time to time.

I wish there was a way to change the column data types on the fly. I've been playing around with jQuery AJAX and CFWDDX and I've noticed that when I pass a query object as a WDDX packet and re-construct it using CFWDDX the data types can get all messed up -- specifically when dealing with dates. They get even more messed up if the first row of a query contains a NULL value.

@David,

Yeah, that would be cool. That's why I like converting query data to array/struct data, so I know how it's all going back and I can do any massaging that needs to be done.

I had the exact same problem with a regular query on a varchar data type. I used the CAST() function and it solved the problem, but it only errored when certain strings were passed in. E.G. 'mfrscan' worked fine, but 'mfrbillback' threw sql conversion error. Very strange?

@Charlie,

Yeah, that is strange - especially with values that are not too different. Query of queries is awesome, but can definitely be frustrating from time to time.

@Charlie

Query of Queries is like a 3 year old with ADHD. You should never leave it alone to make any decisions for you. I highly recommend using a combination of JavaCast (when building your QofQ manually) and Cast() in order to ensure the data matches your expectations.

@David,

Yeah, JavaCast() is awesome; but, I've even had *some* issues with that. Sometimes, ColdFusion just wants to ignore everything about your column and cast data!!

I noticed (with CF8) similar QofQ date bizareness, trying to test for date X being before or on now().

If I have a query "foo" and it cfdumps and the "dateX" col looks and smells like a date field, I'd try "SELECT * from foo where cast([dateX] as date) <= cast(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#now()#"/> as date)" with no luck. But what worked? Doing the cast as integer. "SELECT * from foo where cast([dateX] as integer) <= cast(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#now()#"/> as integer)" got me the correct results

Yeah, CF QofQ is very cool, but in a maddening unpredictable way. Argh.

@Alan,

That's frustrating when not even a CAST() call will fix your problem (or at least, not with the more appropriate data type :)).

Ben, I'll pass along another QofQ datatype disaster I went through a couple days ago. An app that uses QofQ started throwing "cannot conver value '' to number" errors for no apparent reason. The QofQ had been CASTed to death, and I could not come to a solution, but did come up with a strange but successful jury-rig.

I seralized the original query with CFWDDX, then deseralized it back to a query again. After that, the subsequent QofQ worked fine. Go figure.

Anyhow, I'm not trying to hijack this thread or anything, but doing a google of "cf query of query" brings this page up so it's become a good resource for QofQ victims. Thanks for your excellent blog.

@Alan,

Please, never feel bad about contributing to the conversation; every time someone brings up a new point, it's an opportunity for us to learn something new.

The converting "" to numeric, maybe that happens if the value is NULL? Although I think QoQ should handle NULLs properly... although who knows, right??

I display usage quarterly for various products in tabular format. I displays products in rows and monthly usage in column. When I query my sql database, I may get 1-3 records based on the entry. I create a coldfusion query which has 0 as usage for all month for a given product. I union both the query and sum up usage grouping date so I will get a record for every month irrespective for data in database. My problem starts while merging data as date format are different in cf query ans sql query. Data do not group due to date difference (sql 2010-10-01 00:00:00.0 do not match with {ts '2010-10-01 00:00:00'}). I cast sql date while merging data and bingo... it works.. thanks for the solution given it this article.

select start, cast(usagedate as Date) as usagedate from sql.qrymaxrow
union all select start, usagedate from cfm.rowscounter

Post Comment  |  Ask Ben

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.