I'm getting a MySqlException when trying to provision a table with a text field
the exact error is coming when trying to create the table_insert procedure
am using the latest professional version
steps to reproduce the issue
if I remove the description field the provisioning and the sync will be completed.
the error is coming from this procedure
Code: Select all
CREATE PROCEDURE `Products_insert` (
p_id INT ,
p_name VARCHAR(100) CHARACTER SET LATIN1 ,
p_categoryid INT ,
p_isavailable TINYINT ,
p_description TEXT[CHARSET=LATIN1][COLLATE=LATIN1_SWEDISH_CI] CHARACTER SET LATIN1 ,
OUT sync_row_count INT)
BEGIN
DECLARE rowexist BIGINT;
SET sync_row_count = 0;
SELECT COUNT(*) INTO rowexist FROM `Products_tracking` t WHERE t.`Id` = p_id;
IF (rowexist = 0) THEN
INSERT INTO `Products` (`Id`, `Name`, `CategoryId`, `IsAvailable`, `description`)
VALUES (p_id, p_name, p_categoryid, p_isavailable, p_description);
SET sync_row_count = ROW_COUNT();
END IF;
END
Code: Select all
CREATE TABLE IF NOT EXISTS `Products` (
`Id` int(11) NOT NULL,
`Name` varchar(100) DEFAULT NULL,
`CategoryId` int(11) DEFAULT NULL,
`IsAvailable` tinyint(1) DEFAULT NULL,
`description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `Products`
--
INSERT INTO `Products` (`Id`, `Name`, `CategoryId`, `IsAvailable`, `description`) VALUES
(1, 'HP 2000-2b19WM ', 1, 1, 'Description 1'),
(2, 'Dell Adamo XPS', 1, 0, 'Description 2'),
(3, 'DELL LAPTOP I14Z', 1, 1, 'Description 3'),
(4, 'HP DC5750 Desktop Computer Dual Core/ ', 2, 1, 'Description 4'),
(5, 'Apple iMac All In One', 2, 0, 'Description 5'),
(6, 'Sharp - AQUOS - ', 3, 1, 'Description 6'),
(7, 'Samsung ST76', 4, 1, 'Description 7'),
(8, 'Canon PowerShot', 4, 1, 'Description 8'),
(9, 'Olympus OM-D E-M5', 4, 1, 'Description 9');
Code: Select all
CREATE TABLE [dbo].[Products](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[CategoryId] [nvarchar](max) NOT NULL,
[IsAvailable] [bit] NULL,
[description] [TEXT] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Code used:
Code: Select all
static void Main(string[] args)
{
SqlConnection clientConnection = new SqlConnection("server=thermo-pc\\dev2012;database=syncdb;integrated security=SSPI;");
MySqlConnection serverConnection = new MySqlConnection("host=localhost;protocol=SSH;user=root;password=admin;database=syncdb;");
serverConnection.SshOptions.AuthenticationType = SshAuthenticationType.Password;
serverConnection.SshOptions.User = "vagrant";
serverConnection.SshOptions.Host = "10.37.129.3";
serverConnection.SshOptions.Password = "vagrant";
DbSyncScopeDescription scopeDesc1 = new DbSyncScopeDescription("DevCategoryScope1");
DbSyncScopeDescription scopeDesc2 = new DbSyncScopeDescription("DevCategoryScope2");
DbSyncTableDescription tableDesc = MySqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConnection);
DbSyncTableDescription tableDesc1 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", clientConnection);
scopeDesc1.Tables.Add(tableDesc);
scopeDesc2.Tables.Add(tableDesc1);
MySqlSyncScopeProvisioning devProvision = new MySqlSyncScopeProvisioning(serverConnection, scopeDesc1);
devProvision.Apply();
SqlSyncScopeProvisioning productionProvisioning = new SqlSyncScopeProvisioning(clientConnection, scopeDesc2);
productionProvisioning.Apply();
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.RemoteProvider = new MySqlSyncProvider("DevCategoryScope1", serverConnection, null, null);
syncOrchestrator.LocalProvider = new SqlSyncProvider("DevCategoryScope2", clientConnection, null, null);
SyncOperationStatistics stats = syncOrchestrator.Synchronize();
Console.WriteLine("{0}: {1}", "Total Download changes", stats.DownloadChangesTotal);
Console.WriteLine("{0}: {1}", "Total Download changes Failed", stats.DownloadChangesFailed);
Console.WriteLine("{0}: {1}", "Start time", stats.SyncStartTime.ToString());
Console.WriteLine("{0}: {1}", "End Time", stats.SyncEndTime.ToString());
Console.WriteLine("{0}: {1}", "Download Changes Applied", stats.DownloadChangesApplied);
}
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[CHARSET=LATIN1][COLLATE=LATIN1_SWEDISH_CI] CHARACTER SET LATIN1 ,
OUT sync_r' at line 6
Code: Select all
140907 22:18:20 66 Connect root@localhost on syncdb
66 Query SHOW KEYS FROM Products FROM syncdb WHERE Column_name LIKE '%'
66 Query SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `Null` , COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`, COLLATION_NAME as `Collate`, CHARACTER_SET_NAME AS `CharacterSet`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA= 'syncdb' AND TABLE_NAME = 'Products'
140907 22:18:24 66 Query SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'scopeinfo'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'scopeinfo'
66 Query SET TRANSACTION ISOLATION LEVEL READ COMMITTED
66 Query BEGIN
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'sequences'
66 Query CREATE TABLE `sequences`
(
name VARCHAR(16) NOT NULL,
curr_value BIGINT UNSIGNED NOT NULL,
inc_value BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (name)
) ENGINE = INNODB;
INSERT IGNORE INTO `sequences` VALUES ('new_timestamp', 1,1)
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'get_newtimestampfunc' AND `type` = 'FUNCTION'
66 Query CREATE FUNCTION `get_newtimestampfunc` ()
RETURNS BIGINT UNSIGNED
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE curr_value BIGINT UNSIGNED;
SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE;
UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp';
RETURN curr_value;
END
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'scopeinfo'
66 Query CREATE TABLE `scopeinfo` (
scope_local_id INT AUTO_INCREMENT,
scope_id BINARY(16) NOT NULL,
scope_name VARCHAR(100) NOT NULL,
scope_sync_knowledge BLOB NULL,
scope_tombstone_cleanup_knowledge BLOB NULL,
scope_timestamp BIGINT NULL,
scope_cleanup_timestamp TIMESTAMP NULL,
scope_user_comment VARCHAR(200) NULL,
scope_config_id BINARY(16) NULL,
scope_restore_count INT default 0,
PRIMARY KEY (scope_local_id),
CONSTRAINT scopeinfo_name UNIQUE (scope_name)
)
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
66 Query CREATE TABLE `schemainfo` (
schema_major_version INT NOT NULL,
schema_minor_version INT NOT NULL,
schema_extended_info varchar(100) NOT NULL,
PRIMARY KEY (schema_major_version,schema_minor_version)
)
66 Query INSERT INTO `schemainfo` (schema_major_version, schema_minor_version, schema_extended_info ) VALUES ('2','1','')
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'scopeconfig'
66 Query CREATE TABLE `scopeconfig` (
config_id BINARY(16) NOT NULL,
config_data TEXT,
scope_status char(1) NULL,
PRIMARY KEY (config_id)
)
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'scope_parameters'
66 Query CREATE TABLE `scope_parameters` (
sync_scope_name VARCHAR(100) NOT NULL,
parameter_data TEXT NOT NULL,
CONSTRAINT PK_scope_parameters PRIMARY KEY (sync_scope_name)
)
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'scope_templates'
66 Query CREATE TABLE `scope_templates` (
template_id INT AUTO_INCREMENT,
template_name VARCHAR(100) NOT NULL,
template_config_id BINARY(16) NOT NULL,
template_description VARCHAR(100) NOT NULL,
CONSTRAINT PK_scope_templates PRIMARY KEY (template_id),
CONSTRAINT scopeinfo_name UNIQUE (template_name)
)
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'update_scopeinfo' AND `type` = 'PROCEDURE'
66 Query create procedure `update_scopeinfo`
(
sync_scope_name varchar(100),
sync_scope_knowledge blob,
sync_scope_cleanup_knowledge blob,
sync_check_concurrency INT,
sync_scope_timestamp BIGINT,
out sync_row_count INT
)
begin
update `scopeinfo` set
scope_sync_knowledge = sync_scope_knowledge,
scope_tombstone_cleanup_knowledge = sync_scope_cleanup_knowledge
where scope_name = sync_scope_name
AND (sync_check_concurrency = 0 OR scope_timestamp = sync_scope_timestamp);
set sync_row_count = ROW_COUNT();
end
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'get_newtimestamp' AND `type` = 'PROCEDURE'
66 Query CREATE PROCEDURE `get_newtimestamp` (
out sync_new_timestamp bigint
)
BEGIN
set sync_new_timestamp = `get_newtimestampfunc`();
END
66 Query INSERT INTO `scopeconfig` (config_id, config_data, scope_status) VALUES(0xA0E92253D93F2749AC783F7CE53E9673, '<SyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="false"><Adapter DelTrig="`Products_on_delete`" GlobalName="Products" InsTrig="`Products_on_insert`" UpdTrig="`Products_on_update`" Name="`Products`" TrackingTable="`Products_tracking`" SelChngProc="`Products_selectchanges`" SelRowProc="`Products_selectrow`" InsMetaProc="`Products_insertmetadata`" UpdMetaProc="`Products_updatemetadata`" DelMetaProc="`Products_deletemetadata`" InsProc="`Products_insert`" UpdProc="`Products_update`" DelProc="`Products_delete`"><Col name="Id" type="int" prec="11" scale="0" size="0" pk="true" /><Col name="Name" type="varchar[charset=latin1][collate=latin1_swedish_ci]" prec="0" scale="0" size="100" null="true" /><Col name="CategoryId" type="int" prec="11" scale="0" size="0" null="true" /><Col name="IsAvailable" type="tinyint" prec="1" scale="0" size="0" null="true" /><Col name="description" type="text[charset=latin1][collate=latin1_swedish_ci]" prec="0" scale="0" size="0" null="true" /></Adapter></SyncProviderScopeConfiguration>', 'P')
66 Query INSERT INTO `scopeinfo` (scope_id, scope_name, scope_config_id, scope_user_comment, scope_timestamp) VALUES(0x4A834F27E4EB2F40B39A77256EC0C6E6, 'DevCategoryScope1', 0xA0E92253D93F2749AC783F7CE53E9673, '', `get_newtimestampfunc`())
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
66 Query SELECT schema_major_version, schema_minor_version, schema_extended_info FROM `schemainfo`
66 Query SHOW KEYS FROM `Products_tracking` FROM syncdb WHERE Column_name LIKE '%'
66 Query SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `Null` , COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`, COLLATION_NAME as `Collate`, CHARACTER_SET_NAME AS `CharacterSet`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA= 'syncdb' AND TABLE_NAME = '`Products_tracking`'
66 Query SHOW KEYS FROM `Products_tracking` FROM syncdb WHERE Column_name LIKE '%'
66 Query SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `Null` , COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`, COLLATION_NAME as `Collate`, CHARACTER_SET_NAME AS `CharacterSet`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA= 'syncdb' AND TABLE_NAME = '`Products_tracking`'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'Products'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'Products_tracking'
66 Query SHOW TABLE STATUS FROM syncdb LIKE 'Products_tracking'
66 Query CREATE TABLE `Products_tracking` (
`Id` int(0) NOT NULL,
update_scope_local_id INT NULL,
scope_update_peer_key INT,
scope_update_peer_timestamp BIGINT NULL,
local_update_peer_key INT,
local_update_peer_timestamp BIGINT,
create_scope_local_id INT NULL,
scope_create_peer_key INT,
scope_create_peer_timestamp BIGINT,
local_create_peer_key INT,
local_create_peer_timestamp BIGINT,
sync_row_is_tombstone INT,
restore_timestamp BIGINT,
last_change_datetime TIMESTAMP NULL,
PRIMARY KEY (`Id`)
)
66 Query INSERT INTO `Products_tracking` (`Id`, create_scope_local_id, local_create_peer_key, local_create_peer_timestamp, update_scope_local_id, local_update_peer_key, restore_timestamp, sync_row_is_tombstone, local_update_peer_timestamp)
SELECT m.`Id`, null, 0, `get_newtimestampfunc`(), NULL, 0, NULL, 0, `get_newtimestampfunc`()
FROM `Products` m
LEFT OUTER JOIN `Products_tracking` t ON m.`Id` = t.`Id`
WHERE t.`Id` IS NULL
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query SELECT s.curr_value
INTO curr_value
FROM `sequences` s
WHERE s.name = 'new_timestamp'
FOR UPDATE
66 Query UPDATE `sequences` s
SET s.curr_value = s.curr_value + s.inc_value
WHERE s.name = 'new_timestamp'
66 Query select
`TRIGGER_SCHEMA` as `Database`,
`TRIGGER_NAME` as `Name`,
`EVENT_OBJECT_SCHEMA` as `TableDatabase`,
`EVENT_OBJECT_TABLE` as `Table`,
`EVENT_MANIPULATION` as `Event`,
`ACTION_TIMING` as `Timing`,
`ACTION_STATEMENT` as `Body`,
`SQL_MODE` as `SqlMode`
from information_schema.`TRIGGERS`
where `TRIGGER_SCHEMA` like 'syncdb' and `TRIGGER_NAME` like 'Products_on_insert'
66 Query select
`TRIGGER_SCHEMA` as `Database`,
`TRIGGER_NAME` as `Name`,
`EVENT_OBJECT_SCHEMA` as `TableDatabase`,
`EVENT_OBJECT_TABLE` as `Table`,
`EVENT_MANIPULATION` as `Event`,
`ACTION_TIMING` as `Timing`,
`ACTION_STATEMENT` as `Body`,
`SQL_MODE` as `SqlMode`
from information_schema.`TRIGGERS`
where `TRIGGER_SCHEMA` like 'syncdb' and `TRIGGER_NAME` like 'Products_on_update'
66 Query select
`TRIGGER_SCHEMA` as `Database`,
`TRIGGER_NAME` as `Name`,
`EVENT_OBJECT_SCHEMA` as `TableDatabase`,
`EVENT_OBJECT_TABLE` as `Table`,
`EVENT_MANIPULATION` as `Event`,
`ACTION_TIMING` as `Timing`,
`ACTION_STATEMENT` as `Body`,
`SQL_MODE` as `SqlMode`
from information_schema.`TRIGGERS`
where `TRIGGER_SCHEMA` like 'syncdb' and `TRIGGER_NAME` like 'Products_on_delete'
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectchanges' AND `type` = 'PROCEDURE'
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectchanges' AND `type` = 'PROCEDURE'
66 Query CREATE PROCEDURE `Products_selectchanges` (
sync_min_timestamp INT,
sync_metadata_only INT,
sync_scope_local_id INT,
sync_initialize INT)
BEGIN
SELECT
t.`Id`,
m.`Name`,
m.`CategoryId`,
m.`IsAvailable`,
m.`description`,
t.sync_row_is_tombstone,
t.local_update_peer_timestamp as sync_row_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id)
THEN
CASE
WHEN (t.restore_timestamp is null) THEN t.local_update_peer_timestamp
ELSE t.restore_timestamp
END
ELSE t.scope_update_peer_timestamp
END AS sync_update_peer_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id) THEN t.local_update_peer_key
ELSE t.scope_update_peer_key
END AS sync_update_peer_key,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_timestamp
ELSE t.scope_create_peer_timestamp
END AS sync_create_peer_timestamp,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_key
ELSE t.scope_create_peer_key
END AS sync_create_peer_key
FROM `Products` m
RIGHT JOIN `Products_tracking` t
ON m.`Id` = t.`Id`
WHERE (t.update_scope_local_id is NULL OR t.update_scope_local_id <> sync_scope_local_id OR
(t.update_scope_local_id = sync_scope_local_id AND t.scope_update_peer_key <> CASE WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id)
THEN t.local_update_peer_key ELSE t.scope_update_peer_key END))
AND t.local_update_peer_timestamp > sync_min_timestamp;
END
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectrow' AND `type` = 'PROCEDURE'
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectrow' AND `type` = 'PROCEDURE'
66 Query CREATE PROCEDURE `Products_selectrow` (
p_id INT ,
sync_scope_local_id INT
)
BEGIN
SELECT
t.`Id`,
m.`Name`,
m.`CategoryId`,
m.`IsAvailable`,
m.`description`,
t.sync_row_is_tombstone,
t.local_update_peer_timestamp as sync_row_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id)
THEN
CASE
WHEN (t.restore_timestamp is null) THEN t.local_update_peer_timestamp
ELSE t.restore_timestamp
END
ELSE t.scope_update_peer_timestamp
END AS sync_update_peer_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id) THEN t.local_update_peer_key
ELSE t.scope_update_peer_key
END AS sync_update_peer_key,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_timestamp
ELSE t.scope_create_peer_timestamp
END AS sync_create_peer_timestamp,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_key
ELSE t.scope_create_peer_key
END AS sync_create_peer_key
FROM `Products` m
RIGHT JOIN `Products_tracking` t
ON m.`Id` = t.`Id`
WHERE t.`Id` = p_id;
END
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_insert' AND `type` = 'PROCEDURE'
66 Query SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_insert' AND `type` = 'PROCEDURE'
66 Query CREATE PROCEDURE `Products_insert` (
p_id INT ,
p_name VARCHAR(100) CHARACTER SET LATIN1 ,
p_categoryid INT ,
p_isavailable TINYINT ,
p_description TEXT[CHARSET=LATIN1][COLLATE=LATIN1_SWEDISH_CI] CHARACTER SET LATIN1 ,
OUT sync_row_count INT)
BEGIN
DECLARE rowexist BIGINT;
SET sync_row_count = 0;
SELECT COUNT(*) INTO rowexist FROM `Products_tracking` t WHERE t.`Id` = p_id;
IF (rowexist = 0) THEN
INSERT INTO `Products` (`Id`, `Name`, `CategoryId`, `IsAvailable`, `description`)
VALUES (p_id, p_name, p_categoryid, p_isavailable, p_description);
SET sync_row_count = ROW_COUNT();
END IF;
END
Devart.Data.Synchronization