You need to define stored procedures and other things to handle the upload. You do this separately for inserts, deletes, and updates.
This lesson assumes you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Using scripted upload.
This lesson assumes you have completed all preceding lessons. See Lesson 1: Creating the consolidated database.
Using the instance of Interactive SQL connected to the remote database, create a trigger to set the insert_time on each row when it is inserted using the following SQL.
CREATE TRIGGER emp_ins AFTER INSERT ON employee REFERENCING NEW AS newrow FOR EACH ROW BEGIN UPDATE employee SET insert_time = CURRENT TIMESTAMP WHERE id = newrow.id END;
This timestamp is used to determine if a row has been inserted since the last synchronization. This trigger is not fired when dbmlsync is applying downloaded inserts from the consolidated database because later in this example you set the FireTriggers extended option to off. Rows inserted by the download get an insert_time of 1900-01-01, the default value defined when the employee table was created. This value should always be before the start progress so those rows are not treated as new inserts and are not uploaded during the next synchronization.
Still in the remote database, create a procedure to return as a result set all the inserted rows to be uploaded.
CREATE PROCEDURE employee_insert() RESULT( id unsigned integer, name varchar( 256 ), salary numeric( 9,2 ) ) BEGIN DECLARE start_time timestamp; SELECT value INTO start_time FROM #hook_dict WHERE name = 'start progress as timestamp'; // Upload as inserts all rows inserted after the start_time // that were not subsequently deleted SELECT id, name, salary FROM employee e WHERE insert_time > start_time AND NOT EXISTS( SELECT id FROM employee_delete ed WHERE ed.id = e.id ); END;
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|