Lesson 4: Adding synchronization scripts

This lesson assumes you have completed all preceding lessons. See Lesson 1: Setting up a text file data source.

In this lesson, you add scripts to your consolidated database for SQL row handling and direct row handling.

SQL row handling allows you to synchronize remote data with tables in your MobiLink consolidated database. SQL-based scripts define:

How data that is uploaded from a MobiLink client is to be applied to the consolidated database.
What data should be downloaded from the consolidated database.

The following SQL-based upload and download events are created:

upload_insert This event defines how new orders inserted in a client database should be applied to the consolidated database.
download_cursor This event defines the orders that should be downloaded to remote clients.
download_delete_cursor This event is required when using synchronization scripts that are not upload-only. Set the MobiLink server to ignore this event for the purpose of this tutorial.

You use direct row handling to add synchronization script information to your MobiLink consolidated database using stored procedures. In this lesson, you register method names corresponding to the handle_UploadData, handle_DownloadData, end_download, download_cursor, and download_delete_cursor events. You create your own Java or .NET class in a later lesson.

Add scripts to your consolidated database for SQL row handling and direct row handling

Connect to your consolidated database from Interactive SQL, if you are not already connected, by running the following command:
dbisql -c "DSN=mldirect_db"

Use the ml_add_table_script stored procedure to add SQL-based table scripts for the upload_insert, download_cursor and download_delete_cursor events.

Execute the following SQL statements in Interactive SQL. The upload_insert script inserts the uploaded order_id, product_id, quantity, and order_status into the MobiLink consolidated database. The download_cursor script uses timestamp-based filtering to download updated rows to remote clients.

CALL ml_add_table_script( 'default', 'RemoteOrders', 
    'upload_insert',
    'INSERT INTO RemoteOrders( order_id, product_id, quantity, order_status)
     VALUES( {ml r.order_id}, {ml r.product_id}, {ml r.quantity}, {ml r.order_status} )' );
   
CALL ml_add_table_script( 'default', 'RemoteOrders',
    'download_cursor',
    'SELECT order_id, product_id, quantity, order_status
     FROM RemoteOrders WHERE last_modified >= {ml s.last_table_download}');

CALL ml_add_table_script( 'default', 'RemoteOrders',
    'download_delete_cursor', '--{ml_ignore}');

COMMIT;

You use this method to free memory resources when the MobiLink server runs the end_download connection event.

For Java, execute the following statement in Interactive SQL:

CALL ml_add_java_connection_script( 'default',
    'end_download',
    'MobiLinkOrders.EndDownload' );

For .NET, execute the following statement in Interactive SQL:

CALL ml_add_dnet_connection_script( 'default',
    'end_download',
    'MobiLinkOrders.EndDownload' );

Interactive SQL registers the user-defined EndDownload method for the end_download event.

For Java, execute the following statements in Interactive SQL:

CALL ml_add_java_connection_script( 'default', 
    'handle_UploadData',
    'MobiLinkOrders.GetUpload' );
   
CALL ml_add_java_connection_script( 'default',
    'handle_DownloadData',
    'MobiLinkOrders.SetDownload' );

For .NET, execute the following statements in Interactive SQL:

CALL ml_add_dnet_connection_script( 'default', 
    'handle_UploadData',
    'MobiLinkOrders.GetUpload' );
   
CALL ml_add_dnet_connection_script( 'default',
    'handle_DownloadData',
    'MobiLinkOrders.SetDownload' );

Interactive SQL registers the user-defined GetUpload and SetDownload methods for the handle_UploadData and handle_DownloadData events, respectively. You create these methods in an upcoming lesson.

Execute the following statements in Interactive SQL:

CALL ml_add_table_script( 'default', 'OrderComments',
    'download_cursor', '--{ml_ignore}');

CALL ml_add_table_script( 'default', 'OrderComments',
    'download_delete_cursor', '--{ml_ignore}');

The download_cursor and download_delete_cursor events must be registered for the OrderComments table when using scripts because the synchronization is bi-directional and not upload-only. See Required scripts.

Commit your changes.

Execute the following statement in Interactive SQL:
COMMIT;
Close Interactive SQL.
Proceed to Lesson 5: Creating a Java or .NET class for MobiLink direct row handling.

Lesson 4: Adding synchronization scripts

Add scripts to your consolidated database for SQL row handling and direct row handling

See also