In this lesson, you create a table, a trigger and a stored procedure to handle 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.
Context and remarks
To handle uploads, you need to ensure that the correct pre-image is used based on the start progress when the upload was built.
Using the instance of Interactive SQL connected to the remote database, create a table that maintains pre-images of updated rows. The pre-images are used when generating the scripted upload.
CREATE TABLE employee_preimages ( id unsigned integer NOT NULL, name varchar( 256), salary numeric( 9, 2 ), img_time timestamp default CURRENT TIMESTAMP, primary key( id, img_time ) );
Next, create a trigger to store a pre-image for each row when it is updated. As with the insert trigger, this trigger is not fired on download.
CREATE TRIGGER emp_upd AFTER UPDATE OF name,salary ON employee REFERENCING OLD AS oldrow FOR EACH ROW BEGIN INSERT INTO employee_preimages ON EXISTING SKIP VALUES( oldrow.id, oldrow.name, oldrow.salary, CURRENT TIMESTAMP ); END;
This trigger stores a pre-image row each time a row is updated (unless two updates come so close together that they get the same timestamp). At first glance this looks wasteful. It would be tempting to only store a pre-image for the row if there is not already one in the table, and then count on the sp_hook_dbmlsync_upload_end hook to delete pre-images once they have been uploaded.
However, the sp_hook_dbmlsync_upload_end hook is not reliable for this purpose. The hook may not be called if a hardware or software failure stops dbmlsync after the upload is sent but before it is acknowledged, resulting in rows not being deleted from the pre-images table even though they have been successfully uploaded. Also, when a communication failure occurs dbmlsync may not receive an acknowledgement from the server for an upload. In this case, the upload status passed to the hook is 'unknown'. When this happens there is no way for the hook to tell if the pre-images table should be cleaned or left intact. By storing multiple pre-images, the correct one can always be selected based on the start progress when the upload is built.
Next, create an upload procedure to handle updates.
CREATE PROCEDURE employee_update() RESULT( preimage_id unsigned integer, preimage_name varchar( 256), preimage_salary numeric( 9,2 ), postimage_id unsigned integer, postimage_name varchar( 256), postimage_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 an update all rows that have been updated since // start_time that were not newly inserted or deleted. SELECT ep.id, ep.name, ep.salary, e.id, e.name, e.salary FROM employee e JOIN employee_preimages ep ON ( e.id = ep.id ) // Do not select rows inserted since the start time. These should be // uploaded as inserts. WHERE insert_time <= start_time // Do not upload deleted rows. AND NOT EXISTS( SELECT id FROM employee_delete ed WHERE ed.id = e.id ) // Select the earliest pre-image after the start time. AND ep.img_time = ( SELECT MIN( img_time ) FROM employee_preimages WHERE id = ep.id AND img_time > start_time ); END;
This stored procedure returns one result set that has twice as many columns as the other scripts: it contains the pre-image (the values in the row the last time it was received from, or successfully uploaded to, the MobiLink server), and the post-image (the values to be entered into the consolidated database).
The pre-image is the earliest set of values in employee_preimages that was recorded after the start_progress. This example does not correctly handle existing rows that are deleted and then reinserted. In a more complete solution, these would be uploaded as an update.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|