Dear Rick,
Thank you for your informative reply. I have considered your suggestions and I will be glad to provide you with more information and answer your questions (because the problem is still present).
I know that the SHOW COLLATION will list the possible values. I think I do not have any problem with the collation, where I have the problem is the character set, and I can prove it. On my computer used as localhost for designing my website, I ran the MySql Instance Config Wizard and set the character set to utf8 (with the default collation of utf8_general_ci). Actually, it took about 1 minute and that was all I had to do for my website to save and display e.g. the latin capital letter O with double acute (U+0150) correctly! There was no need for additional programming on my website or use the phpMyAdmin website. I assume that MySql is a sophisticated program and it allows the users to apply the accented utf8 characters (e.g. ”Ő” or “Ű”). The user-friendly way to program a remote MySql would be by giving one or couple of SQL commands at one time, instead of the need of performing extensive reprogramming of each web page of the application. My question: is it possible to send SQL command(s) to MySql located on the hosting server from the phpMyAdmin interface in order to program it to support utf8 characters? I would like to send some commands at one time and achieve permanent change in a way that the utf8 characters are saved and displayed correctly. What command(s) should I send? (the SET command or SET name command was executed successfully, but the affected variables like character_set_server remained latin1, there was no change when I ran the query show variables.)
I think that the problem is that the character_set_server is latin1 and character_set_database is latin1 as well on the hosting server. I DO know that when these variables are set to utf8 (on my computer), the utf8 characters are displayed correctly. How can I change the variables character_set_server and character_set_database to utf8 on the hosting server? (The SET command brought about no change.)
The latin capital letter O with double acute (U+0150) is a regular Hungarian character. On my computer, the column’s character set is set to utf8, the table’s character set is set to utf8 (with default collation of utf8_general_ci), my database’s character set is set to utf8, and the character set of the MySql on my computer is set to utf8 (with default collation of utf8_general_ci), and the my website DOES save and display the utf8 characters correctly, including the “Ő”. Since I have the correct and working settings on my computer, the simplest and most straightforward way would be to set the variables of the hosting server too in the same way. I can do it by using phpMyAdmin, e.g. by sending SQL commands to MySql. What commands should I send?
Your question was: Where do your latin2 or utf8 characters come from?
The characters will simply be entered by the users into a textbox. They use Hungarian keyboard layout and hence they will from time to time enter the letter o or u with double accent. In order to provide you with a better overview, I have set up a test website on my not-yet-correctly-running homepage. If you go to
http://79.170.40.235/maximkft.hu/CharTest1.aspx you will see a textbox where you can enter that special character and see with your own eyes that the double accent disappears when it is saved into the database. (On the test webpage you can also see the hosting server’s current variables that do not work correctly.)
The strange phenomenon is that if I enter the characters with double acute in phpMyAdmin into the table's cell, they are saved and displayed correctly! It is only on my website where the characters are not stored correctly when saved and displayed (as you can test it on the above-mentioned test website).
I assume the problem is with the wrong setting of MySql server variables, and not with .net or ado.net or something else. It is proven by the fact that my website saves all the utf8 characters correctly on my own computer as localhost (with utf8 server and database settings). Previously I was advised for instance to use the requestEncoding="UTF-8" responseEncoding="UTF-8" settings in the globalization element in the system.config file, but it did not work. It is very likely that it would be the wrong way to look for the error somewhere else instead of programming the MySql variables correctly.
You mentioned that 0150 is "unicode" and distinct from latin2 and utf8. I do not doubt it, but no matter what character it is, it is saved correctly when the character_set_server and character_set_database is set to utf8 (or latin2).
You suggest I should use the SET command right after opening the connection. Since I do not know how to do it within my web site, I would prefer to use the SET command before opening any connection, in a permanent way and on one occasion on the phpMyAdmin interface. I know how to use it there, so I would prefer using the SET command there. Is it possible? (on phpMyAdmin I can send SQL commands on localhost level, database level and table level, but on localhost level the SET command seems to have no effect, seemingly I am not authorized to change some “global” server parameters.)
Your question was: Do you have any characters that _might_ be stored in a table? And you had the request: Show me the SELECT output…
I am not sure what you mean with the characters that _might_ be stored in a table. All of the information entered is stored in my MySql tables.
As for the SELECT output, I went into the phpMyAdmin, I selected the database (web235-maxim), then I selected the table (models), and assuming that x refers to the column Language3 (where I have the only one letter o with double acute), I entered the SQL command SELECT Language3, HEX(Language3), LENGTH(Language3), CHAR_LENGTH(Language3) FROM models. The result for the corresponding row is the following:
Language3 HEX(Language3) LENGTH(Language3) CHAR_LENGTH(Language3)
ő C591 2 1
Does it make any sense for you? You are right, the LENGTH and CHAR_LENGTH are not equal. But please note that this special character with double acute is in the given cell only because I entered and saved it into the database on the phpMyAdmin for testing purpose! If I enter it on my website and save it, it will become and “o” without double acute. If I re-run the SQL query for “o” (which is the incorrect character for me), I get following output:
Language3 HEX(Language3) LENGTH(Language3) CHAR_LENGTH(Language3)
o 6F 1 1
In this case the LENGTH and CHAR_LENGTH are equal.
Some more information as to how the characters are saved on my website. The MySql database is configured in my web.config system as ConnectionString as follows:
<connectionStrings>
<remove name="LocalMySqlServer" />
<add name="LocalMySqlServer" connectionString="password=MyPassword;User Id=web235-maxim;server=localhost;database=web235-maxim" providerName="MySql.Data.MySqlClient" />
It does not specify anything about character set.
Then, the specific table is also defined here as a ConnectionString:
<add name="maximModels" connectionString="metadata=res://*;provider=MySql.Data.MySqlClient;provider connection string="server=localhost;User Id=web235-maxim;password= MyPassword;Persist Security Info=True;database=web235-maxim"" providerName="System.Data.EntityClient" />
First, the user enters the text (mostly with Hungarian keyboard layout) into a textbox. The websites establishes a connection to the MySql database by the statements: Model.maximModels me; me = new Model.maximModels();
The class maximModels is auto-generated, it says “Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.” So I assume I should not use here any SQL command. It contains such code:
/// <summary>
/// There are no comments for Property Language3 in the schema.
/// </summary>
[global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute()]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public string Language3
{
get
{
return this._Language3;
}
set
{
this.OnLanguage3Changing(value);
this.ReportPropertyChanging("Language3");
this._Language3 = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value, true);
this.ReportPropertyChanged("Language3");
this.OnLanguage3Changed();
}
}
private string _Language3;
partial void OnLanguage3Changing(string value);
partial void OnLanguage3Changed();
Saving of the changes occurs by the following command: me.SaveChanges();
It contains following code (an excerpt only):
// Summary:
// Persists all updates to the store and resets change tracking in the object
// context.
//
// Returns:
// The number of objects in an System.Data.Objects.EntityState.Added, System.Data.Objects.EntityState.Modified,
// or System.Data.Objects.EntityState.Deleted state when System.Data.Objects.ObjectContext.SaveChanges()
// was called.
public int SaveChanges();
//
// Summary:
// Persists all updates to the store and optionally resets change tracking in
// the object context.
//
// Parameters:
// acceptChangesDuringSave:
// This parameter is needed for client-side transaction support. If true, the
// change tracking on all objects is reset after System.Data.Objects.ObjectContext.SaveChanges(System.Boolean)
// finishes. If false, you must call the System.Data.Objects.ObjectContext.AcceptAllChanges()
// method after System.Data.Objects.ObjectContext.SaveChanges(System.Boolean).
//
// Returns:
// The number of objects in an System.Data.Objects.EntityState.Added, System.Data.Objects.EntityState.Modified,
// or System.Data.Objects.EntityState.Deleted state when System.Data.Objects.ObjectContext.SaveChanges()
// was called.
public int SaveChanges(bool acceptChangesDuringSave);
//
// Summary:
// Returns an object that has the specified entity key.
I performed a keyword search but the INSERT Sql command is nowhere featured in the code.
To make a long story short: the code of my website works and the utf8 characters are saved correctly on my own computer where MySql’s character_set_server and character_set_database are set to utf8. On the hosting server, however, these variables are set to latin1 and I think the simple solution is to give the appropriate SQL commands to reprogram the character variables of the hosting server in such a way that it allows to save utf8 values correctly, while character_set_server and character_set_database remain set to latin1, or by changing these values to utf8 somehow.
You wrote: I might be able to say "you got the characters stored correctly; now the problem is reading them out". (Or they might have been messed up during the INSERT.)…
As I mentioned, my MySql table is able to store the double acute characters IF entered on the phpMyAdmin surface. For obvious reasons, I want the users to enter the data on my website, and not on the phpMyAdmin site. The issue is that whenever I save the text on my website, the accents are removed. I still do not know whether the storing or the reading out is wrong, but I am confident you could find it out based on the information I provided.
Your feedback is highly appreciated.
Kind regards,
Zoltan
Edited 3 time(s). Last edit at 06/25/2010 12:10PM by Zoltan Sz.