Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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.

Data Conversion Error

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.

I Only Added Fields

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.

Column Caching

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).

<cfquery name="someQuery">
      select *
      from dbo.table
      -- 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.

Always Explicitly List You Columns

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.

<cfquery name="someQuery">
            -- etc --
      from dbo.table as t1
      -- 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.

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.


Archives Blog Listing

Tag Listing

Learn CF In A Week


Fork me on GitHub