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 Plaza Hotel 2012 (New York City) with:

Unexpected CF_SQL_BIT Behavior In ColdFusion ValueList() Function

By Ben Nadel on
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.


Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

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.