I spent most of my morning chasing a sneaky little bug. Basically, once upon a time one of our tables contained a column (to protect the innocent, let's say it was called Meaningless_Data). Several months ago, my team removed the column from the DB schema as well as all references to it in the code.

I grabbed the changes and continued on my merry way... until today, which apparently is the first time I've tried to use a function that touches that particular table. I kept getting an error:

Type: Database
Message: Error Executing Database Query.
Detail: Invalid column name 'Meaningless_Data'.

So it's looking for the column and not finding it. Not surprising, since we deliberately deleted it a long time ago. I triple-checked the code, but couldn't find a single reference to Meaningless_Data in any SQL statement (or anywhere else in the codebase, for that matter).

I drove myself batty for way too long before copying the query straight from CFCATCH.SQL and manually running it against my local DB. Same error. Okay, it's not a Coldfusion issue...

Then I ran it against the DB on our development server. Worked like a charm. Getting warmer...

I generated SQL scripts for the table on each machine and diffed them, and the culprit was revealed. A trigger was pointing to the missing column, and was firing every time I touched the table. The other developer had updated the triggers on his local DB and the dev server, but when I grabbed the schema and code updates, I didn't think to look there.

File under lessons learned.

Shady McShadingtons, Exhibit A:

As a web developer, I often find myself in need of professional help. (Don't we all?) Sometimes all that stands between me and a completed project is a quick Coldfusion, SQL, or Actionscript example. At times like these, I turn to Google.

Much of the time I can find what I need in the first few search results. A code example here, a syntax clarification there. One site that used to bug me, however, was


SQL Date Conversions - More Than You Want To Know

Today I was writing a query that performed some date formatting and found myself using this resource by Manuj Bahl, to which I've often referred but to which I've rarely given any thought. By dumb luck, I fat-fingered one of the codes and ended up with something not listed on the table, but the query ran fine.

Curiousity sufficiently piqued, I wrote a simple Coldfusion script, and a few seconds later found myself looking at a complete table of date/time conversion codes. Most of them are not terribly useful, but in case anyone wants an exhaustive list of every conversion code available, you'll find it behind the cut.


BlogCFC was created by Raymond Camden. This blog is running version 5.9.002. Contact Blog Owner