ColdFusion Muse

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:

SQL SeesCF sees
usernameusername
useriduserid
fnamefname
lnamelname
bestFriendbestFriend
spousespouse

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:

SQL SeesCF sees
usernameusername
useriduserid
fnamefname
lnamelname
bestFriendbestFriend
bestFriendsGirlspouse
spouse?

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.
One of my quick and easy fix favorites is simply to drop and recreate the Datsource in the Coldfusion administrator. If you have appropriate error trapping this can have a very small impact on your users and you can make it happen inside of a 30 second window assuming you have all the settings and permissions lined out ahead of time.

Comments
Ben Nadel's Gravatar While I am not sure what your queries look like, just wanted to add a note that often times this problem is caused by the use of "SELECT *". Replacing the "*" with the desired column names helps.
# Posted By Ben Nadel | 4/17/08 12:23 PM
mark kruger's Gravatar Unfortuantely, just using column names will not guarantee that the problem will not occur. That being the case it is somewhat ill-advised to rely on it as a way of mitigating this particular error - if you see what I mean. People feel reassured when they are not using the asterisk, but that does not mean you shouldn't double check after making schema changes.

That being said, I think there are many other reasons why the use of the asterisk should be avoided in nearly all cases :)
# Posted By mark kruger | 4/17/08 12:37 PM
Ben Nadel's Gravatar @Mark,

I did not mean to imply that that was a fix-all. I just know from experience that it can cause this problem. But, agreed, SELECT * should be avoided :) And, after making any changes to the DB, things should probably be rechecked as a matter of principle.
# Posted By Ben Nadel | 4/17/08 12:40 PM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.