The Dreaded Mismatched Column or Data Type Error Revisited

This annoying error that occurs when using JDBC and MS SQL has been around for a while. The JDBC system in Coldfusion caches information about the tables you access. If you change a table (add a column for example) the column ids get out of whack and they no longer match the correct column. For Example, the first time you run a query you might get back the following:

So far so good. But let's say you add a column called "bestFriendsGirl" to the table after the bestFriend column and then re-run the query. You might end up with:

Anyone who is married will see this as a problem. Not only is your spouse now your best friend's girl, but the person labeled as your spouse is.... who knows? Some other column from the DB no doubt - hopefully not your mother in law.

If fact, what often happens is that the offset results in a "data type mismatch". The specific error is "Invalid data type for column A" or something similar. For example, if the username is now in the place of the userid column and you try to insert a username into userid (which we assume is an int) you will end up with an error resulting from attempting to insert an int into a character column. Incidentally this error is much preferable than the alternative of matching data types incorrectly. When the offset exists but the data types are the same you end up in the unenviable position of inserting data from column A into column B. This could result in Bob Johnson from Pokipsy being inserted as Johnson Pokipsy from Bob. That's a much more difficult error to find and repair - and it could even go undetected for a while.

The Fix

The rule of thumb is to be careful when adding columns to tables in your database - especially on production! Make sure you have a plan to mitigate this error. Possible solutions often suggested are:

• Restart CF - not always advisable or possible.
• Add spaces or alter all the queries - a rather labor intesive process.