International Characters in DB from CF 4.5

Every now and then you get a little job that seems simple and straight forward, but is anything but. I just encountered one of those.

I recently inherited an old CF application, written in CF 4.5 or 5.0.

The cfml templates have Japanese characters simply pasted in the file, with a meta tag in the HEAD section of the HTML that sets the character set to x-sjis (shift japanese).

That displays great in CF5 but CFMX pushes everything as UTF-8 by default, so I had to put a CFPROCESSINGDIRECTIVE tag on every freakin CFML template in order to get the text to display correctly (that tag does not propogate via Application.cfm).

I thought that was plenty of suffering for one job, but nope, there's more -- Japanese data stored in a SQL Server DB.

I could not get CFMX to display the data correctly. The CFML templates displayed the inline Japanese characters just fine, but the output from the query still looked to be displayed as utf-8.

After a lot of wasted time, I have not found a way to display that data as it was intended.

The only thing left to try was to modify the data in the database so it would display correctly. But how are you supposed to do that????

Steve Nelson came up with the idea of putting the content from the query in a file, and immediately cfincluding it. We tried that, and it worked! WTF?

So I took it a step further and used that code to help modify the data in the DB, as shown below. Note the data is stored in ntext fields, and I'm using the "N" trick so SQL Server stores the characters correctly. Finally, I can display the data correctly.