ColdFusion Query of Queries Unexpected Data Type Conversion

Posted November 1, 2006 at 4:03 PM by Ben Nadel

Tags: ColdFusion

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

@Hiren,

Nice use of cast.

Hmmmm. My problem seems to fit into this maybe. Stranger things have happened. But this is NOT a QoQ. I am querying an Oracle 10g dtabase with an IN clause which is a query. So maybe it is having the same effect as a QoQ. Basically there is an ID number that can be either digits or a combo of letters and digits. So I do a query that retrieves a list of IDs. I may have B22,699,C22 type of return.

Now I want to get more info for those 3 and so I run the query

Select info
from infotable
where id IN (select ids from idtable)

I get things like "B22 is an invalid identifier"

It shows that the IDs are being put in the query without quotes around them. So I ran that query seperately and tried casting the ID as text but still no go. I tried manually putting single quotes around each one but when I build the string for the query it then puts another set of quotes in.

Any ideas?

@Don,

Hmm, not sure about that one. I don't have any experience with Oracle, so this may very well be Oracle-specific. I have definitely use the IN with a sub-query inside of it with success. Are the ID in the top query and the IDS in the sub-query the same data type?

god, u saved me

<cfquery dbtype="query" name="qryCashReceipt">
select cashreceiptid, category + ' - ' as Dspstr1,
'#qryCashReceipt.category# - #DollarFormat(qryCashReceipt.amount)# (#dateformat(qryCashReceipt.createdate,"d/m/yy")#)' as Dspstr2
from qryCashReceipt
</cfquery>

this also work

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:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment