MobiLink Synchronization Reference
  Synchronization Events

download_cursor cursor event

Function 

Defines a cursor to select rows that are to be downloaded and inserted or updated in the remote database.

Parameters 

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

Event parameters are optional only if no subsequent parameters are specified. You must use parameter 1 if you want to use parameter 2.

Default action 

None.

A default download_cursor SQL script can be generated using the MobiLink synchronization server -za option. Also, the UltraLite analyzer generates a SELECT statement based on your reference database that you can use to get started.

Description 

The MobiLink synchronization server opens a read-only cursor with which to fetch a list of rows to download to the remote database. This script should contain a suitable SELECT statement.

The parameters are the last_download timestamp and the user name. You can use these values if you choose by placing question marks in your SQL statement.

You can have one download_cursor script for each table in the remote database.

The last_download timestamp is the value obtained from the consolidated database during the last successful synchronization immediately prior to the download phase. If the current user has never synchronized successfully, this value is set to 1900-01-01.

To optimize performance of the download stage of synchronization to UltraLite clients, when the range of primary key values is outside the current rows on the device, you should order the rows in the download cursor by primary key. Downloads of large reference tables, for example, can benefit from this optimization.

Note that download_cursor allows for cascading deletes. Thus, you can delete records from a database.

For Java and .NET applications, this script must return valid SQL.

See also 

download_delete_cursor cursor event

SQL example 

The following example comes from an Oracle installation, although the statement is valid against all supported databases. The example downloads all rows that have been changed since the last time the user downloaded data, and which match the user name in the emp_name column.

call ml_add_table_script( 
  'Lab',
  'ULOrder',
  'download_cursor',
  'SELECT order_id, cust_id, prod_id, emp_id, 
      disc, quant, notes, status
       FROM ULOrder 
       WHERE last_modified >= ? AND emp_name = ?' )

To write a download_cursor SQL script that does not use the first parameter (the last_download timestamp), but does use the second parameter (the MobiLink user name), add a dummy clause that affects no rows. For example:

call ml_add_table_script(
   'Lab',
   'ULOrder',
   'download_cursor',
   'SELECT order_id, cust_id, prod_id, emp_id, disc,
       quant, notes, status
        FROM ULOrder WHERE ? IS NOT NULL AND emp_name = ?' )

You must still use both parameters, but the first ? is a place holder that does nothing.

Java example 

The following stored procedure call registers a Java method called downloadCursor as the script for the download_cursor cursor event when synchronizing the script version ver1. This syntax is for Adaptive Server Anywhere consolidated databases.

call ml_add_java_table_script(
   'ver1',
   'table1',
   'download_cursor',
   'ExamplePackage.ExampleClass. downloadCursor ' )

Following is the sample Java method downloadCursor. It dynamically creates the SQL statement for the download cursor.

public String downloadCursor( Timestamp ts,
String user )
{  return( "SELECT order_id, cust_id, prod_id, emp_id,
disc, " + " quant, notes, status " + "FROM ULOrder " +
"WHERE emp_name = '" + user + "'" ); }

.NET example 

The following stored procedure call registers a .NET method called DownloadCursor as the script for the download_cursor cursor event when synchronizing the script version ver1 and the table table1. This syntax is for Adaptive Server Anywhere consolidated databases.

call ml_add_dnet_table_script(
  'ver1', 'table1', 'download_cursor',
  'TestScripts.Test.DownloadCursor'
)

Following is the C# signature for the call DownloadCursor.

public string DownloadCursor(
  DateTime timestamp,
  string user )

The following C# example populates a temporary table with the contents of a file called rows.txt. It then returns a cursor that causes MobiLink to send the rows in the temporary table to the remote database.

public string DownloadCursor( 
   DateTime ts, 
   string user )
{
  DBCommand   stmt   = curConn.CreateCommand();
  StreamReader  input  = new StreamReader( "rows.txt" );
  string        sql    = input.ReadLine();

  stmt.CommandText = "DELETE FROM dnet_dl_temp";
  stmt.ExecuteNonQuery();

  while( sql != null ){
   stmt.CommandText = "INSERT INTO dnet_dl_temp VALUES " + sql;
   stmt.ExecuteNonQuery();
   sql = input.ReadLine();
  }
  return( "SELECT * FROM dnet_dl_temp" );
}