Synchronizing customers in the Contact sample

Synchronizing sales representatives in the Contact sample

Synchronizing contacts in the Contact sample

MobiLink Synchronization User's Guide
The Contact Sample Application
Synchronization

Synchronizing customers in the Contact sample

The synchronization scripts for the Customer table illustrate timestamp-based synchronization and partitioning rows. Both of these techniques minimize the amount of data that is transferred during synchronization while maintaining consistent table data.

For more information, see Timestamp-based synchronization.

For more information, see Partitioning rows among remote databases.

Business rules

The business rules governing customers are as follows:

Customer information can be modified at both the consolidated and remote databases.
Periodically, customers may be reassigned among sales representatives. This process is commonly called territory realignment.
Each remote database contains only the customers they are assigned to.

Downloads

download_cursor The following download_cursor script downloads only active customers for whom information has changed since the last successful download. It also downloads only customers assigned to a particular sales representative.
```
SELECT cust_id, Customer.name, Customer.rep_id
FROM Customer key join SalesRep
WHERE Customer.last_modified > ?
AND SalesRep.ml_username = ?
AND Customer.active = 1
```
download_delete_cursor The following download_delete_cursor script downloads only customers for whom information has changed since the last successful download. It deletes all customers marked as inactive or who are not assigned to the sales representative.
```
SELECT cust_id
FROM Customer key join SalesRep
WHERE Customer.last_modified > ?
AND ( SalesRep.ml_username != ? OR Customer.active = 0 )
```
If rows are deleted from the Customer table at the consolidated database, they do not appear in this result set and so are not deleted from remote databases. Instead, customers are marked as inactive.

When territories are realigned, this script deletes those customers no longer assigned to the sales representative. It also deletes customers who are transferred to other sales representatives. Such additional deletes are flagged with a SQLCODE of 100 but do not interfere with synchronization. A more complex script could be developed to identify only those customers transferred away from the current sales representative.

The MobiLink client carries out cascading deletes at the remote database, so this script also deletes all contacts who work for customers assigned to some other sales representative.

Uploads

Customer information can be inserted, updated, or deleted at the remote database. The scripts corresponding to these operations are as follows:

upload_insert The following upload_insert script adds a row to the Customer table, marking the customer as active:
```
INSERT INTO Customer(
   cust_id, name, rep_id, active )
VALUES ( ?, ?, ?, 1 )
```
upload_update The following upload_update script modifies the customer information at the consolidated database:
```
UPDATE Customer
SET name = ?, rep_id = ?
WHERE cust_id = ?
```
Conflict detection is not carried out on this table.
upload_delete The following upload_delete script marks the customer as inactive at the consolidated database. It does not delete a row.
```
UPDATE Customer
SET active = 0
WHERE cust_id = ?
```