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.
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:
The current value in the consolidated database.
After remote1 synchronizes, the value in the consolidated database is 7.
The new row value that Remote2 uploaded.
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.
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.
Start Interactive SQL.
At a command prompt, type dbisql.
Click the Identification tab.
In the User ID field, type DBA.
In the Password field, type sql.
Click the Database tab.
In the Server Name field, type cons.
Install the conflict detection and resolution scripts:
Execute the following in Interactive SQL:
/* upload_update */ call ml_add_table_script( 'ver1', 'Product', 'upload_update', 'UPDATE Product SET quantity = ?, last_modified = ? WHERE name = ? AND quantity=? AND last_modified=?' ) go /* upload_old_row_insert */ call ml_add_table_script( 'ver1', 'Product', 'upload_old_row_insert', 'INSERT INTO Product_old (name,quantity,last_modified) values (?,?,?)') go /* upload_new_row_insert */ call ml_add_table_script( 'ver1', 'Product', 'upload_new_row_insert', 'INSERT INTO Product_new (name,quantity,last_modified) values (?,?,?)') go /* resolve_conflict */ call ml_add_table_script( 'ver1', 'Product', 'resolve_conflict', '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.
For more information about MobiLink conflict detection and resolution, see Handling conflicts.
For more information about MobiLink consolidated databases, see MobiLink consolidated databases.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|