I was recently asked to help another developer finish up a small project. My task was pretty simple, all I had to do was add on two extra fields to an existing table and add a single form. So I add the fields, create the form, define a query for it and test that its all working.
Me being me though, before I push my code to source control I decide to have one more walk through of the rest of the app to learn a bit more about it. Well I start clicking away and within about a minute I get a big error message.
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Unsupported data conversion.
What was unusual about this error message was that it not only came a section of the application I had visited that morning without seeing any error but also whose code was totally separate to what I had been working on. However the error seemed to indicated that something I had done to the table was causing the problem.
This made no sense to me as I had only added a bit and a varchar field to the table and both had defined defaults. So I go into the code and extract the sql that was throwing the error and run it directly on the database. Yet when I ran the query on the database I got no errors. At a loss for any other explanation I dropped the two columns and sure enough now the query ran perfectly through the application as well.
After staring at the query for a few minutes it suddenly dawns on me what I'm looking at. When I write my queries I explicitly define the columns I want back. However the query that the first developer had written and was now throwing errors was this (obfuscated).
select *
from dbo.table
where
-- snip conditions --
I reason that what had happened was that when I hit that page earlier in the day somewhere along the line the column definitions returned with the query were cached. This was fine until I added the two new columns for my part of the application. Suddenly the columns being returned to this query did not match what was being expected.
Aside: I do not know if this was a Java, SqlServer or a ColdFusion behavour. I suspect though that it could be related to the fact that the query was run in a cfc object cached in the application scope, but I don't know this for a fact.
This fix to this was really easy. I modified the trouble query to explicitly list the columns it needed to return (which did not by the way include the new ones I had added) and I added my two columns back to the table.
select
t1.colname
,t1.colname2
-- etc --
from dbo.table as t1
where
-- snip conditions --
I retested the app and sure enough the problem had disappeared. So the moral of the story is this:
Always explicitly list your column names.