(Please Note: the term "replication" is used here to refer to the synchronization and replication feature in 4D v12 and not specifically the act of using the command REPLICATE)

There are a few requirements for using the replication feature. Replication is a per-table feature, so these requirements apply to each table to be replicated.

Connection Type

Replication requires a “direct” SQL connection to 4D Server. A direct SQL connection does not require any ODBC driver. This is achieved by using the SQL LOGIN command with either the “4D:” or “IP:” syntax for the connection string.

Furthermore the connection must be made such that Begin SQL/End SQL blocks are directed towards the external server. This is accomplished by passing a * parameter.

For example:

SQL LOGIN("4D:Accounts_DB";"John";"azerty";*)

This opens a direct SQL connection to the database named “Accounts_DB” and ensures all Begin SQL/End SQL requests are sent to that server.

More information can be found in the SQL Reference:

https://doc.4d.com/4Dv12.2/help/command/en/page817.html

Language Access

Replication requires the use of 4D SQL code; that is to say replication requires the use of SQL commands placed in Begin SQL/End SQL blocks. It is not possible to use the SQL theme 4D language commands like SQL EXECUTE, SQL LOAD RECORD, etc.

This does not mean the replication SQL code must all be literal text. In order to use dynamic SQL statements with Begin SQL/End SQL the EXECUTE IMMEDIATE command can be used. In this way the SQL statement can be composed programmatically on-the-fly.

Primary Key

Any table to be replicated must have a “primary key”. This term is simply the formalized definition, from SQL, for a unique per-record identifier.

Any unique field in an existing 4D table can serve as a primary key. However it can be useful to add a new field to existing tables, or alter the definition of the existing field, rather than use the field as is. It is specifically useful to switch to UUID's for primary keys.

To create a primary key right-click on the field in the Structure editor and select “Create primary key”.



Alternatively primary keys can be created via SQL using the ALTER TABLE command, for example:

ALTER TABLE Groups ADD PRIMARY KEY (Group_ID);

Note this is not creating a field; it is only modifying constraints on an existing field.

Enable Replication

Once a primary key has been defined, replication must be enabled. This can be done using the Table inspector:



Note that this check box will not be enabled if the table does not have a primary key.

Replication can also be enabled using SQL:

ALTER TABLE Groups ENABLE REPLICATE;

Touch the Data (optional)

This last step is somewhat subtle but matches the existing experience in 4D.

Enabling replication for a table does not necessarily “do” anything. Unless the data is changed, nothing will be sent as part of a replication or synchronization. This is much like changing the type of a field in 4D; changing the field type does nothing to the existing data. Each record must be loaded and resaved in order to cause 4D to convert the data to the new type. Similarly with replication each record must be loaded and saved in order to generate the necessary information for replication.

This act of loading and re-saving each record is referred to as “touching” the data. One easy way to accomplish this is to apply a SQL statement, like the example below, to any text field:

UPDATE Groups SET Group_ID = CONCAT(Group_ID,'');

Of course the developer may not want to send all of the data as part of the first replication, so this step can be considered optional.