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 are going to use parameter 2). If you use named parameters, you can specify any subset of the parameters in any order.

None.

The WHERE clause must include all the primary key columns being synchronized and it can optionally include the non-primary key columns. 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_update 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 database 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.

• Data scripts
• Script parameters
• Script additions and deletions
• Conflict detection with upload_update scripts
• Conflict resolution with upload_update scripts
• upload_delete table event
• upload_fetch table event
• upload_insert table event

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}')