Unexpected CF_SQL_BIT Behavior In ColdFusion ValueList() Function
Posted August 2, 2007 at 8:09 AM
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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- #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:
Launch code in new window » Download code as text file »
- <!--- 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.
Download Code Snippet ZIP File
Comments (0) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
There are no comments posted for this web log entry.
