This example implements file-based download for timestamp-based synchronization. It sets up the three databases and then demonstrates how to download data by file. This example is presented in such a way that you can either just read through it, or you can cut and paste the text to run the sample.

Create databases for the sample

The following commands create the three databases used in the example: a consolidated database, a remote database, and a file-definition database.

dbinit tcons.db
dbinit tremote.db
dbinit tfdef.db

The following commands start the three databases and create a data source name for MobiLink to use to connect to the consolidated database.

dbeng11 -n tfdef_eng tfdef.db
dbeng11 -n tcons_eng tcons.db
dbeng11 -n tremote_eng tremote.db
dbdsn -y -w tfbd_demo -c "eng=tcons_eng;dbf=tcons.db;uid=DBA;
   pwd=sql;astart=off;astop=off"

Open Interactive SQL, connect to tcons.db and run the MobiLink setup script. For example:

read "c:\Program Files\SQL Anywhere 11\MobiLink\setup\syncsa.sql"

Start the MobiLink server:

start mlsrv11 -v+ -c "dsn=tfbd_demo" -zu+ -ot tcons.txt

Set up the timestamp example consolidated database

In this example, the consolidated database has one table, called T1. After connecting to the consolidated database, you can run the following code to create T1:

CREATE TABLE T1 (
 pk  INTEGER PRIMARY KEY,
 c1  INTEGER,
 last_modified TIMESTAMP DEFAULT TIMESTAMP
);

The following code defines a script version called normal with a minimal number of scripts. This script version is used for synchronizations that do not use file-based download.

CALL ml_add_table_script( 'normal', 'T1', 
   'upload_insert',
   'INSERT INTO T1( pk, c1) VALUES( {ml r.pk}, {ml r.c1} )' );
 
CALL ml_add_table_script( 'normal', 'T1',
   'upload_update', 
   'UPDATE T1 SET c1 = {ml r.c1} WHERE pk = {ml r.pk} ' );

CALL ml_add_table_script( 'normal', 'T1',
   'upload_delete', 
   'DELETE FROM T1 WHERE pk = {ml r.pk}' );

CALL ml_add_table_script( 'normal', 'T1',
   'download_cursor',
   'SELECT pk, c1 FROM T1 
      WHERE last_modified >= {ml s.last_table_download}' );

The following code sets the generation number for all subscriptions to 1. It is good practice to use generation numbers in case your consolidated database ever becomes lost or corrupted and you need to force an upload.

CREATE PROCEDURE begin_pub ( 
       INOUT generation_num integer,
       IN    username       varchar(128),
       IN    pubname        varchar(128) )
BEGIN
 SET generation_num = 1;
END;
 
CALL ml_add_connection_script( 'normal', 
    'begin_publication', 
    '{ call begin_pub( 
        {ml s.generation_number},
        {ml s.username},
        {ml s.publication_name},
        {ml s.last_publication_upload},
        {ml s.last_publication_download} ) }' );
    
COMMIT;

The following code defines the script version called filebased. This script version is used to create file-based download.

CALL ml_add_connection_script( 'filebased', 
    'begin_publication', 
    '{ call begin_pub( 
        {ml s.generation_number},
        {ml s.username},
        {ml s.publication_name} ) }' );

CALL ml_add_table_script( 'filebased', 'T1',
    'download_cursor',
    'SELECT pk, c1 FROM T1 
        WHERE last_modified >= {ml s.last_table_download}' );

The following code sets the last download time so that all changes that occurred within the last five days are included in download files. Any remote that has missed all the download files created in the last five days have to perform a normal synchronization before being able to apply any more file-based downloads.

CREATE PROCEDURE ModifyLastDownloadTimestamp( 
       INOUT last_download_timestamp TIMESTAMP,
       IN    ml_username             VARCHAR(128) )
BEGIN
    SELECT dateadd( day, -5, CURRENT TIMESTAMP )
    INTO last_download_timestamp;
END;
 
CALL ml_add_connection_script( 'filebased', 
   'modify_last_download_timestamp',
   'CALL ModifyLastDownloadTimestamp(
      {ml s.last_download}, {ml s.username} )' );
    
COMMIT;

Create the timestamp example remote database

In this example, the remote database also contains one table, called T1. After connecting to the remote database, run the following code to create table T1, a publication called P1, and a user called U1. The code also creates a subscription for U1 to P1.

CREATE TABLE T1 (
 pk INTEGER PRIMARY KEY,
 c1 INTEGER
);

CREATE PUBLICATION P1 (
    TABLE T1
);

CREATE SYNCHRONIZATION USER U1;

CREATE SYNCHRONIZATION SUBSCRIPTION 
TO P1
FOR U1;

The following code defines an sp_hook_dbmlsync_validate_download_file stored procedure. This stored procedure prevents the application of download files that do not have the string "ok" embedded in them.

CREATE PROCEDURE sp_hook_dbmlsync_validate_download_file()
BEGIN
    DECLARE udata varchar(256);
    
    SELECT value 
    INTO udata
    FROM #hook_dict
    WHERE name = 'user data';
    
    IF udata <> 'ok' THEN 
      UPDATE #hook_dict 
      SET value = 'FALSE'
      WHERE name = 'apply file';
    END IF;
END

Create the timestamp example file-definition database

The following code defines the file-definition database for the timestamp example. It creates a table, a publication, a user, and a subscription for the user to the publication.

CREATE TABLE T1 (
 pk INTEGER PRIMARY KEY,
 c1 INTEGER
);

CREATE PUBLICATION P1 (
    TABLE T1
);

CREATE SYNCHRONIZATION USER G1;

CREATE SYNCHRONIZATION SUBSCRIPTION 
TO P1
FOR G1;

Prepare for initial synchronization

To prepare your new remote database so that you can apply a download file, you need to either perform a normal synchronization or create the download file with the dbmlsync -bg option. This example shows you how to use -bg.

The following code defines a script version called filebased_init for the consolidated database. This script version has a single begin_publication script.

CALL ml_add_table_script( 
  'filebased_init', 'T1', 'download_cursor',
  'SELECT pk, c1 FROM T1' );

CALL ml_add_connection_script( 
  'filebased_init', 
  'begin_publication', 
  '{ call begin_pub(
      {ml s.generation_number},
      {ml s.username},
      {ml s.publication_name} ) }' );
    
COMMIT;

The following two command lines create and apply an initial download file using the script version called filebased_init and the -bg option.

dbmlsync -c "uid=DBA;pwd=sql;eng=tfdef_eng;dbf=tfdef.db" 
  -v+ -e "sv=filebased_init" -bc tfile1.df -be ok -bg 
  -ot tfdef1.txt

dbmlsync -c "uid=DBA;pwd=sql;eng=tremote_eng;dbf=tremote.db" 
  -v+ -ba tfile1.df -ot tremote.txt

Demonstrate the timestamp example file-based download

Connect to the consolidated database and insert some data that is synchronized by file-based download, such as the following:

INSERT INTO T1(pk, c1) VALUES( 1, 1 );
INSERT INTO T1(pk, c1) VALUES( 2, 4 );
INSERT INTO T1(pk, c1) VALUES( 3, 9 );
commit;

The following command line creates a download file containing the new data.

dbmlsync -c 
   "uid=DBA;pwd=sql;eng=tfdef_eng;dbf=tfdef.db" 
   -v+ -e "sv=filebased" -bc tfile2.df -be ok -ot tfdef2.txt

The following command line applies the download file to the remote database.

dbmlsync -c "uid=DBA;pwd=sql;eng=tremote_eng;dbf=tremote.db" 
   -v+ -ba tfile2.df -ot tfdef3.txt

The changes are now applied to the remote database. Open Interactive SQL, connect to the remote database, and run the following SQL statement to verify that the remote has the data:

SELECT * FROM T1

Clean up the timestamp example

The following commands stop all three database servers and then erase the files.

del tfile1.df
mlstop -h -w
dbstop -y -c "eng=tfdef_eng; uid=DBA; pwd=sql"
dbstop -y -c "eng=tcons_eng; uid=DBA; pwd=sql"
dbstop -y -c "eng=tremote_eng; uid=DBA; pwd=sql"
dberase -y tfdef.db
dberase -y tcons.db
dberase -y tremote.db