Lesson 2: Add synchronization scripts

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

SQL row handling

SQL row handling allows you to synchronize remote data with tables in your MobiLink consolidated database. The 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.

In this lesson, you write synchronization scripts for the following SQL-based upload and download events.

upload_insert To define how new orders inserted in a remote client database should be applied to the consolidated database.
download_cursor To define what orders updated in the MobiLink consolidated database should be downloaded to remote clients.

In this procedure, you add synchronization script information to your MobiLink consolidated database using stored procedures.

To add SQL-based scripts to MobiLink system tables

Connect to your consolidated database in Interactive SQL if you are not already connected.

Run the following command:
dbisql -c "dsn=mobilink_db"

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

Run the following commands 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( ?, ?, ?, ? )' );
   
CALL ml_add_table_script( 'default', 'RemoteOrders',
   'download_cursor',
     'SELECT order_id, product_id, quantity, order_status
      FROM RemoteOrders WHERE last_modified >= ?');

commit

Direct row handling processing

In this tutorial, you use direct row handling to add special processing to a SQL-based synchronization system. In this procedure you register method names corresponding to the handle_UploadData, handle_DownloadData, and end_download events. You create your own Java or .NET class in Lesson 3: Write Java or .NET logic for processing direct row handling.

To add information for direct row handling in MobiLink system tables

Connect to your consolidated database in Interactive SQL.

Run the following command:
dbisql -c "dsn=mobilink_db"

You use this method to free i/o resources when the end_download connection event fires.

For Java, execute the following command in Interactive SQL.

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

For .NET, execute the following command 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 commands 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' );

commit

For .NET, execute the following commands 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' );

commit

Interactive SQL registers the user-defined GetUpload and SetDownload methods for the handle_UploadData and handle_DownloadData events, respectively.