Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » MobiLink - Server Administration » Writing Synchronization Scripts » Writing scripts to download rows

Writing download_cursor scripts Next Page

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 from which you want to delete rows during synchronization.

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:

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.

While each download_delete_cursor script must select all the column values present in the primary key of the corresponding remote table, it may also select all the other columns. This feature is present only for compatibility with older clients. Selecting the additional columns is less efficient, as the database engine must retrieve more data. Unless the client is of an old design, the MobiLink server discards the extra values immediately. The extra values are downloaded only to older clients.

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 UDB and Oracle consolidated databases, you must specify a dummy table to select NULL. For IBM DB2 UDB 7.1, you can use the following syntax:

SELECT NULL, NULL 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 employees who are both in the consolidated and remote databases at the time the script is executed.

SELECT emp_id
FROM employee

The download_delete_cursor accepts the parameters last_download and ml_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}
Note:

For some consolidated databases, you may need to cast to the appropriate data type. See CAST function [Data type conversion].

These examples could be inefficient in an organization with many employees. You can make the delete process more efficient by selecting only rows that could be present in the remote database. For example, you could limit the number of rows by selecting only those people who have recently been assigned a new manager. Another strategy is to allow the client application to delete the rows itself. This method is possible only when a rule identifies the unneeded rows. For example, rows might contain a timestamp that indicates an expiry date. Before you delete the rows, use the STOP SYNCHRONIZATION DELETE statement to stop these deletes being uploaded during the next synchronization. Be sure to execute START SYNCHRONIZATION DELETE immediately afterwards if you want other deletes to be synchronized in the normal fashion.

See also

You can use the referential integrity checking built into all MobiLink clients to delete rows in an efficient manner. See Referential integrity and synchronization.

For more information about using download_delete_cursor scripts, see: