4

My problem is when using C# and MySQL database to save some records by sending parameters.

Although i have already set the charset as Utf-8 and i can see the unicode characters correctly, the problem i get when trying to insert unicode characters is that it only saves half of the string.

The really weird this is that this happens only with unicode strings such as Greek words and only when i send the query with parameters.

Ie. if my query as seen in C# is:

string query = "INSERT INTO tablename VALUES (NULL, @somestring)";

and i set the @somestring parameters value as "TESTING". This would work just fine.

If i try to set the value as unicode string "ΤΕΣΤΙΝΓ", the query executes fine with no errors but only saves half the characters in the database, ie. it only saves "ΤΕΣΤ".

On the other hand if i remove the parameters and adjust the query to be as:

string somestring = "ΤΕΣΤΙΝΓ";
string query = "INSERT INTO tablename VALUES (NULL,'" + somestring + "')";

the query again works just fine AND saves the whole word/sentence in the database.

Hope i explained it correctly and you can understand my situation.

Thanks

Kypros
  • 2,997
  • 5
  • 21
  • 27
  • Welcome to StackOverflow. Please check out the FAQs: http://meta.stackexchange.com/q/7237/27535 – gbn Jan 21 '12 at 15:30

1 Answers1

5

The length of how you declare the parameter @somestring is too short in c#.

UTF-8 takes upto 3 bytes per character so you'd need length to be 21 not 7 for example to fit testing and varieties thereof

Saying that, I've not used c# to call MySQL (only SQL Server) but I'm sure this is the problem

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks for the quick response. I guess you are right since i tried inserting a larger string and it works. So if the field is a name or something shorter i should not be using parameters? Since it 'works' without parameters – Kypros Jan 21 '12 at 15:25
  • @Kypros:you should always use parameters because it help prevent SQL injection attacks. The general rule is "never concatenate string to make SQL calls". – gbn Jan 21 '12 at 15:30
  • Exactly that why im asking the question since i'd like a solution using parameters. So if i get it right i should rename my parameters to something that has a length of 21 or greater right? What i dont like about this is how big my queries would get this way. – Kypros Jan 21 '12 at 15:34
  • @Kypros: make the length of parameter the same length as the database field (or x3). So if it is varchar(100) makes it 100 or 300. This way, it allows all values. It doesn't make your queries "bigger" because only *used* characters will be sent but you'll avoid truncations – gbn Jan 21 '12 at 15:37
  • Now i get it. I think i have been kind of using them wrong. How would you alter the next parameter for a database column of varchar(200)? MySqlParameter someString= new MySqlParameter("@somestring", typeof(string)); – Kypros Jan 21 '12 at 15:47
  • 1
    `MySqlParameter someString= new MySqlParameter("@somestring", MySqlDbType.VarChar, 100);` See http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlparameter.html and http://stackoverflow.com/questions/5777450/mysql-c-insert-issue – gbn Jan 21 '12 at 15:50
  • Thats what i thought but why 100? Shouldnt that be 200 (or 600) as per your posts above? – Kypros Jan 21 '12 at 15:53
  • @Kypros: not now I see an actual example. But you can verify with say a 99 character Greek string and see what happens – gbn Jan 21 '12 at 15:57
  • 1
    Yes. Thanks a lot my friend. You have been very helpful in finding what the problem was. – Kypros Jan 21 '12 at 16:04