CF Upgrade Gotcha (6.1 to 7.0.2)

With the CFMX 6.1 reaching its end of life my company is in the process of upgrading ColdFusion. Unfortunately Adobe chose to EOL CF 6.1 before CF 8 was released, so my company began the upgrade process to 7.0.2. (I'm going to do my best to convince the powers that be to upgrade to 8, but that may be difficult given that it is brand spankin' new.)

Anyway, we have been running 7.0.2 on our local development boxes for the past month or so and we have only really hit one issue; that issue has to do with the QuerySetCell function. In ColdFusion 7 and 8 the QuerySetCell function does type validation. What does this mean? Well CF 6 wouldn't complain if you tried to set a numeric column to a text value like so:

<cfquery name="getTestRS" datasource="test">
SELECT ID, NumCol,TextCol
FROM TestTable

<cfset QuerySetCell(getTestRS,"NumCol","Test",1)>

<cfdump var="#getTestRS#">

In the example above NumCol is actually a int column, however CF 6 has no problem setting it to a text value.

If you try running the above code on CF 7 or 8 you get a nice error message.

Now, as I'm sure we all know, CF represents null values as empty strings in query resultsets. So what if you need to set a numeric field to null? Well CF 7 will let you assign an empty string to any type of field, so to get around this issue in our application we now check to see if the value we are assigning to the field is only whitespace, and if so we insert a blank string.

<cfif Len(Trim(MyVal)) eq 0>
<cfset QuerySetCell(getTestRS,"NumCol","") />
<cfset QuerySetCell(getTestRS,"NumCol",MyVal) />

The above code will still fail if MyVal is anything other than whitespace, but in our case this is the behavior we wanted. If we do move to CF 8 we may look at using the new dbinfo tag to use database metadata to do futher validation on the value. (For example if the TYPE_NAME returned for the column is int, maybe do an IsValid("integer",MyVal).)

I thought I'd share this now because I'm pretty sure I'm not the only one looking at upgrading from 6.1.

BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.