Problem inserting text with special Hungarian characters into MySQL database

[Guest]

When I insert text into my MySQL db the special Hungarian characters (ő,ű) they change into "?".
When I check the <cfoutput>#FORM.special_character#</cfoutput> it gives me the correct text, things go wrong just when writing it into the db. My hosting provider said the following: "please try to evidently specify "latin2" charset with "latin2_hungarian_ci" collation when performing any operations with tables. It is supported by the server but not used by default." At my former hosting provider I had no such problem. Anyway how could I do what my hosting provider has suggested. I read a PHP related article that said use "SET NAMES latin2". How could I do such thing in ColdFusion? Any suggestion? Besides I've tried to use UTF8 and Latin2 character encoding both on my pages and in the db but with not much success.
I've also read a French language message here in this forum that suggested to use:
<cfscript>
setEncoding("form", "utf-8");
setEncoding("url", "utf-8");
</cfscript>
<cfcontent type="text/html; charset=utf-8">
I' ve changed the utf-8 to latin2 and even to iso-8859-2 but didn't help.

Thanks, Aron

[Newsgroup_User]

Prisec wrote:
> When I insert text into my MySQL db the special Hungarian characters (?,?) they
> change into "?".

"?" indicates your data was garbaged by encoding issues.

> When I check the <cfoutput>#FORM.special_character#</cfoutput> it gives me the

what encoding are you using in cf (if you do nothing or your host hasn't changed
anything, it's utf-8)? what version of cf? what db driver are you using (it
should be JDBC NOT ODBC)? if JDBC, are you using the required connection string
to tell the driver what encoding to use? what version of mysql?

> <cfscript>
> setEncoding("form", "utf-8");
> setEncoding("url", "utf-8");
> </cfscript>
> <cfcontent type="text/html; charset=utf-8">

if your encoding back to front is utf-8.

i'd recommend using unicode (utf-8) & straightening out your encodings (from the
browser to your db).

[Guest]

I read that it would be the most straightforward way to do everything in UTF-8 because it handles well special characters so I've tried to set up a simple testing environment. Besides I use CF MX7 and my hosting provider creates the dsn for me so I think the db driver is JDBC but not sure.

1.) In Dreamweaver I created a page with UTF-8 encoding set the Unicode Normalization Form to "C" and checked the include unicode signature (BOM) checkbox. This created a page with the meta tag: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />. I've checked the HTTP header with an online utility at delorie.com and it gave me the following info:
HTTP/1.1, Content-Type: text/html; charset=utf-8, Server: Microsoft-IIS/6.0

2.) Then I put the following codes into the top of my page before everything:
<cfprocessingdirective pageEncoding = "utf-8">
<cfset setEncoding("URL", "utf-8")>
<cfset setEncoding("FORM", "utf-8")>
<cfcontent type="text/html; charset=utf-8">

3.) I wrote some special Hungarian chars (<p>őű</p>) into the page and they displayed well all the time.

4.) I've created a simple MySQL db (MySQL Community Edition 5.0.27-community-nt) on my shared hosting server with phpMyAdmin with default charset of UTF-8 and choosing utf8_hungarian_ci as default collation. Then I creted a MyISAM table and the collation was automatically applied to my varchar field into wich I stored data with special chars. I've checked the properties of the MySQL server in MySQL-Front prog and found the following settings under the Variables tab: character_set_client: utf8, character_set_connection: utf8, character_set_database: latin1, character_set_results: utf8, character_set_server: latin1, character_set_system: utf8, collation_connection: utf8_general_ci, collation_database: latin1_swedish_ci, collation_server: latin1_swedish_ci.

5.) I wrote a simple insert form into my page and tried it using both the content of the form field and a hardcoded string value and even tried to read back the value of the #FORM.special_char# variable. In each cases the special Hungarian chars changed to "q" or "p" letters.

Can anybody see something wrong in the above mentioned or have an idea to test something else?
I am thinking about to try this same page against a db on my other hosting providers MySQL server.
Here is the to the form: http://209.85.117.174/pages/proba/chartest/utf8_1/form.cfm

Thanks, Aron

[Newsgroup_User]

Prisec wrote:
> 4.) I've created a simple MySQL db (MySQL Community Edition

not an mysql expert so i will take your word on this.

> 5.) I wrote a simple insert form into my page and tried it using both the
> content of the form field and a hardcoded string value and even tried to read
> back the value of the #FORM.special_char# variable. In each cases the special
> Hungarian chars changed to "q" or "p" letters.

i guess it's your db driver. check w/your host.

> http://209.85.117.174/pages/proba/chartest/utf8_1/form.cfm

i pumped some unicode data from my unicode test page:

http://www.sustainablegis.com/unicode/

into it & yes there's something wrong w/the encoding. if you're sure about the
mysql & the rest of your cf pages looks ok, i guess it's the db driver.

btw this is how it's done w/sql server:

http://www.sustainablegis.com/unicode/greekTest.cfm

[Daverms]

Hi,

You should put your charset (at the time of your DB Creation through create database command) as one of the MySql's supported Central Europian Character Sets (in where actually the Hungarian Language falls in)..

Syntax :

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [create_specification] ...]

create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name


For MySql Supported Central Europian Character Sets :

http://dev.mysql.com/doc/refman/5.0/en/charset-ce-sets.html

[Newsgroup_User]

Daverms wrote:
> You should put your charset (at the time of your DB Creation through
> create database command) as one of the MySql's supported Central Europian
> Character Sets (in where actually the Hungarian Language falls in)..

it really is a better idea to have the *one* encoding, front to back. mixing
encoding like that is going to cause trouble sooner or later.

[Guest]

I've created the test db on another MySQL server at another hoster provider changed the DSN and the result is the same: it writes the string into that db with p and q instead of the spec chars. I'm a bit confused now.

[Newsgroup_User]

Prisec wrote:
> I've created the test db on another MySQL server at another hoster provider
> changed the DSN and the result is the same: it writes the string into that db
> with p and q instead of the spec chars. I'm a bit confused now.

doesn't really tells us much, ask your host about the db driver.

[Guest]

Some new info about the advancements in my project:

I've tried to make the insertion at a third hosting provider's MySQL server with my 'everything is UTF-8' test case and IT'S DONE! There are my lovely spec chars :-)

Then I've checked the char encoding according -Per's tip in all of my so far used test MySQL dbs and it reported that 'CHARSET=utf8 COLLATE=utf8_hungarian_ci' so this part seems to me OK.

I asked my hosting provider where my production app should run about the db driver and they told it's JDBC (what version of Jconnect still donno') and they are ready to append &characterSetResults=UTF-8 to the JDBC url connection string (somebody told this tip also as a possible soultion) but they asked me to provide the complete connection string to be used for my datasource. I've tried to compose it in my localhost development environment in ColdFusion Admin but it gave me a Connection verification failed error. So I think I did something wrong and need help to write the correct connection string that can be passed to the hosting provider. So the connection string structure I tried to use in the JDBC URL field of the datasource area of CFAdmin is something like this: jdbc:mysql://someipaddresshere/mydbname&characterSetResults=UTF-8
How can it be corrected?

Thanks, Aron

[Newsgroup_User]

Prisec wrote:
> to use in the JDBC URL field of the datasource area of CFAdmin is something
> like this: jdbc:mysql://someipaddresshere/mydbname&characterSetResults=UTF-8

if your host is using the built in JDBC driver, it's probably 3.x & maybe too
old for mysql 5. if you still want to use it, just have them add

useUnicode=true&characterEncoding=utf8

to the connection string in the cfadmin advanced menu for that DSN.

if you want to use another driver, assuming they have correctly setup the DSN as
"other" then the JDBC URL should be something like:

jdbc:mysql://<dbServer>:<dbPort>/<yourDatabaseName>?useUnicode=true&characterEncoding=utf8

i've also read there might be logger clashes w/cf so you might need to add

&logger=com.mysql.jdbc.log.StandardLogger

as well.

[Guest]

Thanks, PaulH that seems fine to me, at least I've created the DSN according your suggestion and CF Admin accepted it on my development machine.
I have just two questions left to clarify before sending the connection string to my hosting provider:

1. In another forum posting I read about a suggestion to append to the connection string the following: &characterSetResults=UTF-8
It is similar but not the same what you've wrote. Should I put this also into the string or it is unnecessary? The complete string would look like this:

jdbc:mysql://<dbServer>:<dbPort>/<yourDatabaseName>?useUnicode=true&characterEncoding=utf8&characterSetResults=UTF-8&logger=com.mysql.jdbc.log.StandardLogger

2. The other question is about syntax. You spelled utf8, the other guy wrote utf-8 (with slash). Which one is correct?

Thanks again, Aron