Unexpected CF_SQL_BIT Behavior In ColdFusion ValueList() Function
Posted August 2, 2007 at 8:09 AM by Ben Nadel
The other day, I was running into a jam that I was having trouble debugging. I was trying to build a layout that would change if a given query had a "1" in a certain column; it didn't matter which row, just any row within that query column. The query was of type BIT in Microsoft SQL Server. To figure out if the 1 existed, I was doing this:
- <cfif ListFind( ValueList( qFormat.is_in_spanish ), 1 )>
This was creating a list of the values in the "is_in_spanish" column and then checking to see if a "1" existed in the value list. Easy right? Well, it wasn't working for me! After about 15 minutes of feeling retarded, I finally figured out why. To demonstrate, let me build a query that has a column of type BIT:
- <cfquery name="qTest" datasource="#REQUEST.DSN.Source#">
- DECLARE @temp TABLE (
- is_true BIT
- );
-
-
- <!--- Populate the temp table. --->
- INSERT INTO @temp
- (
- is_true
- )(
- SELECT
- <cfqueryparam value="1" cfsqltype="CF_SQL_BIT" />
-
- UNION ALL
-
- SELECT
- <cfqueryparam value="0" cfsqltype="CF_SQL_BIT" />
-
- UNION ALL
-
- SELECT
- <cfqueryparam value="0" cfsqltype="CF_SQL_BIT" />
-
- UNION ALL
-
- SELECT
- <cfqueryparam value="1" cfsqltype="CF_SQL_BIT" />
- );
-
-
- <!--- Selec the contents of the temp table. --->
- SELECT
- is_true
- FROM
- @temp
- ;
- </cfquery>
As you can see, we have our @temp table that has one BIT column, which I am then populating using the CF_SQL_BIT type with CFQueryParam. Now, if we CFDump out this query, this is what we get:
| | | | ||
| |  | | ||
| | | |
Here, you can clearly see the 1s and 0s with which I populated the query. And, naturally, a ValueList() on that column should just concatenate all of those values into a list, right?
Wrong!
When you output the value list on this column:
- #ValueList( qTest.is_true )#
... you get this output:
true,false,false,true
For some reason, in the ValueList(), those Yes / No bit values are converted to true/false strings. Apparently, the ColdFusion CFDump is not always showing you exactly what is going to come out of the query.
But, to make matters even more inconsistent, if I were to loop over the query and output the values:
- <!--- Loop over query to output values. --->
- <cfloop query="qTest">
-
- #qTest.is_true#<br />
-
- </cfloop>
... I would get this output:
1
0
0
1
So, when you CFDump out the query and when you loop over the values, you get the 1s and 0s. At least that's some form of consistent. I wonder what it is about the ValueList() that is causing the true/false to come out? Must have to do with the underlying Java representation of the actual result set cell value. Anyway, just be careful of that.
Reader Comments
Just opened some code I worked on a couple of years ago and found this comment: "Tried to use <cfif listFind(valueList(qResults.showDislaimer, ","), 1, ",")> but CF 8 outputs true,false instead of 1,0. Bug in CF?"
I ended up looping over the query. Just wondering if you had a better alternative.
I hesitated checking for "true" in case this issue is ever fixed. Ended up checking for both 1 and true. Welcome back one liner. Don't know why I didn't think of that 2 years ago.
@Brad,
I don't know if this is a symptom of ColdFusion or of the database in use. Definitely irritating though. Luckily, I haven't had to deal with it since this post :)
Thought I was losing my mind on this one and I goggled 'ColdFusion ValueList returning False' and this blog entry came up as the first link. Thanks for posting this so I didn't drive myself any further crazy. ;-) Will just check my list for 0 or false values in case this is ever resolved.
@Katie,
Ha ha, awesome :) Glad I could help you keep your sanity!
