ColdFusion SQL Anamolies

Below are a list of anamolies (whatever that mean) regarding use of SQL in ColdFusion. Knowing these anamolies will make your life easier while developing ColdFusion. I didn't know them and wasted a whole day trying to figure out why my code didn't work.

• ColdFusion doesn't have null. It only has empty string. SQL has both. When CF reads a null SQL field the result is an empty string. When CF reads a empty string SQL field the result is an empty string too.


• Putting SQL in variable and running can be useful for debugging. But single quotes cause problems. Use preservesinglequotes(#QueryString#) function to fix this.


• Convert SQL Date to string before retrieving by ColdFusion SELECT CONVERT(VarChar(10), MyDate, 10) From Mytable


• For Null aggregate, use SELECT TotalCost = Total + ISNULL(ExtraCost, 0.00)


• You do a query SELECT * FROM tablename and you want your code to automatically know the column names. Use the ColumnList variable of the query. PS there are also hidden query variables for RecordCount, CurrentRow and ExecutionTime


• Say I've got a record set of 20 items and I want to access column foo in row 10. Here is the code to do that as an array. RecordSet.foo[10]


• Check if table exists
  select name from sysobjects