I’ve said this before and I think I’ll be saying it for a while yet, ColdFusion is not a database server, and should not be used as one. Or put differently, if your DBMS can do the job, let it do so.
Case in point: Earlier today I was giving someone a hand with an application, and was looking at some database interaction code. When I asked what the code was doing the developer explained that he needed datetime stamps for record creation and update. And so, his SQL INSERT and UPDATE statements (in
So what is wrong with that? Lots! For starters, there is no logical reason to manipulate those columns within CFML, ColdFusion does not need those values and does no special processing to obtain them. In addition, ColdFusion is not going to perform that processing as quickly as your DBMS would. Furthermore, the first time that rows are inserted or updated using a client other than ColdFusion things would break (or require rewriting). And that is just the start of it.
A better solution would be to let the database do the work, that is what DBMSs are designed to do. This particular application was using SQL Server, so I’ll use that in this example. The datetime stamp for record creation could simply use a DEFAULT of GETDATE() to set the date and time to the system date and time on record insertion. To set the update datetime stamp a trigger could be use so that any time a row is updated a SQL UPDATE statement would be issued to update the appropriate column.
That’s the right way to do it.
If you want to read more on this topic, see this recent CFDJ column: http://www.sys-con.com/story/?storyid=43786.