ColdFusion Function to Delete Generic Records from Generic Tables

Thought I’d share a simple generic ColdFusion delete function I wrote this morning. We develop many sites where I work and often they each end up with their own set of unique content management tools. All of the sites share the need for CRUD (create, read, update, delete) functionality and often the query syntax is the same for data retrieval and deletion:

DELETE
FROM table
WHERE table_id = variable_id

In writing a component for some of the tools I started wondering if, in addition to data, I could also pass the names of database tables and columns to create an all-purpose generic delete function.
I started by created a generic component, generic.cfc and wrote my function:

<cffunction name=”deleteRecordByID” access=”public” returntype=”Boolean”
hint=”Deletes a record from a passed table based on passed id” >
<cfargument name=”tableName” type=”string” required=”yes” />

<cfargument name=”idColumnName” type=”string” required=”yes” />
<cfargument name=”idValue” type=”numeric” required=”yes” />
<cfargument name=”dsn” type=”string” required=”no” default=”myDSN” />
<cftry>
<cfquery name=”qDelRecord” datasource=”#arguments.dsn#”>
DELETE
FROM #arguments.tableName#
WHERE #arguments.idColumnName# = <cfqueryparam value=”#arguments.idValue#” cfsqltype=”cf_sql_integer”>
</cfquery>
<cfcatch type=”any”>
<cfreturn false />
</cfcatch>
</cftry>
<cfreturn true />
</cffunction>

Then I gave it a shot:

<cfset objGeneric = createObject(“component”,”generic”) />
<cfset deleteResult = objGlobal.deleteRecordByID(“myTableName”, “myTableIDColumn”, idValue, request.dsn) />

To my surprise, er I mean just as I expected, it was successful. Whether you find the deleteByRecordID function clever or utterly ridiculous, the knowledge that you can pass database object names for query execution can be useful.

-rG

4 thoughts on “ColdFusion Function to Delete Generic Records from Generic Tables

  1. I do something similar. You might want to consider instead of having a generic.cfc, call it something like BaseDAO.cfc and then have all of your DAOs extend BaseDAO. That way if you HAVE custom delete rules for a given object (maybe with delete user it needs to fire a trigger to do something else or update logs) you’ll be able to overload by writing a UserDAO.deletebyID(), and for all other objects they can just use the underlying BaseDAO method.

    Just a thought!

  2. Peter,
    I actually had a feeling I might get a comment from you on this one. That’s a great idea that I’d probably implement if we didn’t already have a new CMT being developed by a coder with much more skill than I 😉 . Not sure if you know who I am but I’ll give you a hint if you don’t….I’ll see you at lunch tomorrow.

    Todd,
    Thanks for the link. That’s exactly the concept I was shooting for. I have to get my head out of ActionScript more often.

    -rG

Comments are closed.