Writing download_cursor scripts

You write download_cursor scripts to download information from the consolidated database to your remote database. You must write one of these scripts for each table in the remote database for which you want to download changes. You can use other scripts to customize the download process, but no others are necessary.

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.

Example

The following script could serve as a download_cursor script for a remote table that holds employee information. The MobiLink server would use this SQL statement to define the download cursor. This script downloads information about all the employees.

SELECT emp_id, emp_fname, emp_lname
FROM employee;

The MobiLink server passes specific parameters to some scripts. To use these parameters, you can use named parameters, or you can include a question mark in your SQL statement. In the latter case, the MobiLink server substitutes the value of the parameter before executing the statement against the consolidated database. The following script shows how you can use named parameters:

CALL ml_add_table_script( 
   'Lab',
   'ULOrder',
   'download_cursor',
   'SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status
         FROM ULOrder o
         WHERE o.last_modified >= {ml s.last_table_download}
               AND o.emp_name = {ml s.username}' )

Notes

Row values can be selected from a single table or from a join of multiple tables.
The script itself need not include the name of the remote table. The remote table need not have the same name as the table in the consolidated database. The name of the remote table is identified by an entry in the ml_table MobiLink system table. In Sybase Central, the remote tables are listed together with their scripts.
The rows in the remote table must contain the values of emp_id, emp_fname, and emp_lname. The remote columns must be in that order, although they can have different names. The columns in the remote database are in the same order as those in the reference database.
All cursor scripts must select the columns in the same order as the columns are defined in the remote database. Where column names or table structure is different in the consolidated database, columns should be selected in the correct order for the remote database, or equivalently, the reference database. Columns are assigned to columns in the remote database based on their order in the SELECT statement.
When you build an UltraLite application, the UltraLite generator creates a sample download script for each table in your UltraLite application. It inserts these sample scripts into your reference database. The example scripts assume that the consolidated database contains the same tables as your application. You must modify the sample scripts if your consolidated database differs in design, but these scripts provide a starting point.
The download_cursor script must contain all columns in the same order as they are defined in the remote database.