generate_next_last_download

The script is used to invoke a user-defined algorithm to generate the next_last_download_timestamp.

Parameters

In the following table, the description provides the SQL data type. If you are writing your script in Java or .NET, use the appropriate corresponding data type.

In SQL scripts, you can specify event parameters by name or with a question mark. Using question marks has been deprecated. Use named parameters instead. You cannot mix names and question marks within a script. If you use question marks, the parameters must be in the order shown below and are optional only if no subsequent parameters are specified (for example, you must use parameter 1 if you are going to use parameter 2). If you use named parameters, you can specify any subset of the parameters in any order.

Parameter name for SQL scripts	Description	Order (deprecated for SQL)
s.remote_id	VARCHAR(128). The MobiLink remote ID. You can only reference the remote ID if you are using named parameters.	Not applicable
s.next_last_download	TIMESTAMP. This is an INOUT parameter. The MobiLink server initializes this parameter with the last_download_timestamp, a timestamp used to generate a download stream in the current synchronization.	1
s.username	VARCHAR(128). The MobiLink user name.	2
s.script_version	VARCHAR(128). Optional IN parameter to specify that the MobiLink server passes the script version string used for the current synchronization to this parameter. Question marks cannot be used to specify this parameter.	Not applicable

Remarks

This script is invoked in the prepare_for_download transaction, right before the prepare_for_download script is called.

Use this event with caution, especially with consolidated databases that support a snapshot isolation level, such as, SQL Anywhere, Oracle, Microsoft SQL Server, and IBM DB2 LUW 9.7. The MobiLink server always uses the snapshot isolation level for download with Oracle. By default, it also uses the snapshot isolation level for download with SQL Anywhere and Microsoft SQL Server, when the snapshot isolation level is enabled on the database.

For robust timestamp-based synchronization, the output of next_last_download must be the earlier of:

the current timestamp
the starting timestamp of the earliest open transaction updating (for example, inserting, updating or deleting) any table or view used to construct the download.

This script can also be specified as an ignored script using the --{ml_ignore} clause. When this script is defined as an ignored script, the MobiLink server does not call this script and does not use MobiLink internal logic to generate the next last download timestamp. Instead, the MobiLink server sends back to the client the last download timestamp that was sent by the client in the current synchronization. You can use this technique for synchronizations that always download all the rows from the consolidated database for all the synchronization tables. However, for timestamp-based synchronization, you should define this script as a real script using the appropriate business logic to generate the next last download timestamp. Alternatively, don't define any script for this event and the MobiLink server uses its internal logic to generate the next last download timestamp.

Example

The generate_next_last_download_timestamp script can be used in the MobiLink server to generate UTC time-based downloads. Here are the steps to set up a UTC time based download for Oracle using SQL:

Assume you have a sync table called my_table that is defined as follows:

CREATE TABLE my_table (  pk              INT PRIMARY KEY NOT NULL,
                         c1              VARCHAR(100) ,
                         last_modified   TIMESTAMP DEFAULT SYS_EXTRACT_UTC( SYSTIMESTAMP )
)

Create a stored procedure called GenerateNextDownloadTimestamp to get the starting time of the earliest open transaction in UTC in the Oracle database:

CREATE PROCEDURE GenerateNextDownloadTimestamp ( p_ts IN OUT TIMESTAMP ) AS
BEGIN
     SELECT SYS_EXTRACT_UTC( NVL( MIN( TO_TIMESTAMP( START_TIME, 'mm/dd/rr hh24:mi:ss' ) ),
                                    SYSTIMESTAMP ) )
          INTO p_ts FROM GV$TRANSACTION;
END;

Call the ml_add_connect_script to install the script:

call ml_add_connection_script( 
  'my_script_version',
  'generate_next_last_download_timestamp',
  '{ call GenerateNextDownloadTimestamp( {ml s.next_last_download} ) }' )

Note The MobiLink server logon ID must have a SELECT privilege on GV_$TRANSACTION.

generate_next_last_download_timestamp event

Example