A session-based synchronization technology designed to synchronize UltraLite and Adaptive Server Anywhere databases with many industry-standard SQL database management systems from Sybase and other vendors.

We use it to synchronize data from one or more bookstores to a consolidated database running at their warehouse. This lets them run reports that aggregate data from many stores, transfer stock between stores, centralize purchasing, and do stock look-ups and one-time data entry,

How Does It Work?
Data synchronization is initiated by the client using dbmlsync. This creates a socket connection to dbmlsrv9, which is running on the consolidated server. It uses port 2439.

Mobilink Commands
Here are example commands for the server and client respectively. Note that they assume that you have a database called blconsolidated.db in your root directory and DSN entries called BLConsolidated and BlRemote for your server and client respectively.

C:\>dbsrv9 -n BLConsolidated blconsolidated.db
C:\>dbmlsrv9 -c "DSN=BLConsolidated" -v+ -dl -o ml.txt -vcrs -o debug.txt -zu+
C:\>dbmlsync -c "DSN=BLRemote" -v+ -dl -e Verbose='on' -o rem.txt

The first command starts the consolidated database server. The second starts the Mobilink synchronization server. The last is the command issued by the client to start the synchronization process.

How Do We Tell It What To Sync?
By its publication. What's that? A database object that identifies replicated data. In MobiLink publications exist only on the clients. A publication consists of articles. Periodically, the changes made to each publication are replicated to all subscribers to that publication. MobiLink users can synchronize a publication by creating a synchronization subscription to it.

If you look in Sybase Central you can see the definition of our multi-site publication. It's listed under publications and is called Booklog.

You must specify which tables and columns you want to replicate. A wildcard publication is specified by the '*' and indicates all columns. These are bad though because as you'll see on the server we must define what columns are going to be received from the client. The ordering of these two must match. If they don't the scripts won't work. So consider the addition of a column to a table without modificating the server-side scripts, if you have a wildcard publication, well, you just broke synchronization for that table.

What Are the Server-Side Scripts and What Do They Do?
There are many, look in the help, but there are three we usually code for:

• download_cursor - What data should be pushed from the server to the client
• upload_insert - Called after detecting an insert has occurred on the client
• upload_update - Called after detecting a modification has occurred on the client

The server-side scripts are stored on consolidated. You can see them by connecting to the Mobilink synchronization service in Sybase Central then look under synchronized tables. For each synchronized table, you'll see their respective server-side scripts.

How Does Mobilink Know What Changes Have Occurred on the Client?
Through the transaction log. That's why you must never delete it.

Let's look at the relationship between a Mobilink publication and server-side scripts for the color_tb. First the client publication, (remember this is stored on the remote db), it has two columns published as part of its article. They are co_code and co_desc. Again you can look at it if you connect to the remote database using Sybase Central and look under the publication tab.

Now, the server-side scripts (remember this is stored on the consolidated db), it has the three scripts we discussed earlier. Once again, you can look at them if you connect to the Mobilink synchronization service and look under the synchronized table tab for the color_tb.

Next we 'll look at how columns are added to articles in the publication and how server-side scripts are added to consolidated in code. This work is done in the upgrade scripts for client and server programs respectively.

How to Modify the Server-Side Scripts in the Upgrade Object for the color_tb
There are three cases:

• if exec_sql("call ml_add_table_script('default', 'color_tb', 'download_cursor', 'SELECT co_code, co_desc, new_col FROM color_tb WHERE last_updt_time >= ?');") < 0 then return -1
• if exec_sql("call ml_add_table_script('default', 'color_tb', 'upload_insert', 'call GROUP1.AddColor(?,?,?)');") < 0 then return -1
• if exec_sql("call ml_add_table_script('default', 'color_tb', 'upload_update', 'UPDATE color_tb SET co_desc = ?, new_col = ? WHERE co_code = ?');") < 0 then return -1

Mobilink is time-based synchronization. That's why we have a last_updt_time in the download_cursor. This prevents data from being re-downloaded.

The question mark is a placeholder for the data that's sent to consolidated. It will be sent in the order in which it's defined in the table. That's why the order of your scripts must match what's defined by the remote table definition.

For the upload_update event you'll specify the primary key as part of the where clause.

For the upload_insert event, we call a stored procedure to do the insert, but we didn't have to. It's just one technique for making the insertion into consolidated. An INSERT statement could have been used instead.

Now for a closer look at the stored procedure used to add server-side scripts, ml_add_table_script(....)

Function
Use this stored procedure to add or delete SQL table scripts in the consolidated database:

ml_add_table_script(version VARCHAR(128), table_name VARCHAR(128), event VARCHAR(128), script TEXT)

Description
When you add a script, the script is inserted into the ml_script table and the appropriate references are defined to associate the script with the table, event, and script version you specify. If the version name is new, it's automatically inserted into the ml_version table.

To delete a table script, set the script parameter to NULL.

What Does That Modified Stored Procedure for the update_insert Event Look Like?

ALTER PROCEDURE "GROUP1"."AddColor"
   (
    IN code char(5),
    IN description char(30),
    IN parm3 char(30)
   )
BEGIN
    IF NOT EXISTS ( SELECT 1 FROM color_tb WHERE co_code = code ) THEN
       INSERT INTO color_tb
       (co_code, co_desc, new_col) VALUES (code, description, parm3 );
    ELSE

    END IF
;
END

Resources

MobiLink Synchronization User's Guide (part of the online help)

www.ianywhere.com

www.risingroad.com

www.google.com

www.sybase.com/developer/newsgroups

Breck Carter. SQL Anywhere Studio 9 Developer's Guide. Wordware Publishing, Inc. 2004. ISBN 1-55622-506-7