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

SQL Anywhere 12.0.1 » MobiLink - Client Administration » SQL Anywhere clients for MobiLink » Scripted upload » Tutorial: Using scripted upload

 

Lesson 3: Handling inserts

This lesson assumes you have completed all preceding lessons. See Lesson 1: Creating the consolidated database.

 Handle inserts
  1. 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.

  2. 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;

    This procedure returns all rows that (based on the insert_time) have been inserted since the last successful upload but were not subsequently deleted. The time of the last successful upload is determined from the start progress value in the #hook_dict table. This example uses the default setting for the dbmlsync extended option LockTables, which causes dbmlsync to lock the tables being synchronized. As a result, you do not need to exclude rows inserted after the end progress: the table locks prevent any operations from occurring after the end progress, while the upload is built.

  3. Proceed to Lesson 4: Handling updates.