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 3: Handling inserts

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.

  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.

        UPDATE employee SET insert_time = CURRENT TIMESTAMP
        WHERE id =

    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 )
        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 = );


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.


Proceed to Lesson 4: Handling updates.