About a year or so ago, I blogged about problems I was having with ColdFusion 9 ORM and using its entityDelete() function to delete items that ultimately could not be deleted because of foreign key constraints within the database. The problem I was having, in a nutshell, was I couldn't trap the error. Despite all kinds of error trapping, a nasty Hibernate exception kept bubbling up.

I was stuck. I tried posting the problem to cf-talk, but didn't find much help.

Some time later, I discovered the cf-orm-dev Google Group and again posted my quandary. Thankfully, I got several replies. One of those was from Mark Mandel (of Transfer and ColdSpring fame), someone in the CF community that I respect very much. He essentially said using try/catch in the manner I was attempting was bad coding practice. I had never considered that -- it was one of those things I always did. Another person suggested that I use ORM events, namely preDelete(). ORM events were new to me, so I investigated.

ColdFusion ORM includes a bunch of events which get called at various states of a database request's lifecycle. Think of it like Application.cfc for database calls. Among these include preLoad, postLoad, preInsert, postInsert, preUpdate, postUpdate, preDelete, and postDelete. Dan Vega has a really great breakdown of all these on his website. The thing I discovered early on is these events aren't enabled by default. You have to switch on eventhandling in ormsettings, like so:

this.ormsettings.eventhandling = true;

I decided that the preDelete() event would allow me to check for constrains, and if I found anything, eaisly back out of the delete. So, in order to delete something we have to load up a record, then run it through entityDelete():

objSong = ("Song",rc.songID,true);
entityDelete(local.objSong);

At this point, preDelete() gets called in Song.cfc, where I can check for any constraints:

<cffunction name="preDelete" access="public" output="false" returntype="boolean">
    <cfquery name="local.qryChildRecords">
        SELECT ServiceItemID AS ID FROM ServiceItems WHERE SongID = <cfqueryparam value="#this.getSongID()#" cfsqltype="cf_sql_integer" />
        UNION
        SELECT EventItemID AS ID FROM EventItems WHERE SongID = <cfqueryparam value="#this.getSongID()#" cfsqltype="cf_sql_integer" />
        UNION
        SELECT SongFileID AS ID FROM SongFiles WHERE SongID = <cfqueryparam value="#this.getSongID()#" cfsqltype="cf_sql_integer" />
    </cfquery>
    <cfif local.qryChildRecords.RecordCount>
        <!--- Child records exists, abort the delete --->
        <cfset this.setSongID(javaCast("null","")) />
        <cfreturn true />
    </cfif>
    <cfreturn false />
</cffunction>

The way preDelete() works is if it returns a true value, the delete is aborted. While that's all fine and good, it doesn't do anything to inform the user nothing was deleted. So in order to detect that, I set the primary key of the object (in this case SongID) to NULL. Then, after entityDelete() is called I know if the delete happened or not. There may be a better way, but that seems to work for the vast majority of what I've done.

If anyone has any ideas or suggestions on how to improve on this, I'd love to hear them!

In a project I've been working on, I wanted a way to remotely backup a mySQL database on to Amazon S3. Because the site was hosted on a shared server, I also didn't have access to the command line.

There are probably dozens of different ways to go about doing this, but the solution I arrived at is incredibly simple and easily lends itself to a quick restore script.

First, we need to get a list of tables in the database:

<!--- Get data  --->
<cfquery name="qryTables" result="stcTables">
    SHOW TABLES
</cfquery>

We will then get all the data from each of those tables and create a WDDX packet out of each (which is a XML representation of ColdFusion query object). Each of these packets is written in to a text file.

<!--- Pull down data and export each table's data into WDDX file--->
<cfset variables.strTempDirectory = "#expandPath("./")#temp\" />
<cfloop query="qryTables">
    <cfset variables.strTableName = qryTables[listFirst(stcTables.ColumnList)] />
    <cfquery name="qryData">
        SELECT * FROM #variables.strTableName#
    </cfquery>
    <cfwddx
        action="cfml2wddx"
        input="#qryData#"
        output="variables.strData" />
    <cffile
        action="write"
        file="#variables.strTempDirectory##variables.strTableName#.txt"
        output="#variables.strData#"
        charset="utf-8" />
</cfloop>

Finally, we'll take all those text files and zip them into a single file, which will be sent to Amazon S3 (thanks to the CF 9.01 enhancement).

<!--- Package WDDX files into a ZIP file --->
<cfset variables.strBackupZip = "database-backup-#dateFormat(now(),"yyyy-mm-dd")#.zip" />
<cfzip
    action="zip"
    file="#variables.strTempDirectory##variables.strBackupZip#"
    source="#variables.strTempDirectory#"
    filter="*.txt" />

<!--- Send to Amazon S3 --->
<cffile
    action="copy"
    source="#variables.strTempDirectory##variables.strBackupZip#"
    destination="s3://your-bucket-name/database/#variables.strBackupZip#"/>

While doing development for my employer, I work primary locally – except for the database. For that I connect remotely to their development SQL Server box. The setup has been working famously for some time now, despite an occasional hiccup. However, more recently things started to slow down to a snails pace. 30 seconds to run a page pace. This was noticeable not only in ColdFusion but also though SQL Management Studio. Very simple queries would take in excess of 5 secs to run. After confirming the server was fine, I started doing some digging. After various tweaks and experiments, I discovered that Windows 7 has some sort of network auto-tuning feature which was the root of the problem. All  I needed to do was run the following at the command line:

netsh interface tcp set global autotuninglevel=normal

After a reboot for good measure, my SQL connections are slick and quick again. I have to credit the following article for this find. http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/be6fc3c4-5c70-480d-9175-0d95a898a41c/