Database Comparer VCL version 7.1 overview

Database Comparer VCL compares and synchronizes databases structure (metadata) and table data for many popular databases.

Currently supported databases: Oracle, MSSQL, MySQL, InterBase (FireBird, Yaffil), Sybase, Postgre SQL.
This list is constantly updated and support for other databases will be added soon.

Most popular database engines such as ADO, BDE supported directly.
Also database specific libraries such as FireDAC, IBX, FIB, IBO, ZEOS, AnyDAC etc supported as well.

The library includes more than 25 components for Database development and its code is constantly being refined and improved.

Product Description

What's new

  • RAD Studio 10.3 Rio support;
  • The CROSS APPLY statement for the SELECT command was added;
  • The Packages support was added to the Firebird 3.0 engine;
  • The parsing/building of CREATE FUNCTION statement was implemented;
  • The DATEADD, DATEDIFF, and OVERLAY functions parsing was introduced for Firebird 3.0;
  • The MS SQL Server CTE support was added;
  • The default values for the stored procedure parameters was implemented.

Implemented features

  • The TableDataComparer component for comparing and synchronizing table data;
  • The following SQL servers support are supported: Firebird, Interbase, MSSQL, Oracle, MySQL, Sybase, Postgre SQL;
  • The Database Comparer VCL can be independently compiled with any version of supported database engines;
  • The following database engines are supported: ADO, BDE, DBX, IBX, FIB, IBO, ZEOS, MyDAC, ODAC and IBDAC.
  • The SQLExec component can be used independently from the DBComparer component for executing any SQL scripts;
  • Sybase: Extract and compare PUBLICATIONs;
  • Sybase(ASA): Extract metadata from DB, compare DB-structures, update DB-structure, execute external script;
  • IB/FB: Option "Ignore IBE$... objects" added (ignore objects created by IBExpert);
  • Dependencies of grants with objects;
  • MSSQL2000 keywords. Quoting of identifiers performed only if necessary;
  • Support of CharSet-prefix for strings (e.g.: select _unicode_fss 'ABCD' from rdb$database);
  • Support of Interbase v 4.0 (extract metadata);
  • Domains CHECK altering via ALTER DOMAIN DROP CONSTRAINT / ADD CHECK (IB6.0 and up);
  • TableDataComparer: the "Update Immediately" option;
  • DBStructure: Extract metadata into a script;
  • TableDataComparer: comparing of tables with different structures;

Fixed Bugs

  • The database comparer cannot parse the "rows 1" statement in triggers - fixed;
  • Crash comparing two oracle 11g2 databases - fixed;
  • The Invalid typecast error occurs when extracting the Firebird 3.0 internal functions - fixed;
  • The SQL LIST ALL and DISTINCT statements cause the parsing error in Firebird 2.6 - fixed;
  • The valid Firebird 2.5 trigger with the script containing the CTE syntax is marked as invalid - fixed;
  • The extractor cuts the final ")" from the stored procedure code - fixed;
  • The updating of the Database Default Charset failed - fixed;
  • The problems with parsing of computed fields of NUMERIC (nn, xx) type were fixed;
  • The NOT NULL table columns are not changed to NULL for Firebird 3.0 - fixed;
  • The problems with parsing of stored procedure scripts containing the PERCENT and OPTION statements were fixed;
  • The comparer doesn't create the global temporary table, but it creates all the indices, primary keys, and foreign keys that belong to GTT - fixed.

TDBComparer is the key component which performs most of the work comparing Master and Target databases and produces final update SQL script. The final update SQL script will be applied to Target database and contains all necessary SQL operators and statements required to make Target database structure identical to Master database structure. You can execute final update SQL script using standard SQL console for your database or by using TxxxSQLExec component (where xxx will be different for different databases).

To extract database structure definitions (metadata) DBC VCL uses set of classes called metadata extractors. There are two different metadata extractors, one to extract metadata from SQL scripts (TxxxScriptExtract classes) and another to extract metadata using live database connection (TxxxDBExtract classes). Each database has own TxxxScriptExtract and TxxxDBExtract classes which knows how to work with this specific database. For example for MS SQL there is a TMSSQLScriptExtract and TMSSQLDBExtract clasess, TIBSctiptExtract and TIBDBExtract for Interbase and so on.

Metadata extractor saves all information about underlying database structure in to special storage class TDBStructure. This class is unified for all types of databases. Such approach makes possible to compare and synchronize databases structure of different types of databases. For example you can compare and synchronize Oracle and MS SQL databases, MS SQL and dBases databases and so on.

When comparing databases the TDBComparer component uses two TDBStructure storages to store Master and Targets database metadata. To customize process as much as possible component has set of properties and options to control various aspects of comparison process. In most cases you will call ExtractAndCompareDatabases method of TDBComparer component.

TDBCConnection class (with underlying TDBCConnectionXXX classes customized for different database engines) implements generic live database connection. Simply set ConnectionType property to desired database engine. Please use TDBCConnection if you want to work with different database types otherwise you can always use database specific connection class e.g. TDBCConnectionADO / TDBCConnectionIBX / TDBCConnectionBDE etc. TDBCConnectionXXX connection components are used by all DBC VCL which requires access to database. The TxxxSQLExec (executes script) and TxxxDBExtract (extracts metadata from database) components are probably most notable among them.

To compare and synchronize data between database tables we created TTableDataComparer component. It can synchronize records within the tables between two different databases. This component works with all databases supported by DBC VCL and capable of synchronizing of data between databases of different type (f.e. MS SQL and Oracle). TTableDataComparer uses TDBCConnectionXXX connection components to connect to databases.

TTableDataComparer creates resulting update script which contains INSERT, UPDATE and DELETE statements which will alter Target database table to make it identical to Master database tables. This resulting update script can be saved into the file and later executed against Target database using standard SQL console for given database or executed via TxxxSQLExec component (where xxx will be different for given databases).

TTableDataComparer has wide set of options, properties and events which make possible comparing of data between tables with different structures. E.g. if tables have different fields you can manually setup list fields used for comparison and fields mapping.

Any SQL-statement which returns set of records can be used for data comparison and synchronization. It can be complex SQL statement with multiple joins and sub-selects or view or just a recordset returned by stored procedure and so on. Use SelectSQL property or OnGetSelectSQL event to set desired SQL statements.

TTableDataComparer also gives you control over Insert/Update/Delete operations so e.g. you can create script which will add or update records without deleting old records.

Please visit our site Clever Components to learn more about Clever Internet Suite and our products. Feel free to join our Mail List Subscription and stay tuned.

Demo version information

Free and restrict functional beta version of Database Comparer VCL is available for evaluation and testing purposes only. Beta version can operate inside Delphi or C++Buidler IDE only, all other features are completely available.

For more details please visit our Customer Portal

Copyright © 1999 - 2019

Add Feedback