Query of Query gotcha

Posted At : December 31, 2007 7:54 AM
Related Categories: ColdFusion

This might be old news to many of you, but I learned something new about Query of Queries today. I was trying to track down a bug where some data was not being found by a QofQ. It turned out that the case sensitivity of the data didn't match, and QofQ is case sensitive! I didn't know that. Here was my query:

<cfquery name="getRealName" dbtype="query">
   select fullName
   from nameQuery
   where uid = '#trim(chAssignedTo)#'
</cfquery>

In that query, the data in "uid" was always upper case, but some of the data in chAssignedTo was lower case, so in those cases ColdFusion didn't match the records. In other words, in a ColdFusion Query of Query "JAM5245" <> "jam5245".

I know this is not a bug, because some databases are case sensitive, and as far as I know that is ANSI SQL compliant. I just never new that QofQ was case sensitive. Learn something new every day. :)

Comments (3) | Print | del.icio.us | Digg It! | Linking Blogs | 875 Views
Comments
[Add Comment]
Yep, but don't forget - you can use lower/upper in QofQ for case-insensitive matches.
# Posted By Raymond Camden | 12/31/07 8:03 AM
Wow, that's weird...I ran into the exact same thing today and I was a bit surprised
# Posted By Jason | 12/31/07 8:36 PM
i think one need to look deep into the QofQ thing. i cough i bug which i couldn't explain.
i use QofQ a lot.
1. my column was coverted to a binary format
2. computation of columns as given wrong result.
# Posted By Arowolo M.A | 4/21/08 6:44 PM
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.9. Contact Blog Owner