Reputation: 2480
I'm using the C# .NET Mysql Connector, and when running this query:
INSERT INTO convos (`userid`,`time`,`from`,`content`,`read`,`deleted`, `ip`, `source`, `charname`, `to`) VALUES ('3', '1347396787', 'Chára', '........', '0', '0', '0.0.0.0:0000', 'C', 'óóóíííí', 'óóóíííí');
I get the following error:
Incorrect string value: '\xE1ra' for column 'from' at row 1
I understand my encoding, everything was configured for utf8, utf8_general_ci. Database, table and columns are configured for utf8. The data is sent from the client in utf8.
If i use a 3rd party tool like, Workbench to insert the query or use the mysql command line it works fine. I don't know if there is a bug with the connector or i need to be doing something else with the values before insert?
Any idea?
Thanks
Is there any in mysql to covert to the correct type?
Upvotes: 0
Views: 2805
Reputation: 1
config mysql like below. it will solve the unicode error when insert into mysql from java or C#
Upvotes: 0
Reputation: 81
For someone who has tried all the suggestions, and nothing has worked (like myself), it is worth checking what MySQL types are your fields mapped to in C#. My text fields were automatically mapped as MySqlDbType.Blob and that was causing the error. I changed the type to MySqlDbType.Text, and I don't see the error any more.
Here is my original response to a similar thread: https://stackoverflow.com/a/16989466/2199026
Upvotes: 0
Reputation: 2480
I figured this out, its taken a while but it seems i was setting the charset too often. The database, tables, columns are all in UTF8. When i made a connection i had "CHARSET=UTF8" in the connection string. I was also running "SET NAMES 'utf8' COLLATE 'utf8_general_ci'" everytime i made a connection. I dropped the CHARSET=UTF8 and "SET NAMES 'utf8' COLLATE 'utf8_general_ci'" and its all working now.
Upvotes: 1
Reputation: 7193
First see your table convos and make sure columns data types is either one of nchar, nvarchar and You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
Tyr:
insertQuery = "INSERT INTO convos (`userid`,`time`,`from`,`content`,`read`,`deleted`, `ip`, `source`, `charname`, `to`) VALUES
(N'3', N'1347396787', N'Chára', N'........', N'0', N'0', N'0.0.0.0:0000', N'C', N'óóóíííí', N'óóóíííí')";
Upvotes: 0
Reputation: 28763
Update it
INSERT INTO convos (`userid`,`time`,`from`,`content`,`read`,`deleted`, `ip`, `source`, `charname`, `to`) VALUES ('3', '1347396787', 'Chara', '........', '0', '0', '0.0.0.0:0000', 'C', 'óóóíííí', 'óóóíííí');
I think for "chara"in your 3rd value gives it
Upvotes: 0
Reputation: 1504
I believe you need to alter the column's char set:
use below code for those columns which is using UTF-8.
ALTER TABLE database.table MODIFY COLUMN col VARCHAR(255)
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
Upvotes: 1