Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the NYC Tech Talk Meetup (Aug. 2010) with:

Unexpected CF_SQL_BIT Behavior In ColdFusion ValueList() Function

Posted by Ben Nadel
Tags: ColdFusion, SQL

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.



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.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
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.