download_delete_cursor scripts

You write download_delete_cursor scripts to delete rows from your remote database. You must write one of these scripts for each table in the remote database participating in the download. If you do not want to delete rows, define each script as --{ml_ignore}. Alternatively, you can use the ml_add_missing_dnld_scripts system procedure to define missing download scripts as ignored. See ml_add_missing_dnld_scripts system procedure.

You cannot just delete rows from the consolidated database and have them disappear from remote databases. You need to keep track of the primary keys for deleted rows, so that you can select those primary keys with your download_delete_cursor. There are two common techniques for achieving this:

  • Logical deletes   Do not physically delete the row in the consolidated database. Instead, have a status column that keeps track of whether rows are valid. This simplifies the download_delete_cursor. However, the download_cursor and other applications may need to be modified to recognize and use the status column. If you have a last modified column that holds the time of deletion, and if you also keep track of the last download time for each remote, then you can physically delete the row once all remote download times are newer than the time of deletion.

  • Shadow table   For each table for which you want to track deletes, create a shadow table with two columns, one holding the primary key for the table, and the other holding a timestamp. Create a trigger that inserts the primary key and timestamp into the shadow table whenever a row is deleted. Your download_delete_cursor can then select from this shadow table. As with logical deletes, you can delete the row from the shadow table once all remote databases have downloaded the corresponding data.

The MobiLink server deletes rows in the remote database by selecting primary key values from the consolidated database and passing those values to the remote database. If the values match those of a primary key in the remote database, then that row is deleted.

  • Each download_delete_cursor script that you want to download deletes must contain a SELECT statement or a call to a stored procedure that returns a result set. The MobiLink server uses this statement to define a cursor in the consolidated database.

  • If you always want a download_delete_cursor to select no rows, define the script as --{ml_ignore}.

  • This statement must select all the columns that correspond to the primary key columns in the 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 values must be selected in the same order as the corresponding columns are defined in the remote database. That order is the order of the columns in the CREATE TABLE statement used to make the table, not the order they appear in the statement that defines the primary key.

  • If you delete a parent record at the remote database via a download_delete_cursor, the child records are automatically deleted as well. This is not applicable to Blackberry devices.

    For more information about deleting child records, see Referential integrity and synchronization.

 Deleting all the rows in a table
 See also