AdeptSQL DataDiff Automation

Starting with version 1.50, AdeptSQL Diff provides simple COM Automation interfaces for both schema and data comparison. The DataDiff COM object described below contains all methods and properties necessary to query and manipulate the compared data.

Note that the DataDiff object is not a stand-alone 'creatable' object. It is returned by CompareData method of the main schema comparison interface described in the topic on Diff automation. The current architecture of AdeptSQL Diff requires that you always do full schema comparison first, and only then you can compare data in a specific table. There is currently no way to circumvent the schema processing and create a data comparison object directly.

Methods of the DataDiff object are designed to be very similar and in fact some of them are identical to the methods of the main schema comparison object. The differences derive from the fact that the compared data is represented as a flat grid rather than a hierarchical tree and the generated SQL will consist of INSERT/DELETE/UPDATE statements instead of CREATE/DROP/ALTER ones.

The primary IDispatch interface of the DataDiff object is:

Interface name	IDataComparison
IID	{317750DE-529B-49CF-BF01-682AE1267E87}

Since the DataDiff object is not creatable, there is no class ID or name for it.

DataDiff Methods

function Ready: AppStates;

This is the function you must call after the object is first returned from CompareData call. Poll the function in a loop (with a delay inside) until it returns apsReady ( = 4). The returned status codes are a subset of those returned by the similar function of the main Diff object:

	Constant name	Indicates that...
3	apsWorking	The DataDiff is still comparing the tables...
4	apsReady	Finally, everything is done and the program is awaiting your commands!

Selection methods

procedure SelectAll;

Makes sure all subsequent MakeSQL calls will work on the whole table. It does not in fact select each specific row in the data grid (as they would be selected when you press Ctrl-A on the grid), but rather selects their invisible common root node, or effectively unselects any specific row. It may sound weird, but it works, because for all scripting commands DataDiff considers "all rows selected" and "no rows selected" as exactly the same situation: it has to script the whole data grid.

function SelectFirstDifference: WordBool;

Selects the first non-identical (either left-only, right-only or changed) data row.

function SelectNextDifference(bAdditive: WordBool): WordBool;

Selects the next changed row (starting after the current selection). If there is no such object, the function returns FALSE and the selection remains unchanged. If the bAdditive parameter is TRUE, the previous row will be also left selected, which allows you to select multiple rows.

procedure SelectByIndex(Index: Integer; bAdditive: WordBool);

Select a row in the data grid by its index. A valid indexes must be in the range from 0 to RowCount-1. If the bAdditive parameter is TRUE, the previous row will be also left selected.

Row info functions

function GetDiffInfo: Variant;

Returns an array consisting of 6 integer values (counters): number of selected rows, number of identical rows, number of changed rows, number of left-only rows, number of right-only rows and the bit mask of changes existing within the selection. This function works exactly like GetDiffInfo of the schema comparison object, please look there for more details.

function DiffCount: Integer;
function SameCount: Integer;
function LeftCount: Integer;
function RightCount: Integer;

These functions call the internal equivalent of GetDiffInfo() and return one of the counters it calculates: the number of rows in the compared tables that were changed, or remained identical, or found only in the left-hand table, or only in the right-hand one, respectively. Use these functions only if you need just one of the counters. If you need them all, call GetDiffInfo() once and access the counters in the returned array.

function RowCount: Integer;

Returns the total number of rows in the data grid. This value will be equal to the sum of the four counters described above.

function GetRowIndex: Integer;

Returns the index of the currently selected row in the data grid. Indexes start from 0. The function will return -1 if none or more that 1 row is selected.

Data access

function IsDigest(sName: WideString; bRight: WordBool): WordBool;

Returns TRUE if the addressed column value was loaded into buffers as MD5 digest. This happens for BLOBs or string values exceeding the size threshold specified in DataDiff options. You can call GetColumnValue for a 'digested' column, but it would only return a 16-byte bytearray which your scripting engine may not even recognize as a valid value.

sName is a column name, bRight determines which buffer we are looking at. As always, the function addresses the selected record and raises exception if no records or more than one record is selected.

function GetColumnValue(sColName: WideString; bRight: Wordbool): Variant;

Returns value of the named column in the currently selected row. bRight parameter determines whether the value is taken from the "left-hand" or the "right-hand" buffer. The returned values will be NULL if the value is missing (e.g. you requesting a "right-hand" value from a "left-hand-only" row), but the function would raise an exception if you provide an invalid column name or if either none or more than one record are selected.

Scripting functions

function MakeSQL(SQLKind: SQLKinds; TargetDB: Integer): WideString;

Exactly like the MakeSQL method of the schema diff, but produces INSERT, DELETE or UPDATE statements instead of CREATE, DROP and ALTER. Works on all selected rows or, if none selected, on the whole data grid.

procedure SaveSQL(SQLKind: SQLKinds; TargetDB: Integer; const FileName: WideString);

Same as MakeSQL method described above, except that the resulting SQL is not returned as a string, but immediately saved to the specified text file (ANSI, not Unicode).