Resolving conflicts with resolve_conflict scripts

When the MobiLink server detects a conflict using an upload_fetch script, the following events take place.

  • The MobiLink server inserts old row values uploaded from the remote database as defined by the upload_old_row_insert script. Typically, the old values are inserted into a temporary table.

    See upload_old_row_insert table event.

  • The MobiLink server inserts the new row values uploaded from the remote database as defined by the upload_new_row_insert script. Typically, the new values are inserted into a temporary table.

    See upload_new_row_insert table event.

  • The MobiLink server executes the resolve_conflict script. In this script you can either call a stored procedure, or define a sequence of steps to resolve the conflict using the new and old row values.

For more information, see resolve_conflict table event.

Example

In the following example, you create scripts for six events and then you create a stored procedure.

  • In the begin_synchronization script, you create two temporary tables called contact_new and contact_old. (You could also do this in the begin_connection script.)
  • The upload_fetch script detects the conflict.
  • When there is a conflict, the upload_old_row_insert and upload_new_row_insert scripts populate the two temporary tables with the new and old data uploaded from the remote database.
  • The resolve_conflict script calls the stored procedure MLResolveContactConflict to resolve the conflict.
Event Script
begin_synchronization
CREATE TABLE #contact_new( 
   id  INTEGER,
   location CHAR(36),
   contact_date DATE);
CREATE TABLE #contact_old(
   id  INTEGER,
   location CHAR(36),
   contact_date DATE)
upload_fetch
SELECT id, location, contact_date
   FROM contact
   WHERE id = {ml r.id}
upload_old_row_insert
INSERT INTO #contact_new( id, location, contact_date ) 
  VALUES ( {ml r.id}, {ml r.location}, {ml r.contact_date} )
upload_new_row_insert
INSERT INTO #contact_old( id, location, contact_date )
  VALUES ( {ml r.id}, {ml r.location}, {ml r.contact_date} )
resolve_conflict
CALL MLResolveContactConflict( )
end_synchronization
DROP TABLE #contact_new;
DROP TABLE #contact_old

The stored procedure MLResolveContactConflict is as follows:

CREATE PROCEDURE MLResolveContactConflict( )
BEGIN
  --update the consolidated database only if the new contact date
  --is later than the existing contact date
  UPDATE contact c
    SET c.contact_date = cn.contact_date
      FROM #contact_new cn
      WHERE c.id = cn.id
        AND cn.contact_date > c.contact_date;
  --cleanup
  DELETE FROM #contact_new;
  DELETE FROM #contact_old;
END