Tech Tip: Programmatic Table Definition for Replication

PRODUCT: 4D | VERSION: 12 | PLATFORM: Mac & Win

Published On: October 5, 2010

As mentioned in Tech Tip How to Build the Table Definition for a Replication, the table defintion for Replication-related commands must enumerate all fields. It can be quite tedious to manually type each field name into the SQL statement, so this Tech Tip demonstrates a technique to programmatically generate a table definition.

// If another table needs to be sync'd, add the name to this array.
APPEND TO ARRAY($tablesToSync_at;"Groups")
APPEND TO ARRAY($tablesToSync_at;"Members")
APPEND TO ARRAY($tablesToSync_at;"People")
APPEND TO ARRAY($tablesToSync_at;"Tasks")
APPEND TO ARRAY($tablesToSync_at;"Meetings")

$numTables_l:=Size of array($tablesToSync_at)

For ($i;1;$numTables_l)

   $currentTable_t:=$tablesToSync_at{$i}

   // Build table definition for SYNCHRONIZE statement.
   $tableDef_t:=$currentTable_t+" ( "

   // All fields to be sync'd must be enumerated.
   Begin SQL
   SELECT COLUMN_NAME FROM _USER_COLUMNS
   WHERE ( TABLE_NAME = :$currentTable_t )
   /* SYNCHRONIZE doesn't really work with LOBs so don't sync these field types. */
   AND NOT ( DATA_TYPE = 18 )
   AND NOT ( DATA_TYPE = 12 )
   INTO :$fieldNames_at;
   End SQL

    // Append the fields on to the statement.
   $numFields_l:=Size of array($fieldNames_at)
   For ($j;1;$numFields_l)
      If ($j=$numFields_l)
         $tableDef_t:=$tableDef_t+$fieldNames_at{$j}+") "
      Else
         $tableDef_t:=$tableDef_t+$fieldNames_at{$j}+", "
      End if
   End for

    // Table definition is complete

End for

Once completed the table definition could be used in a SYNCHRONIZE statement, for example:

$statement_t:="SYNCHRONIZE LOCAL TABLE "+$tableDef_t
$statement_t:=$statement_t+" WITH "
$statement_t:=$statement_t+" REMOTE TABLE "+$tableDef_t
$statement_t:=$statement_t+" FOR REMOTE STAMP :remoteStamp_l,"
$statement_t:=$statement_t+" LOCAL STAMP :localStamp_l"
$statement_t:=$statement_t+" LOCAL OVER REMOTE"
$statement_t:=$statement_t+" LATEST REMOTE STAMP :latestRemoteStamp_l,"
$statement_t:=$statement_t+" LATEST LOCAL STAMP :latestLocalStamp_l;"

Begin SQL
EXECUTE IMMEDIATE :$statement_t;
End SQL