2

I have a mysql database set up with the Character Set as utf8 and the Collection set as utf8_general_ci.

I can see the Russian text in the database is all fine.

I have added a <cfprocessingdirective pageEncoding="utf-8"> in the coldfusion pages. If I type in russian text directly in the cfm page, it displays fine in the browser.

However, If I us a cfquery to pull in the data to display it in the browser, it displays incorrectly i.e. ЎБÐ.

my cfquery is a very simple...

<cfquery name="getStatic" datasource="#session.odbcname#">
SELECT  *
FROM    static_id
WHERE   static_displayname = 'home'
AND     static_status = 'online'
</cfquery>

and then use a cfoutput to display the data #getStatic.data#.

any ideas on how to get the data pulled in to display correctly?

Thanks

elixireu
  • 255
  • 3
  • 15
  • cfprocessingdirective doesn't affect dynamic queries. 1) Did you try specifying character set in the dsn url? https://stackoverflow.com/a/29595897/8895292 2) You said "I have a mysql *database* set up with..." double check the actual *table and column* inherited the correct settings. – SOS Feb 21 '18 at 13:55
  • Did you try my 2 suggestions above? If you're not getting the right output, then 1 or more of those settings probably isn't what you think they are... If you haven't already, double check the column settings first `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'static_id'`. – SOS Feb 21 '18 at 20:47
  • Hi Ageax, thanks for your input. Sadly no joy, I have added various connection strings form your suggested link and others with no joy. I have also created new tables in mySQL with Character Sets utf8 and utf8mb4 and also with a Collection of utf8_general_ci and utf8mb4_general_ci and sadly no joy. :-( – elixireu Feb 22 '18 at 09:09
  • That is very odd.. Maybe it is something specific to the hosting environ? I tried those settings for grins ... and outputting Russian text works no problem. Aside from asking your host, have you tried it on another machine? Easy enough to spin up a dev instance and try it there. – SOS Feb 22 '18 at 14:36
  • 1
    Hi Ageax, many thanks for your help, it looks like your idea of trying another machine works - it looks like the machine I was using was inputting the Russian text fine but for what ever reason it was not importing into the mySQL correctly. Even thou I could view Russian text in my mySQL client. – elixireu Feb 22 '18 at 16:54
  • Ohh, that makes sense... output doesn't work cause it's already corrupted! Glad you figured out the problem. Was the "import" done with CF queries or some other tool? – SOS Feb 22 '18 at 18:29

2 Answers2

1

Add this to the top of your page.

<cfprocessingdirective pageencoding = "utf-8">

Or better yet, add it to the onRequest() event of your application.cfc if you need it on every page.

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-p-q/cfprocessingdirective.html

user9263373
  • 1,054
  • 8
  • 15
  • Hmm, got a downvote. If anonymous downvoter cares to to explain what's wrong with my answer I'd be glad to hear it. – user9263373 Feb 20 '18 at 22:19
  • Hi user9263373, thanks for your answer. sadly I Have tried using the `cfprocessingdirective` but it only works if I have russian text directly on the cfm page. Sadly its not working when I pull in text from the mySQL database. – elixireu Feb 21 '18 at 10:05
  • @elixireu Gotcha. Now I see why my answer was bad... It's clearly in your post and I didn't see it, so my apologies for answering without reading. Take a look at this as it might help https://stackoverflow.com/questions/202205/how-to-make-mysql-handle-utf-8-properly – user9263373 Feb 21 '18 at 13:54
  • Here's another that might help as well https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 – user9263373 Feb 21 '18 at 14:13
  • Hi, many thanks user9263373 for your help on this, sadly I have tried them all but with no success. I have sent a message with the hosting company to see if there is an issue with mySQL as it is all in utf-8mb4. – elixireu Feb 21 '18 at 19:01
  • @elixireu Here's another you can try https://coldfusion.adobe.com/2017/12/how-to-use-unicode-for-chinese-characters-in-coldfusion-and-mysql/ – user9263373 Feb 21 '18 at 19:30
1

ЎБ is 'Mojibake' for ЎБ

In UTF-8, the hex is D08E D091. If that is treated as latin1, hex D0 8E D0 91 means ЎБ.

But, it could also be "double-encoding", and the hex in the table is C390 C5BD C390 E28098. Please do SELECT HEX(col) ... to see which you have.

Double-encoding is a silent error -- usually everything looks ok, while the stored value is wrong.

Look for 'Mojibake' in here for a list of things to check in your code.

(For Cyrillic, CHARACTER SET utf8 and utf8mb4 act identically. They differ primarily for Emoji and Chinese.)

My skimpy notes on ColdFusion say

<cfprocessingdirective pageEncoding="utf-8">

<cffile
action="read"
file="#settings.csvfile#"
variable="autodata"
charset="utf-8">

(Ugh. I counted them. I have notes on 45 3rd-party packages such as ColdFusion. Forgive me if I don't speak authoritatively on one of them.)

Rick James
  • 135,179
  • 13
  • 127
  • 222