Writing download_delete

Writing 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 via a download_delete_cursor, the child records are automatically deleted as well.

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

Deleting all the rows in a table

When MobiLink detects a download_delete_cursor with a row that contains all nulls, it deletes all the data in the remote table. The number of nulls in the download_delete_cursor can be the number of primary key columns or the total number of columns in the table.

For example, the following download_delete_cursor SQL script deletes every row in a table in which there are two primary key columns. This example works for SQL Anywhere, Adaptive Server Enterprise, and Microsoft SQL Server databases.

SELECT NULL, NULL

In IBM DB2 LUW and Oracle consolidated databases, you must specify a dummy table to select null. For IBM DB2 LUW 9.5 and 9.7, you can use the following syntax:

SELECT CAST( NULL AS INTEGER ), CAST( NULL AS INTEGER ) FROM SYSIBM.SYSDUMMY1

For Oracle consolidated databases, you can use the following syntax:

SELECT NULL, NULL FROM DUAL

Examples

The following example is a download_delete_cursor script for a remote table that holds employee information. The MobiLink server uses this SQL statement to define the delete cursor. This script deletes information about all orders that are both in the consolidated and remote databases at the time the script is executed.

SELECT order_id
FROM ULorder

The download_delete_cursor accepts the parameters last_table_download and username. The following script shows how you can use each parameter to narrow your selection.

SELECT order_id
FROM ULOrder
WHERE last_modified >= {ml s.last_table_download}
 AND status = 'Approved'
 AND user_name = {ml s.username}

Another strategy is to allow the client application to delete the rows itself. This method is possible only when a rule identifies the unnecessary rows. For example, rows might contain a timestamp that indicates an expiry date. Before you delete the rows at the remote, use the STOP SYNCHRONIZATION DELETE statement to stop these deletes being uploaded during the next synchronization. Be sure to execute START SYNCHRONIZATION DELETE immediately afterward if you want other deletes to be synchronized in the normal fashion.

Notes

The download_delete_cursor script must contain primary key columns in the same order as they are defined in the remote database.
You can use the referential integrity checking built into all MobiLink clients to delete rows in an efficient manner by deleting only the parent rows. See Referential integrity and synchronization.

Writing download_delete_cursor scripts

Deleting all the rows in a table

Examples

Notes

See also