Sync Framework Provider - Non english characters

[jpapg]

Im struggling to synchronize tables between SQL Server and MySQL databases with nvarchar fields and non-english characters in data.
The problem is that english characters sync OK, but non english do not, no matter the direction of sync. Tried to set different collation to databases, but the problem insists.
Any ideas ??

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

When a synchronization is performed with two different providers (in our case - dotConnect for MySQL and System.Data.SqlClient), both databases must have base tables created. When autogenerating SQL Server data based on metadata from a MySQL database, problems may occur if some specific data types are used. Autogeneration can be performed successfully only when general data types are used.

Create the SQL Server table for synchronization manually in order for the application to work correctly. All the auxiliary tables will be created automatically. Additionally add 'Unicode=true' to the connection string of MySqlConnection. Here is an example of synchronizing SQL Server table with MySQL.

1. Suppose you have the following MySQL table:

Code: Select all

CREATE TABLE products (
  Id int(11) NOT NULL,
  Name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  CategoryId int(11) DEFAULT NULL,
  IsAvailable tinyint(1) DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = MYISAM
AVG_ROW_LENGTH = 36
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

INSERT INTO products(Id, Name, CategoryId, IsAvailable)
VALUES(1,'??', 0, 0)

2. Create the following table in the SQL Server database:

Code: Select all

CREATE TABLE dbo.Products (
  Id INT NOT NULL,
  Name NVARCHAR(100) COLLATE Cyrillic_General_CI_AS NULL,
  CategoryId INT NULL,
  IsAvailable TINYINT NULL,
  CONSTRAINT PK_Products PRIMARY KEY (Id)
) ON [PRIMARY]
GO

3. The following code synchronizes the tables correctly and displays Unicode characters correctly:

Code: Select all

static void Main(string[] args)
{
    SqlConnection productionConnection = new SqlConnection(@"Data Source=<DATA SOURCE>;Integrated Security=False;User ID=sa;");
    MySqlConnection devConnection = new MySqlConnection("Database=<DATABASE>;host=<HOST>;user id=<USER ID>;password=<PASSWORD>;port=3306;unicode=true;");

    DbSyncScopeDescription scopeDesc1 = new DbSyncScopeDescription("DevCategoryScope1");
    DbSyncScopeDescription scopeDesc2 = new DbSyncScopeDescription("DevCategoryScope2");
    DbSyncTableDescription tableDesc = MySqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);
    DbSyncTableDescription tableDesc1 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", productionConnection);
    scopeDesc1.Tables.Add(tableDesc);
    scopeDesc2.Tables.Add(tableDesc1);

    MySqlSyncScopeProvisioning devProvision = new MySqlSyncScopeProvisioning(devConnection, scopeDesc1);
    devProvision.Apply();

    SqlSyncScopeProvisioning productionProvisioning = new SqlSyncScopeProvisioning(productionConnection, scopeDesc2);
    productionProvisioning.Apply();

    SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
    syncOrchestrator.RemoteProvider = new MySqlSyncProvider("DevCategoryScope1", devConnection, null, null);
    syncOrchestrator.LocalProvider = new SqlSyncProvider("DevCategoryScope2", productionConnection, null, null);

    syncOrchestrator.Synchronize();

    SqlSyncScopeDeprovisioning templateDeprovision = new SqlSyncScopeDeprovisioning(productionConnection);
    templateDeprovision.DeprovisionScope("DevCategoryScope2");

    MySqlSyncScopeDeprovisioning templateDeprovision2 = new MySqlSyncScopeDeprovisioning(devConnection);
    templateDeprovision2.DeprovisionScope("DevCategoryScope1");
}