Defines a cursor to select rows to download and insert or update 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.

In SQL scripts, you can specify event parameters by name or with a question mark, but 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 want to use parameter 2). If you use named parameters, you can specify any subset of the parameters in any order.

Default action

None.

Remarks

The MobiLink 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.

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

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.

Each download_cursor script must contain a SELECT statement or a call to a procedure that contains a SELECT statement. The MobiLink server uses this statement to define a cursor in the consolidated database.

The script must select all columns that correspond to the columns in the corresponding table in the remote database. The columns in the consolidated database can have different names than the corresponding columns in the remote database, but they must be of compatible types.

The columns must be selected in the order that the corresponding columns are defined in the remote database.

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

To avoid downloading unnecessary rows, you should include the following line in the WHERE clause of your download_cursor script:

AND last_table_download > '1900/1/1'

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

If you are considering using READPAST table hints in download_cursor scripts because you are doing lots of updates that affect download performance, consider using snapshot isolation for downloads instead. The READPAST table hint can cause problems if used in download_cursor scripts. When using timestamp-based downloads, the READPAST hint can cause rows to be missed, and can cause a row to never be downloaded to a remote database. For example:

From this point on, the row is never downloaded unless it is updated. A possible workaround is to implement a modify_next_last_download_timestamp script and set the last download time to be the start time of the oldest open transaction.

See also

• Script parameters
• Adding and deleting scripts
• Writing scripts to download rows
• Writing download_cursor scripts
• Partitioning rows among remote databases
• download_delete_cursor table event
• Using remote IDs and MobiLink user names in scripts
• Using last download times in scripts
• "Using READPAST with MobiLink synchronization" in FROM clause

SQL example

The following example comes from an Oracle installation, although the statement is valid against all supported databases. This example downloads all rows that have been changed since the last time the user downloaded data, and that 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 >= {ml s.last_table_download} 
    AND emp_name = {ml s.username}' )

Java example

The following call to a MobiLink system procedure registers a Java method called downloadCursor as the script for the download_cursor table event when synchronizing the script version ver1.

CALL ml_add_java_table_script(
   'ver1',
   'ULCustomer',
   'download_cursor',
   'ExamplePackage.ExampleClass.downloadCursor ' )

Following is the sample Java method downloadCursor. It returns a SQL statement to download rows where the last_modified column is greater than or equal to the last download time.

public String downloadCursor( 
  java.sql.Timestamp ts, 
  String user )  {
  return( "SELECT cust_id, cust_name FROM ULCustomer 
             WHERE last_modified >= ' " 
   + ts + " ' ");
}

.NET example

The following call to a MobiLink system procedure registers a .NET method called DownloadCursor as the script for the download_cursor table event when synchronizing the script version ver1 and the table table1.

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

Following is the sample .NET method DownloadCursor. It 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. This syntax is valid for SQL Anywhere consolidated databases.

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" );
}