I found out that ColdFusion 9 ships with MySQL drivers that don’t play nice with aggregate functions like SUM…
I was running a fairly complex query just fine in (Sequel Pro for Mac (probably the best MySQL client out there for Mac). When I added this complex query to a ColdFusion app I was working on — BOOM — ByteArray objects cannot be converted to strings.
The data I was working with was decimal / integer data. CFDump showed that they really were byte arrays.
Well, it seems that the latest and greatest MySQL drivers that ship with CF9 aren’t working so hot for Adobe. After a little research, I noted that there were three workarounds I found on the web, none of which were great:
1. Revert your MySQL drivers back two or so versions
2. Use CAST or CONVERT to encode to Latin (yes, Latin) encoding on the SQL side
3. Use CF function “toString” on the ColdFusion side
I choose not to go messing around with old versions of drivers; but this solution sounded like it might work. However, if you’re in the process of upgrading servers to CF9 and you don’t want to change the SQL or the CF of the underlying application — this might be the best option.
Since I had 200 lines of SQL with a lot of aggregate “fun”, updating the SQL wasn’t an attractive option. I tested this workaround anyway but couldn’t get to stop throwing those ByteArray errors. I suppose that (with enough time) I may have gotten it to work with CAST(someColumn AS decimal), but I would’ve had a bunch of SQL to update so, on to “curtain number three”.
Wrapping the output value from the query with “toString” did the trick for me. There were only a dozen places this change was needed in the code and –since it was new code and all “behind the scenes” stuff– it was the least impact/risk.
All in all, a bit disappointing to find that shipped drivers aren’t working with ColdFusion (or vice-versa).