Sync Framework Provider - Non english characters

Sync Framework Provider - Non english characters

Postby jpapg » Fri 18 Jul 2014 09:59

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 ??
jpapg
 
Posts: 1
Joined: Fri 18 Jul 2014 08:17

Re: Sync Framework Provider - Non english characters

Postby Pinturiccio » Wed 23 Jul 2014 15:40

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44

Re: Sync Framework Provider - Non english characters

Postby Pinturiccio » Thu 04 Sep 2014 15:52

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");
}
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL