Last year, I wrote a File Management application in ColdFusion9. It allows users to manage their uploaded content online through a web Graphical User Interface (GUI). Sometimes, when a user is moving a folder of files, or renaming a folder of files; the SQL will not execute the as expected. This seems to happen when the files are held open by another user.

As a result, the database does not update with the new information, but the ColdFusion manipulates the files and folders anyway. This causes the application to become unstable since it now has rouge files and folders that are not tracked by the database.

To help reduce this risk, I recently implemented a small level of error checking on the SQL statements using @@ROWCOUNT. This has only been tested with MS SQL2008 and ColdFusion9.

<cfquery name="SetFileValue" datasource="DataBaseName">
   SET NOCOUNT ON
   UPDATE   TableFile
   SET   FileValue = 'SomeValue'
   WHERE   FileID = 2

   SELECT ChangeCount = @@ROWCOUNT
   SET NOCOUNT OFF
</cfquery>

This will result in a value being assigned to SetFileValue.ChangeCount and I can check if it is greater than zero, meaning more that one result has occurred in the update query. I could also check with a SetFileValue.RecordCount, but this is making SQL do more work and returning a scoped value instead of a ColdFusion query result value. If you follow. The @@ROWCOUNT method above was aslo used because it can be retrieved now by MS SQL, not just ColdFusion.

Now I can check that the update actually occurred and resulted in at least one record change before performing the next step. In this case, renaming a file from one value to another on the server.

<cfif IsDefined("SetFileValue.ChangeCount") AND SetFileValue.ChangeCount GT 0>
   <cffile action="rename" destination="#FileNew#" source="#FileOld#">
</cfif>

If you find this post useful please leave a comment and let me know how you used the information.