<< ibec_RecompileProcedure | IBEBlock | ibec_CompareMetadata >>

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.

Since IBExpert version 2014.12.17 if the WHERE clause is specified it will be applied to all tables listed in the list of tables to compare.
The UseHashFunc[tion] and UseBlocks options were implemented in IBExpert version 2015.03.14.

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:
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. New in IBExpert version 2015.03.14.
UseBlocksThis option forces the packing of INSERT/UPDATE/DELETE statements into Firebird blocks (EXECUTE BLOCK) if possible. New in IBExpert version 2015.03.14.
BlobsAsHexNew to IBExpert version 2017.04.24: if 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.
IncludeOldValuesNew to IBExpert version 2017.04.24: If this option is enabled, the old values of fields (for UPDATE statements) will be included into the result script as comments. Siehe Table Data Comparer.
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 CommitBlocksNew to IBExpert version 2016.03.15. Performs a COMMIT after each Firebird block.
CommitAfter=<number>New to IBExpert version 2016.03.15. Specifies the number of statements between COMMITs.
SyncOnlineNew to IBExpert version 2016.03.15. 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
 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';

   cbb = 'execute ibeblock (
       RecsProcessed variant)
     as
     begin
       if (ibec_mod(RecsProcessed, 100) = 0) then
       ibec_progress(Records compared:  || RecsProcessed);
     end';

 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_RecompileProcedure | IBEBlock | ibec_CompareMetadata >>