4D v16
SYNCHRONIZE
SYNCHRONIZE
|
SYNCHRONIZE |
||
The SYNCHRONIZE command lets you synchronize two tables located on two different 4D SQL servers. Any change made to one of the tables is also carried out in the other. The 4D SQL server that executes the command is called the local server and the other server is called the remote server.
The SYNCHRONIZE command is a combination of two internal calls to the REPLICATE command. The first call replicates the data from the remote server to the local server and the second carries out the opposite operation: replication of local server data to the remote server. The tables to be synchronized must therefore be configured for replication:
- They must have a primary key,
- The "Enable Replication" option must be checked in the Inspector window of each table.
For more information, please refer to the description of the REPLICATE command.
The SYNCHRONIZE command accepts four stamps as "parameters": two input stamps and two output stamps (last modification). The input stamps are used to indicate the moment of the last synchronization on each server. The output stamps return the value of the modification stamps on each server right after the last modification. Thanks to this principle, when the SYNCHRONIZE command is called regularly, it is possible to use the output stamps of the last synchronization as input stamps for the next one.
Note: Input and output stamps are expressed as number values and not as timestamps. For more information about these stamps, please refer to the description of the REPLICATE command.
In the event of an error, the output stamp of the server concerned contains the stamp of the record at the origin of the error. If the error stems from a cause other than the synchronization (network problems for example), the stamp will contain 0.
There are two different error codes, one to indicate a synchronization error on the local site and another for a synchronization error on the remote site.
When an error occurs, the state of the data will depend on that of the transaction on the local server. On the remove server, the synchronization is always carried out within a transaction, so the data cannot be altered by the operation. However, on the local server, the synchronization process is placed under the control of the developer. It will be carried out outside of any transaction if the Auto-commit Transactions preference is not selected, (otherwise, a transaction context is automatically created). The developer can decide to start a transaction and it is up to the developer to validate or cancel this transaction after data synchronization.
You can "force" the synchronization direction using the REMOTE OVER LOCAL and LOCAL OVER REMOTE clauses, depending on the characteristics of your application. For more information about the implementation mechanisms, please refer to the description of the REPLICATE command.
Note: Operations carried out by the SYNCHRONIZE 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 synchronization operation. The simplest way is to lock, via the 4D or SQL language, the records that must be modified.
In the 4d_language_ref variables of the LATEST REMOTE STAMP and LATEST LOCAL STAMP clauses, 4D returns the values of the last stamps of distant and local tables. This information lets you automate the handling of the synchronization procedure. They correspond to the value of the stamps just after the end of the replication operation: if you use them in a subsequent REPLICATE or SYNCHRONIZE statement, you do not need to increment them; they are incremented automatically before being returned by the REPLICATE command.
To understand the mechanisms involved in a synchronization operation, we are going to look at the different possibilities related to updating of an existing record in both of the synchronized databases.
The synchronization method takes the following form:
C_LONGINT(vRemoteStamp)
C_LONGINT(vLocalStamp)
C_LONGINT(vLatestRemoteStamp)
C_LONGINT(vLatestLocalStamp)
vRemoteStamp:=X... // see values in the array below
vLocalStamp:=X... // see values in the array below
vLatestRemoteStamp:=X... // value returned in a previous LATEST REMOTE STAMP
vLatestLocalStamp:=X... // value returned in a previous LATEST LOCAL STAMP
Begin SQL
SYNCHRONIZE
LOCAL MYTABLE (MyField)
WITH
REMOTE MYTABLE (MyField)
FOR REMOTE STAMP :vRemoteStamp,
LOCAL STAMP :vLocalStamp
LOCAL OVER REMOTE // or REMOTE OVER LOCAL, see in array below
LATEST REMOTE STAMP :vLatestRemoteStamp,
LATEST LOCAL STAMP :vLatestLocalStamp;
End SQLThe initial data is:
- The record stamp in the LOCAL database has a value of 30 and the one in the REMOTE database has a value of 4000
- The values of the MyField field are as follows:
LOCAL REMOTE Old value New value Old value New value AAA BBB AAA CCC - We use values returned by previous LATEST LOCAL STAMP and LATEST REMOTE STAMP clauses in order to synchronize only those values that were modified since the last synchronization.
Here are the synchronizations made by the SYNCHRONIZE command according to the values passed in the LOCAL STAMP and REMOTE STAMP parameters as well as the priority option used: ROL (for REMOTE OVER LOCAL) or LOR (for LOCAL OVER REMOTE):
| LOCAL STAMP | REMOTE STAMP | Priority | LOCAL after sync | REMOTE after sync | LOCAL - REMOTE Synchronization |
| 20 | 3000 | ROL | CCC | CCC | <----> |
| 20 | 3000 | LOR | BBB | BBB | <----> |
| 31 | 3000 | ROL | CCC | CCC | <-- |
| 31 | 3000 | LOR | CCC | CCC | <-- |
| 20 | 4001 | ROL | BBB | BBB | --> |
| 20 | 4001 | LOR | BBB | BBB | --> |
| 31 | 4001 | ROL | BBB | CCC | No synchronization |
| 31 | 4001 | LOR | BBB | CCC | No synchronization |
| 40 | 3000 | ROL | CCC | CCC | <-- |
| 40 | 3000 | LOR | CCC | CCC | <-- |
| 20 | 5000 | ROL | BBB | BBB | --> |
| 20 | 5000 | LOR | BBB | BBB | --> |
| 40 | 5000 | ROL | BBB | CCC | No synchronization |
| 40 | 5000 | LOR | BBB | CCC | No synchronization |
Product: 4D
Theme: SQL Commands
4D SQL Reference ( 4D v16)
