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

SQL Anywhere 11.0.1 » MobiLink - Getting Started » MobiLink Tutorials » Tutorial: Writing Scripts and Monitoring Synchronizations


Lesson 6: Creating scripts for conflict detection and resolution

Conflicts arise during the upload of rows to the consolidated database. If two users modify the same row on different remote databases, a conflict is detected when the second row arrives at the MobiLink server. Using synchronization scripts you can detect and resolve conflicts.

For more information about MobiLink Conflict resolution, see Handling conflicts.

Inventory example

Consider the scenario of two salesmen in the field. Salesman1 starts with an inventory of ten items, and then sells three. He updates the inventory on his remote database, remote1, to seven items. Salesman2 sells four items and updates her inventory (on remote2) to six.

When remote1 synchronizes using the MobiLink synchronization client utility the consolidated database is updated to seven. When remote2 synchronizes, a conflict is detected because the inventory value in the consolidated database has changed.

To resolve this conflict programmatically, you need three row values:

  1. The current value in the consolidated database.

    After remote1 synchronizes, the value in the consolidated database is 7.

  2. The new row value that Remote2 uploaded.

  3. The old row value that Remote2 obtained during the previous synchronization.

In this case, you can use the following business logic to calculate the new inventory value and resolve the conflict:

current consolidated - (old remote - new remote)
that is, 7 - (10-6) = 3

The expression (old remote - new remote) provides the number of items sold by Salesman2 rather than the absolute inventory value.

Synchronization scripts for conflict detection and resolution

For conflict detection and resolution, you add the following scripts:

  • upload_update   The upload_update event determines how data inserted into the remote database should be applied to the consolidated database. You can also use an extended prototype of upload_update to detect update conflicts.

    For more information about using upload_update to detect conflicts, see Detecting conflicts.

    For more information about upload_update, see upload_update table event.

  • upload_old_row_insert   You use this script to handle old row values obtained by the remote database during its previous synchronization.

    For more information about upload_old_row_insert, see upload_old_row_insert table event.

  • upload_new_row_insert   You use this script to handle new row values (the updated values on the remote database).

    For more information about upload_new_row_insert, see upload_new_row_insert table event.

  • resolve_conflict   The resolve conflict script applies business logic to resolve the conflict.

    For more information about resolve_conflict, see resolve_conflict table event.

To install scripts for conflict detection and resolution
  1. Start Interactive SQL.

    1. At a command prompt, type dbisql.

    2. Click the Identification tab.

    3. In the User ID field, type DBA.

    4. In the Password field, type sql.

    5. Click the Database tab.

    6. In the Server Name field, type cons.

    7. Click OK.

  2. Install the conflict detection and resolution scripts:

    Execute the following in Interactive SQL:

    /* upload_update */
    call ml_add_table_script( 'ver1', 'Product',
     'UPDATE Product
       SET quantity = ?, last_modified = ?
        WHERE name = ?
        AND quantity=? AND last_modified=?' )
    /* upload_old_row_insert */
    call ml_add_table_script( 'ver1', 'Product',
     'INSERT INTO Product_old (name,quantity,last_modified)
       values (?,?,?)')
    /* upload_new_row_insert */
    call ml_add_table_script( 'ver1', 'Product',
     'INSERT INTO Product_new (name,quantity,last_modified)
       values (?,?,?)')
    /* resolve_conflict */
    call ml_add_table_script( 'ver1', 'Product',
     'declare @product_name varchar(128);
      declare @old_rem_val integer;
      declare @new_rem_val integer;
      declare @curr_cons_val integer; 
      declare @resolved_value integer;
      // obtain the product name
      SELECT name INTO @product_name
         FROM Product_old;
      // obtain the old remote value
      SELECT quantity INTO @old_rem_val
        FROM Product_old;
      //obtain the new remote value
      SELECT quantity INTO @new_rem_val
        FROM Product_new;
      // obtain the current value in cons
      SELECT quantity INTO @curr_cons_val
       FROM Product WHERE name = @product_name;
      // determine the resolved value
      SET @resolved_value =
        @curr_cons_val- (@old_rem_val - @new_rem_val);
      // update cons with the resolved value
      UPDATE Product
       SET quantity = @resolved_value
       WHERE name = @product_name;
      // clear the old and new row tables
      delete from Product_new;
      delete from Product_old

Setup for the SQL Anywhere consolidated database is complete.

Further reading

For more information about MobiLink conflict detection and resolution, see Handling conflicts.

For more information about MobiLink consolidated databases, see MobiLink consolidated databases.