Skip to main content
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Ralph Whitbeck
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with: Ralph Whitbeck

Unexpected CF_SQL_BIT Behavior In ColdFusion ValueList() Function

By
Published in , Comments (5)

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:

ColdFusion CFDump On Query With Column Of Type CF_SQL_BIT

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.

Want to use code from this post? Check out the license.

Reader Comments

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

15 Comments

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.

15,811 Comments

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

1 Comments

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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel