<< Database Comparer | IBExpert | Log Manager >>

Table Data Comparer

The Table Data Comparer can be found in the IBExpert Tools menu. It allows you to compare data of two tables in different databases and obtain a script detailing all discrepancies which includes corresponding INSERT, UPDATE and DELETE statements. This feature is unfortunately not included in the free IBExpert Personal Edition.

General

The General page displays the default file path and name for the resulting comparison script. This can of course be altered as wished.

The first step is to select the Master or Reference Database from the drop-down list of all registered databases. This is the reference database, to which the second database and its table(s) are to be compared. Then select the Target Database, i.e. the database whose table(s) need to be assessed and altered in order to conform with the reference database and table(s). The databases and tables must already exist.

Since IBExpert version 2015.09.28 a single transaction is now used when comparing data of several tables at the same time.

The Online synchronization feature was implemented in IBExpert version 2016.03.15. This allows you to update the target database simultaneously with the comparison process. Online synchronization is performed in a separate thread. If the result file name is specified, IBExpert also creates script file. If you only need online synchronization, leave the script file name editor empty.

Then select the tables to be compared. Tables with the same name in both databases are listed next to each other in the Tables to be compared list. If you wish to compare tables with different names, click the arrow to the right of the table field and select the desired table from the list of all tables in this database. Tables with different names must have the same structure. An error is raised if there is no primary key defined for the reference table.

Temporary tables (GTT) was removed from the tables selection list in IBExpert version 2016.11.11.

To select all tables use the right-click context-sensitive menu. As you will see in the illustration below, system tables are not selected when using this function.

Selected generators/sequences can also be synchronized as part of the table comparison. And the connection role can be used if specified.

If you wish you can save your current settings into a file and load previously saved settings from file using the relevant toolbar icons.

back to top of page

Options

The Options page allows:

  • Selection of INSERT, UPDATE or DELETE records.
  • Option to include milliseconds into time/timestamp values.
  • The options Use ALTER SEQUENCE instead of SET GENERATOR and Use UPDATE OR INSERT instead of INSERT/UPDATE are relevant for Firebird 2.0 and Firebird 2.1 respectively.

Introduced in IBExpert version 2015.03.14:

  • Compare blobs using the Firebird HASH function: this option will be ignored if one of the servers of the source or target database doesn't support the HASH function.
  • Pack INSERT/UPDATE/DELETE statements into Firebird blocks (EXECUTE BLOCK): this will not affect statements which contain parameters to load data from a blob file.
  • The Commit each block and Commit after specified number of records options were added in IBExpert version 2016.03.15.

#20170424Introduced in IBExpert version 2017.04.24:

  • Put BLOB values into script as hex strings: 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.

  • Include old values of fields (for UPDATE statements only) option: If this option is enabled, the old values of fields (for UPDATE statements) will be included into the result script as comments:
      UPDATE TDC_DATA
          SET INT_DATA = 57555,
              /* OLD.INT_DATA = 91186 */
              STR_DATA = 'some string value',
              /* OLD.STR_DATA = '' */
              STR_DATA2 = 'bla, bla',
              /* OLD.STR_DATA (trimmed) = 'comment...' */
              BLOB_DATA = :h15A73_4D
              /* OLD.BLOB_DATA = (BLOB) */
          WHERE (ID = 871); 

(trimmed) means that the old value contains a '*/' sequence which cannot be shown within a comment: IBExpert trims the rest of string and shows '...' instead.

To start the table comparison simply click the Compare button (green arrow) or [F9].

back to top of page

Log

The Table Data Comparer resolves dependencies between master and detail tables while creating the script.

The resulting log:

displays whether the database connections were successful, records searched, time taken and the number of discrepancies found. The resulting script file may then be loaded into the Script Executive if wished.

If the target database ODS version is equal to or greater than 11.1 string values with the character set OCTETS will be presented in the result script in hex representation (e.g. x'7D81AE05CB'). Since IBExpert version 2016.10.09 OCTETS character set strings (CHAR/VARCHAR) are now written into a LOB file if it is not possible to convert them into hexadecimal format.

IBEBlock

The IBEBlock page was added in IBExpert version 2014.09.15 and contains an automatically-composed IBEBlock for comparing table data.

See also:
Database Comparer
IBEScript.exe

back to top of page
<< Database Comparer | IBExpert | Log Manager >>