REPLICATE

The REPLICATE command lets you replicate the data of a table of database A into that of a table of database B. By convention, the database where the command is executed is called the "local database" and the database from which the data are replicated is called the "remote database."

This command can only be used in the framework of a database replication system. In order for the system to work, replication must have been enabled on the local database and the remote database side and each table implicated must have a primary key. For more information about this system, please refer to the Replication via SQL section. 

Note: If you would like to implement a complete synchronization system, please refer to the description of the SYNCHRONIZE command.

Pass a list of fields (virtual or standard) separated by commas in replicated_list. The fields must belong to the table_reference table of the remote database.
The FROM clause must be followed by an argument of the table_reference type which can be used to designate the table of the remote database from which to replicate the data of the replicated_list fields.

Note: The virtual fields of the remote table can only be stored in the arrays of the local database.

The optional WHERE clause can be used to apply a preliminary filter to the records of the table in the remote database so that only those records that satisfy the search_condition will be taken into account by the command.

4D then recovers the values of the replicated_list fields for all the records designated by the FOR REMOTE STAMP clause. The value passed in this clause can be either:

• a value of the type longint > 0: In this case, records where the value of __ROW_STAMP is greater than or equal to this value are recovered.
• 0: In this case, all the records where the value of __ROW_STAMP is different from 0 are recovered. Note that any records that existed before the enabling of replication will therefore not be taken into account (the value of their __ROW_STAMP = 0).
• -1: In this case, all the records of the remote table are recovered; in other words, all the records where the value of __ROW_STAMP >= 0. Unlike the previous case, all the records of the table, including any that existed before replication was enabled, will be taken into account.
• -2: In this case, all the records deleted from the remote table (after enabling of replication) are recovered; in other words, all the records where the value of __ROW_ACTION = 2.

Finally, you can apply the optional OFFSET and/or LIMIT clauses to the selection obtained:

• When it is passed, the OFFSET clause is used to ignore the first X records of the selection (where X is the value passed to the clause).
• When it is passed, the LIMIT clause is used to restrict the result selection to the first Y records (where Y is the value passed to the clause). If the OFFSET clause is also passed, the LIMIT clause is applied to the selection obtained after the execution of OFFSET.

Once both clauses have been applied, the resulting selection is sent to the local database.

The values recovered are directly written into the target_list of the local database or in the standard fields specified by sql_name of the table_reference table of the local database. The target_list argument can contain either a list of standard fields or a list of arrays of the same type as the remote fields (but not a combination of both). If the destination of the command is a list of fields, the target records will be automatically created, modified or deleted according to the action stored in the virtual __ROW_ACTION field. 

You resolve conflicts for replicated records that already exist in the target database (identical primary keys) using priority clauses (REMOTE OVER LOCAL or LOCAL OVER REMOTE):   

• If you pass the REMOTE OVER LOCAL option or omit the priority clause, all the source records (remote database) designated by the FOR REMOTE STAMP clause replace the target records (local database) if they already exist -- regardless of whether they were modified or not, on either side. In this case, it is pointless to pass a LOCAL STAMP clause because it is ignored.
• If you pass the LOCAL OVER REMOTE option, the command takes the LOCAL STAMP into account. In this case, target records (local database) whose stamp values are less than or equal to the one that is passed in LOCAL STAMP are not replaced by the source records (remote database). For example, if you pass 100 in LOCAL STAMP, all the records of the local database whose stamp is <=100 are not replaced by the equivalent records of the remote database. This lets you keep modified data locally and reduce the selection of records to be replicated in the local table.
• If you pass the LATEST REMOTE STAMP and/or LATEST LOCAL STAMP clauses, 4D returns the values of the last stamps of the remote and local tables in the corresponding 4d_language_reference variables. This information can be useful if you want to automate the management of the synchronization procedure. These values correspond to the value of the stamps just after the replication operation was completed: if you use them in a subsequent REPLICATE or SYNCHRONIZE statement, you do not need to increment them because they were automatically incremented before being returned by the REPLICATE command.

If the replication operation is carried out correctly, the OK system variable is set to 1. You can check this value from a 4D method. 

If errors occur during the replication operation, the operation is stopped at the first error that occurs. The last source variable (if it has been specified) is valorized with the stamp of the record in which the error occurred. The OK system variable is set to 0. The error generated can be intercepted by an error-handling method installed by the ON ERR CALL command. 

Note: Operations carried out by the REPLICATE command do not take data integrity constraints into account. This means, for instance, that the rules governing foreign keys, uniqueness, and so on, are not checked. If the data received could undermine data integrity, you must check the data after the replication operation is finished. The simplest way is to lock, via the 4D or SQL language, the records that have to be modified.