3

I have a Coldfusion app running on Lucee which connects to a SQL Server database.

When I run the following query directly in SQL Server Manager:

UPDATE article
SET content='20m²'
WHERE id=3159

The column gets populated fine with 20m².

HOWEVER, when run from a cfml page which simply runs this:

UPDATE article SET content='20m²' WHERE id=3159

The column gets populated with: 20m²

As in, this additional  character appears. This also occurs with some other special characters, but most are fine. Is this to do with some configuration of the jdbc connector? I don't see what the difference should be between the above two? Putting the value in a cfqueryparam tag makes no difference.

Thanks

paddyc
  • 79
  • 1
  • 10
  • What version of CF? You might wanna take a look at `cfprocessingdirective` if less than CF11. – Abhishekh Gupta Sep 26 '16 at 18:16
  • 1
    What sort of column is it? Is it NVarchar? If processing directive doesn't work you can try the unicode syntax `content=N'20m²'` ... Also make sure you use "cfqueryparam" to bind your variable. – Mark A Kruger Sep 26 '16 at 18:31
  • 1
    Is your file encoded in Windows1252 or Utf8? Does the server expect the file to have another encoding? – Bernhard Döbler Sep 26 '16 at 19:14
  • Seems like an encoding issue of some sort. – Leeish Sep 26 '16 at 19:56
  • All comments absolutely relevant and helped me understand, although with my above example the cfprocessingdirective tag with the char encoding set to utf-8 worked! Thanks – paddyc Sep 26 '16 at 20:47
  • @paddyc - BTW, cfprocessingdirective is only needed because the special characters are hard coded *into the cfm file*. If the real code passes the input as some sort of form parameter, it should not be necessary. – Leigh Sep 26 '16 at 21:13
  • @BernhardDöbler - You should post that as an answer. – Leigh Sep 27 '16 at 12:36

1 Answers1

3

If it's hard coded, I believe you'll want to make sure you save that file in Unicode UTF-8.

enter image description here

Also make sure your JVM arguments will process that as well. Admin > Server Settings > Java and JVM. Add " -Dfile.encoding=UTF-8" to the Arguments.

enter image description here

Jules
  • 1,941
  • 15
  • 18
  • Since there is a db query involved, there is a little more to it than just the change the page encoding ;-) Most likely the hard coding is just for testing anyway. So other side of the question is how to ensure the correct values are transmitted to the database *and* stored properly. It would be good to elaborate on the full cycle ... – Leigh Sep 27 '16 at 21:51
  • Process of elimination - make sure the file with '20m²' is saving and being read back properly. It could be the db end of things are working fine. – Jules Sep 28 '16 at 19:30