Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » MobiLink - Client Administration » SQL Anywhere clients for MobiLink » Scripted upload » Tutorial: Using scripted upload

 

Lesson 4: Handling updates

In this lesson, you create a table, a trigger and a stored procedure to handle updates.

Prerequisites

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.

 Task
  1. 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 )
    );
  2. 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.

  3. 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.

Results

A table is created to store pre-images, a trigger is created to store pre-images of each row that is updated, and a stored procedure is created to handle the updates.

Next

Proceed to Lesson 5: Handling deletes.