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
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
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!
If I’m not mistaken Rob Gonda’s sqlcfc does this – but for all CRUD functions. You may want to check it out.
http://www.robgonda.com/blog/projects/sqlcfc
Peter,
. 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.
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
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
At the risk of pushing my own wares, DataMgr does this (and much more) as well and supports several databases.
It can also use the same syntax for logical deletes.
http://www.bryantwebconsulting.com/cfcs/