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?
When you output the value list on this column:
#ValueList( qTest.is_true )#
... you get this output:
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:
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.
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.
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.
Ha ha, awesome :) Glad I could help you keep your sanity!