A data script that provides an event that the MobiLink server uses during processing of the upload to handle rows updated at the remote database.
In SQL scripts, you can specify event parameters by name or with a question mark. Using question marks has been deprecated and it is recommended that you use named parameters. 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.
Parameter | Description | Order (deprecated for SQL) |
---|---|---|
r. column-1 | Required. The first non-primary key column value from the new (post-image) column value, referenced by column name or column number. | 1 |
... | ... | ... |
r. column-M | Required. The last non-primary key column value from the new (post-image) column value, referenced by column name or column number. | M |
r. pk-column-1 | Required. The first primary key column value from the new (post-image) column value, referenced by column name or column number. | M + 1 |
... | ... | ... |
r. pk-column-N | Required. The last primary key column value from the new (post-image) column value, referenced by column name or column number. | M + N |
o. column-N | Optional. The first non-primary key column value from the old (pre-image) column value, referenced by column name or column number. | M + N + 1 |
... | ... | ... |
o. column-M | Optional. The last non-primary key column value from the old (pre-image) column value, referenced by column name or column number. | M + N + M |
None.
The WHERE clause must include all the primary key columns being synchronized. The SET clause must contain all the non-primary key columns being synchronized.
You can use named parameters in any order. The same named parameter can be used as many times as you want in the same script. You may only specify a subset of the columns in a script with named parameters.
For example, the upload_script for the table MyTable can be written as:
UPDATE MyTable SET column_2 = { ml r.column_2 }, column_1 = { ml r.column_1 }, ..., column_M = { ml r.column_M } WHERE pk_column_1 = { ml r.pk_column_1 } AND ... AND pk_column_N = { ml r.pk_column_N } |
You can have one upload_update script for each table in the remote database.
This script must be implemented in SQL. For Java or .NET processing of rows, see Direct row handling.
To use the upload_update script to detect conflicts, include all non-primary key columns in the WHERE clause:
UPDATE table-name SET col1 = {ml r.col1}, col2 = {ml r. col2 } ... WHERE pk1 = {ml r.pk1} AND pk2 = {ml r.pk2} ... AND col1 = {ml o.col1} AND col2 ={ml o.col2} ...
In this statement, col1 and col2 are the non-primary key columns, while pk1 and pk2 are primary key columns. The values passed to the second set of non-primary key columns are the pre-image of the updated row. The WHERE clause compares old values uploaded from the remote to current values in the consolidated database. If the values do not match, the update is ignored, preserving the values already on the consolidated database.
This script must be implemented in SQL. For Java or .NET processing of rows, see Direct row handling.
This example handles updates made to the Customer table in the remote database. The script updates the values in a table named Customer in the consolidated database.
CALL ml_add_table_script( 'ver1', 'table1', 'upload_update', 'UPDATE Customer SET name = {ml r.name}, rep_id = {ml r.rep_id} WHERE cust_id = {ml o.cust_id}') |
This next example performs a similar update, but uses the old (pre-image) values to ensure the update only happens if there is no conflict. If there is a conflict, the update is ignored in this "first in wins" conflict resolution policy.
CALL ml_add_table_script( 'ver1', 'table1', 'upload_update', 'UPDATE Customer SET name = {ml r.name}, rep_id = {ml r.rep_id} WHERE cust_id = {ml o.cust_id}') AND name = {ml o.name} AND rep_id = {ml o.rep_id} |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |