This example implements file-based download for snapshot synchronization. It sets up the three databases that are required by the file-based download, and then demonstrates how to download data. 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 scons.db
dbinit sremote.db
dbinit sfdef.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 sfdef_eng sfdef.db
dbeng11 -n scons_eng scons.db
dbeng11 -n sremote_eng stremote.db
dbdsn -y -w fbd_demo -c "eng=scons_eng;dbf=scons.db;uid=DBA;
   pwd=sql;astart=off;astop=off"

Open Interactive SQL, connect to scons.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=fbd_demo" -zu+ -ot scons.txt

Set up the snapshot 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 SQL to create table T1:

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

The following code creates a script version called filebased and creates a download script for that script version.

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

The following code creates a script version called normal and creates upload and download scripts for that script version.

CALL ml_add_table_script ( 'normal', 'T1',
  'upload_insert',
  'INSERT INTO T1 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' );

COMMIT;

The following command creates the stored procedure begin_pub and specifies that begin_pub is the begin_publication script for both the "normal" and "filebased" script versions:

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(
  'filebased',
  'begin_publication',
  '{ call begin_pub(
      {ml s.generation_number},
      {ml s.username},
      {ml s.publication_name} ) }' );

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

Create the snapshot example remote database

In this example, the remote database also contains one table, called T1. Connect to the remote database and run the following SQL to create the table T1, a publication called P1, and a user called U1. The SQL 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 creates an sp_hook_dbmlsync_validate_download_file hook to implement user-defined validation logic in the remote database:

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 snapshot example file-definition database

A file-definition database is required in MobiLink systems that use file-based download. This database has the same schema as the remote databases being updated by file-based download, and it contains no data or state information. The file-definition database is used solely to define the structure of the data that is to be included in the download file. One file-definition database can be used for many groups of remote databases, each defined by its own MobiLink group user name.

The following code defines the file-definition database for this sample. It creates a schema that is identical to the remote database, and also creates:

You must connect to sfdef.db before running this code.

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 initialize your new remote database by performing a normal synchronization.

You can perform an initial synchronization of the remote database with the script version called normal that was created earlier:

dbmlsync -c "uid=DBA;pwd=sql;eng=sremote_eng;
  dbf=sremote.db" -v+ -e "sv=normal"

Demonstrate the snapshot 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 VALUES( 1, 1 );
INSERT INTO T1 VALUES( 2, 4 );
INSERT INTO T1 VALUES( 3, 9 );
COMMIT;

The following command must be run on the computer that holds the file-definition database. It does the following:

dbmlsync -c 
   "uid=DBA;pwd=sql;eng=sfdef_eng;dbf=sfdef.db"
   -v+ -e "sv=filebased" -bc file1.df -be ok -ot fdef.txt

To apply the download file, run dbmlsync with the -ba option on the remote database, supplying the name of the download file you want to apply:

dbmlsync -c "uid=DBA;pwd=sql;eng=sremote_eng;
   dbf=sremote.db" -v+ -ba file1.df -ot remote.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 snapshot example

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

del file1.df
mlstop -h -w
dbstop -y -c "eng=sfdef_eng; uid=DBA; pwd=sql"
dbstop -y -c "eng=scons_eng; uid=DBA; pwd=sql"
dbstop -y -c "eng=sremote_eng; uid=DBA; pwd=sql"
dberase -y sfdef.db
dberase -y scons.db
dberase -y sremote.db