I took a call from our help desk last night around midnight. One of our users in Asia had called in reporting an error in one of our applications. I checked our error logs, and sure enough there was an error - an SQL error: SQL0117 - Statement contains wrong number of values. This server happens to be an old ColdFusion server still running ODBC. We're in the process of upgrading t to MX, but that's another story. Anyhow, at first, I was puzzled by the error as we hadn't made any changes to the code in quite some time. Then it hit me. Earlier in the day, we added a new column to one of the database tables used by the application in order to facilitate the upgrade. Harmless enough, right? Wrong.

Turns out the application in question had an SQL insert that looked something like this:

INSER INTO myTable VALUES('#foo#', goo, '#boo#')

Now granted this code was written about 6 years ago, but the developer who wrote it took a shortcut that came back to bite us. Instead of explicitly declaring the fields to be inserted, he or she simply expressed the values to insert. This only works when the number of values exactly matches the number of fields in the database table. In my case, the extra field we added caused the application to break. Now granted this is something that could have been caught in testing/qa, but we didn't have that luxury in this particular instance.

The moral of the story here is that in order to future proof your application, it's best to always declare the columns you intend to insert:

INSER INTO myTable (foo, goo, boo) VALUES('#foo#', goo, '#boo#')