Detecting conflicts with upload_update scripts

To use the upload_update script to detect conflicts, include all columns in the WHERE clause:

UPDATE table-name
SET col1 = {ml r.col1},  col2  = {ml r. col2 } ...
WHERE pk1 = {ml o.pk1} AND pk2 = {ml o.pk2} ...
   AND col1 = {ml o.col1} AND col2 = {ml o.col2} ...

In this statement, col1, col2 and so on are the non-primary key columns, while pk1, pk2 and so on are primary key columns. The values passed to the second set of non-primary key columns (o.) are the pre-image (or old values) of the updated row. The WHERE clause compares old values uploaded from the remote to current values in the consolidated database. If the values do not match, the update is ignored, preserving the values already on the consolidated database.

See upload_update table event.

The upload_update script is used for conflict detection only if no conflict is detected by upload_fetch or upload_fetch_column_conflict.

Scenario 1

You define scripts for the following events: upload_update, upload_old_row_insert, upload_new_row_insert, and resolve_conflict.

You define the following upload_update script:

UPDATE product
SET name={ml r.name}, description={ml r.description}
WHERE id={ml r.id}
   AND name={ml o.name}
   AND description={ml o.description]

MobiLink performs the update and then checks to see how many rows were modified. If no rows were modified, then MobiLink has detected a conflict: no row in the consolidated database matches the pre-image row. MobiLink calls the upload_old_row_insert and upload_new_row_insert scripts, followed by resolve_conflict.

See Resolving conflicts with resolve_conflict scripts.

Scenario 2

You do not define scripts for upload_old_row_insert, upload_new_row_insert, and resolve_conflict. Instead, you create a stored procedure to handle the conflict detection and resolution and you call it in the upload_update script.

See Resolving conflicts with upload_update scripts.