Restoring data in MySql for a .Net application

[Hubert CANEVET]

Hello everybody,

In January, I saved my MySql base with the command "backup the database as SQL Dump" from SqlYog.

One month later, I restored it after reinstalling my machine.

MySql is version 5.6, I guess it was the same one month ago ?
The system is Windows XP Home SP3.

To restore the database, I opened the dump in a query tab in SqlYog to execute it. I also deleted the database and tried the same from MySql Workbench, as it worked a few days ago without scratching.

For english text, the restoration presents no problem. But concerning accentuated characters, we are not at it.

For example, for bikes in Adventure Works from Microsoft, the category name in French is "Vélo", that is transmitted in UTF8 as "Vélo". Initially, I seized "Vélo" in the field, but after restoring the database, it appears as "Vélo" in it, and of course the same on the screen of the application.

I tried changing the character set after restoring, but with no success about the appearance of the data.

I presume the next time I save the database I should choose another way of doing it, or at least with different parameters, but for this time am I bound to correct the accentuated characters one by one ?

Or at least it would be by programming something. I wrote a 2 lines csharp procedure that translates iso to utf (strangely, it works better in that direction than reverse), but if I try to call a csharp procedure, I shall have to do it record by record, with the key and the translated text as arguments. Correcting a whole table from a SQL query supposes to write a UDF, I presume. If no more simple answer is proposed here, maybe this will be the direction, but for me this supposes to start from scratch about user defined functions. Did anybody do that just with the documentation, or is there somewhere some step by step instructions about beginning to develop UDFs and calling them ?

CREATE TABLE `dbo_dimproductcategory` (
`ProductCategoryKey` int(11) NOT NULL AUTO_INCREMENT,
`ProductCategoryAlternateKey` int(11) DEFAULT NULL,
`EnglishProductCategoryName` varchar(100) NOT NULL,
`SpanishProductCategoryName` varchar(100) NOT NULL,
`FrenchProductCategoryName` varchar(100) NOT NULL,
PRIMARY KEY (`ProductCategoryKey`),
UNIQUE KEY `AK_DimProductCategory_ProductCategoryAlternateKey` (`ProductCategoryAlternateKey`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `dbo_dimproductcategory` */

insert into `dbo_dimproductcategory`(`ProductCategoryKey`,`ProductCategoryAlternateKey`,`EnglishProductCategoryName`,`SpanishProductCategoryName`,`FrenchProductCategoryName`) values (1,1,'Bikes','Bicicleta','Vélo');
insert into `dbo_dimproductcategory`(`ProductCategoryKey`,`ProductCategoryAlternateKey`,`EnglishProductCategoryName`,`SpanishProductCategoryName`,`FrenchProductCategoryName`) values (2,2,'Components','Componente','Composant');
insert into `dbo_dimproductcategory`(`ProductCategoryKey`,`ProductCategoryAlternateKey`,`EnglishProductCategoryName`,`SpanishProductCategoryName`,`FrenchProductCategoryName`) values (3,3,'Clothing','Prenda','Vêtements');
insert into `dbo_dimproductcategory`(`ProductCategoryKey`,`ProductCategoryAlternateKey`,`EnglishProductCategoryName`,`SpanishProductCategoryName`,`FrenchProductCategoryName`) values (4,4,'Accessories','Accesorio','Accessoire');



Edited 1 time(s). Last edit at 02/25/2014 06:42AM by Hubert CANEVET.