ColdFusion in Context: Bad Bits

Suppose you're using the common short cut for checking if a value is greater than zero: "cfif myVariable" instead of "cfif myVariable is gt 0". There is a rare situation where this doesn't work. The short cut is still good and should be used; because, it enhances understanding as well as speed. Here, we'll discuss the sole exception to this principle so you'll know a workaround when you see it.

Short Cut

When you're evaluating yes/no statements, this code works very well. Sometimes the value will be zero. Other times, it won't be zero. You don't care HOW much greater than zero as long as it's not zero (or as long as it IS zero, if the sense is reversed as in the last statement). 

<cfif myQuery.recordcount>
  {do something}
</cfif>
<cfif myVariable>
  {do something}
</cfif>
<cfif hisExpression>
  {do something}
</cfif>
<cfif not yourVariable>
  {do something}
</cfif>

Long Way Around

It's faster to write and easier to read the short cut than to work with the lengthy method. When the code takes the long route, you find yourself evaluating both pieces of the expression to see if there's a catch somewhere. Instead of knowing at a glance that the conditions are intended to be true/false, you look at each one to see the exact value at which the decision point is found. The only statement where this makes a difference is the last statement in the following list, but you had to review all of them to know that. 

<cfif myQuery.recordcount gt 0>
  {do something}
</cfif>
<cfif myVariable gt 0>
  {do something}
</cfif>
<cfif yourVariable gt 5>
  {do something}
</cfif>

Bad Bits

The short cut fails - with a noisy error, fortunately - IF the value in the shortcut evaluation is NULL. When might this occur?

ColdFusion won't cause this error; you can't define a variable as NULL.

You'll never see this with Microsoft Access; it forces bit fields to zero (false).

However, you may see it if you create bad bits by hastily modifying a table in MS SQL Server or Oracle.

Suppose you've added a column to an existing table, and the column is a bit field. You probably did this interactively through whatever graphic user interface the database engine provides. You were going to force this field to a known value in your code, weren't you, when your code is eventually used to make changes to the table? However, you haven't used your code yet. So, the table has null values in bit fields. The table has bad data.

If you have a "heavy" database engine such as MS SQL or Oracle, try to demonstrate the problem. Create table BadBit with a text field Name and a bit field Status. Add a few rows through your database interface, filling in Name but leaving the Status undefined. Then run this code, placed in BadBits.cfm, against this bad data. 

<cfquery name="badBitList" datasource="context">
select *
from BadBit
order by Name
</cfquery>
<table>
<tr><td>NAME</td><td>STATUS</td></tr>
<cfoutput query="badBitList">
</tr><td>#Name#</td>
<td>
<cfif Status>
  Awake
<cfelse>
  Asleep
</cfif>
</td></tr>
</cfoutput>
</table>

The code will run just fine in Microsoft Access. However, it may give you an error of this form when other database engines are used:

Error Occurred While Processing Request
Error Diagnostic Information
Inside Text
Cannot convert to boolean.
Please, check the ColdFusion manual for the allowed conversions between data types

The error occurred while processing an element with a general identifier of (CFIF)...

Assumption

When you see this error, the problem is really in your database, not your code. However, you can change your code to hide the bad bits. Here are two ways; there are more. Replace the if-else-endif cluster in a copy of badbits.cfm with the following code: call the result badbits2.cfm. 

<cfif val(Status)>
  Working
<cfelse>
  Retired
</cfif>
<cfif Status is 1>
  Up
<cfelse>
  Down
</cfif>

When you use these forms of evaluation, ColdFusion compensates for the bad data. It assumes you meant for the field to be false unless it's true.

However

ColdFusion is not necessarily the place where this assumption should be made. False and null are not the same thing. False means you know the answer, and it's false. Null means you don't know the answer. You don't know the status: the value of whatever condition is being tested.

The following code admits this, making the bad data obvious so that the user will fix it if you didn't care to [OUCH] or couldn't (because the data came from an external source). Replace the if-else-endif cluster in a copy of badbits.cfm with this code: call the result badbits3.cfm. 

<cfif Status is 1>
  Heads
<cfelseif Status is 0>
  Tails
<cfelse>
  The coin landed on edge
</cfif>

In Summary

Continue to use short cut evaluation. If you have to use these workarounds for bad bits in your database, consider fixing your data. =Marty= [After publication, a table tag was corrected, and the method of viewing the result of different approaches was formalized.]