ibec_CompareTables

Compares the data of specified tables and creates a script of all discrepancies.

Syntax

 function ibec_CompareTables(MasterDB : variant; SubscriberDB : variant;
                             MasterTable : string; SubscriberTable :string;
                             ScriptFile : string; Options : string;
                             CallbackProc : variant) : variant;

Description

This function compares the data of two tables and creates a discrepancy script. Both tables must have a primary key.

It is possible to include millseconds into time/timestamp values when comparing table data. Use the IncludeMilliseconds or IncludeMsecs option for this.

If the WHERE clause is specified it will be applied to all tables listed in the list of tables to compare.

Since IBExpert version 2020.09.13 a lot of additional statistical/progress information is passed to a callback block. See the IBEBlock page in the Table Data Comparer form for a full list of available data.

Parameters

MasterDBA handle to the reference database, maybe 0 or NULL if the current connection is used as a reference connection.
SubscriberDBA handle to the comparative database, maybe 0 or NULL if the current connection is used as a comparative connection.
MasterTable, SubscriberTableNames of the reference and comparative tables.
ScriptFileName of the script file which will contain the discrepancy script.
OptionsList of options, delimited with a semicolon; possible options are:
NoConnectIf this option is specified in the option string the result script will not contain CONNECT statement and corresponding SET NAMES, SET SQL DIALECT and SET CLIENTLIB directives.
OmitDeletesMissing records will not be checked by the data comparison. You can also use ProcessDeletes=0.
OmitInsertsNew records will not be checked by the data comparison. You can also use ProcessInserts=0.
OmitUpdatesModified records will not be checked by the data comparison. You can also use ProcessDeletes=0.
UpdateAllColumnsIf this option is specified UPDATE statements will include non-modifed columns too.
AppendModeIf this option is specified and the file ScriptFile already exists the resulting script will be appended to the ScriptFile. Otherwise a new file will be created.
UseHashFunc[tion]This option allows the comparison of blobs using the HASH function. This option will be ignored if one of the servers - source or comparative - doesn't support the HASH function.
UseBlocksThis option forces the packing of INSERT/UPDATE/DELETE statements into Firebird blocks (EXECUTE BLOCK) if possible.
UseUniquesThis option has been implemented to allow the comparison of tables which have a UNIQUE constraint instead of a PRIMARY KEY.
BlobsAsHexIf this option is enabled the values of blob fields will be written directly into the result script in hexadecimal notation, i.e. UPDATE MYTABLE SET BLOB_FIELD = x'4E657276656E';. Hexadecimal notation for binary values is available in Firebird 2.5 and Firebird 3. The length of the binary value which may be encoded in hexadecimal notation is limited to 32765 bytes in Firebird 2.5 and to 65535 bytes in Firebird 3. If the blob value exceeds the limit it will be written in the lob-file as is.
IncludeOldValuesIf this option is enabled, the old values of fields (for UPDATE statements) will be included into the result script as comments. See Table Data Comparer.
IgnoreSyncIf the Ignore synchronization errors option is ON all INSERT/UPDATE/DELETE errors during online synchronization are ignored, allowing an attempt to execute all statements in the synchronization queue. See Table Data Comparer.
QueueLimitA positive value means limitation by number of statements. A negative value means limitation by memory size (in megabytes). Default value is 0 - no limits.
Examples:
QueueLimit=100 - number of statements in the queue must not exceed 100.
QueueLimit=-5 - the queue must not occupy more than 5 megabytes.
ReturnBy default, this function returns null, but now it is possible to force it to return the total number of different records: specify Return=DiffCount in the option string.
CallbackProcA callback IBEBlock which will be executed for each record processed whilst comparing data. The callback IBEBlock must have at least one input parameter, which will be used to pass a number of processed records within it.
whereThe WHERE clause can be used to restrict the record sets which will be compared. Please refer to the examples below.
CommitBlock or CommitBlocksThis option performs a COMMIT after each Firebird block.
CommitAfter=<number>This specifies the number of statements between COMMITs.
SyncOnlineThis performs online synchronization. This allows you to update the target database simultaneously with the comparison process. Online synchronization is performed in a separate thread.

It is possible to compare more than one table in a single operation. Simply specify the list of necessary tables, delimited with a comma or semicolon, as MasterTable and SubscriberTable. For example:

 ibec_CompareTables(DB1, DB2,'TABLE1, TABLE2, "Table3"',
                      'TABLE1, TABLE2, "Table3"',
                      'D:\Diff.sql', 'UpdateOrInsert', cbb);'

The UpdateOrInsert option (and UseUpdateOrInsert) is now also valid. This allows you to generate UPDATE OR INSERT statements instead of UPDATE/INSERT for Firebird 2.1 databases (see example above).

Example of usage

  execute ibeblock
 returns (
   TotalTime double precision = 0 comment 'Time spent (seconds)')
 as
 declare ibeblock cbb (LogData variant, StatData variant)
  returns (Threshold integer = 0)
  as
  begin
    ibec_progress('Records processed: ' || LogData[0]);
    Threshold = 50;
  end;
 begin
   create connection MasterDB dbname 'localhost:c:\MasterDB.fdb'
   password 'masterkey' user 'SYSDBA'
   clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

   create connection SubscriberDB dbname 'localhost:c:\SubscriberDB.fdb'
   password 'masterkey' user 'SYSDBA'
   sql_dialect 3
   clientlib 'C:\Program Files\Firebird\bin\fbclient.dll';

   StartTime = ibec_gettickcount();
   ibec_CompareTables(MasterDB, SubscriberDB, 'IBE$$TEST_DATA', 'IBE$$TEST_DATA',  
   'E:\CompRes.sql','OmitUpdates', cbb); 
   ibec_CompareTables(MasterDB, SubscriberDB, 'IBE$$TEST_DATA', 'IBE$$TEST_DATA',  
   'E:\CompRes.sql','AppendMode; OmitDeletes; OmitInserts; UpdateAllColumns', cbb);

   close connection MasterDB;
   close connection SubscriberDB;

   EndTime = ibec_gettickcount();
   TotalTime =  (EndTime - StartTime) / 1000;
   suspend;
 end

Examples with the where clause

 ibec_CompareTables(MasterDB, SubscriberDB, 'MYTABLE', 'MYTABLE', 'E:\CompRes.sql',
   'OmitUpdates; Where="WHERE RECORD_DATE  > '12-NOV-2005'"', cbb);

It is also possible to omit the WHERE keyword:

 Where="RECORD_DATE > '12-NOV-2005'" 

IBExpert will add it automatically.

You can also use the WhereClause as an optional name:

 WhereClause="RECORD_DATE > '12-NOV-2005'" 

See also:
Table Data Comparer

back to top of page
<< ibec_RecompileFunction | IBEBlock | ibec_CompareMetadata >>