Resolving conflicts with resolve

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

Discuss this page in DocCommentXchange.