Im currently in the process of converting my site from Fusebox to FW1 so this is going to be a short post on a technique I learned a few years ago but I still think its useful especially for new developers.

Using CFTransaction To Roll Back Failed Query Groups

Up to a few years ago I did all my data modification in stored procedures which of course allows you to do transactions. However when I switched to writing my queries at the application level I discovered that CF also allowed for transactions with the <cftransaction> tag. (CF just rocks). One problem that I had though was that if a transaction failed there was no direct way of logging the failure.

To demonstrate take the following code block. Every time this is run the first query tries to do an insert only to be rolled back by the failure of the second query.

<cftransaction>
      <!--- This is a good query --->
      <cfquery name="testInsert" datasource="SQLServer_test">
            INSERT into table_1 (test1) values ('hello')
      </cfquery>
      
      <!--- This is a bad query --->
      <cfquery name="testInsert" datasource="SQLServer_test">
            INSERT into table_1 (iDontExist) values ('hello')
      </cfquery>
</cftransaction>

While the above is fine as far as data consistency we still need to be able to detect and handle the fail at the application level.

Combining CFTry and CFTransaction

In order to handle a failed transaction at the application level we simply pair the cftransaction tag with a cftry as follows.

<cfset message = "All is right with the world">

<cftransaction action="begin">
<cftry>
      <!--- This is a good query --->
      <cfquery name="testInsert" datasource="SQLServer_test">
            INSERT into table_1 (test1) values ('hello')
      </cfquery>
      
      <!--- This is a bad query --->
      <cfquery name="testInsert" datasource="SQLServer_test">
            INSERT into table_1 (iDontExist) values ('hello')
      </cfquery>
      
      <!--- 
            If both of the above queries succeed then commit 
            Using this tag here is actually optional but I put it in for clarity
      --->
      <cftransaction action="commit" />
      
      <!--- something happened, roll everyting back ---> 
      <cfcatch>
            <!--- Rollback (note the self closing tag for compatibility with Railo --->      
            <cftransaction action="rollback" />
            <cfset message = "oops an error happened trying to do this, go shout at Steven">
      </cfcatch>
</cftry>
</cftransaction>

<cfoutput>#message#</cfoutput>

When you run this code snippet every time it fails the user gets a message to go shout at Steven until he fixes the bad query and then all is right with the world.

A Note To Railo Users

This technique works on Adobe CF and Railo (I haven't tried Openbd but I'd be surprised if it didn't) but in Railo you have to self close the commit & rollback <cftransaction> tags. This caught me out when I first time I migrated a site to Railo.