AdeptSQL Diff Automation interface

Starting from version 1.50, AdeptSQL Diff provides limited programmatic control by external applications or scripts via a simple ActiveX Automation interface. Read the technical information below only if you are familiar with COM and plan to invoke AdeptSQL Diff from a programming language supporting ActiveX Automation. The Diff itself can't host automation scripts (yet), so if you are using an embeddable scripting language such as VBScript or JScript, you will have to put your scripts into a DHTML page or use Windows Scripting Host (WSH) to run the scripts  from command line.  Using VB, C++ or Delphi you can call AdeptSQL Diff from your own applications.

Multiprocess / unattended use limitations: Please note that in Diff versions 1.xx, the Automation support was added as an afterthought, on top of the GUI and all existing functionality.  This means that whether you run the Diff interactively or via its Automation interfaces, it always shows up with all its GUI windows. Multiple instances of comparison objects would all refer to the same  instance of Diff.  When you run a comparison from your script, the Diff will show the progress dialog; when you navigate the schema tree programmatically, it will move selection in the schema view panel accordingly, etc. In future versions the internal architecture is likely to change to separate the comparison engine into a separate layer accessible either from GUI or programmatically, but meanwhile you should avoid using Diff automation in any multi-process or server-like way.  

Trial mode limitations: Note that although the automation does work with Diff running in trial mode, it will not be convenient to use. Every time the Diff is programmatically started, you will have to switch to it and manually close the registration dialog. And another dialog will show up every time you start data comparison. Therefore it is strongly recommended that you purchase a license before starting to use the automation interface.

The Comparison object.

There isn't much of a "document object model" here: all you work with is a single automation object representing the program. Another object for DataDiff comparison can be produced by CompareData call. The DataDiff object is described on a separate page.

Class name:	AdeptSQL_Diff.Comparison
Class GUID:	{DD76F532-F4EA-416A-B099-142B14F825AF}

The primary IDispatch interface of the class is:

Interface name:	IAdeptSQLDiff
IID:	{B0D5F6EA-47CE-4351-A5B3-AFD68221CBB4}

Comparison methods of IAdeptSQLDiff:

function Ready: AppStates;

This is the function you must call after creating the object and later after starting a comparison. Poll the function in a loop (with a delay inside, like sleep(100) in Windows API or DoEvents in VB) until it returns a proper status code: apsEmpty (=2) after the Diff has started and then apsReady (=4) after the comparison is complete.  See the sample code below for details. Here is the complete list of the returned status codes:

	Constant name	Indicates that...
0	apsStarting	The program is still initializing itself, don't bother it in this state
1	apsModal	The program has displayed some modal dialog, waiting for the user to close it. Nothing your script can do but wait.
2	apsEmpty	The Diff is up and running, there is no comparison yet. Now this is time to call Compare/CompareDoc and begin the real work!
3	apsWorking	The Diff is still comparing the databases, keep waiting...
4	apsReady	Finally, everything is done and the program is ready to process your commands!

procedure LoadConfig(const FileName: WideString);

[Diff 1.90 or later]: This method attempts to load the entire set of AdeptSQL Diff settings (comparison, scripting, visual and all others) from the specified .INI file.  This method is optional. If you never call it,  the Diff will continue to use the settings it loaded on startup from either the registry or the default configuration file ASQLDIFF.INI.  Note that these application-level settings do not intersect with the project-level ones, which are kept in a comparison document.  By calling LoadConfig() first and then proceeding with CompareDoc(), you can ensure that the comparison and scripting are done in the completely controlled environment.  This may be important, because most of the automation methods described below are affected by various settings and there is currently no way to change those settings through the automation interfaces.

To produce a customized configuration file, run the Diff interactively, specifying the "-wi filename.ini" command line parameter.  Configure any options as required and exit the program.  On exit, all modified options will be written to that configuration file.

procedure Compare(const ConnLeft: WideString; const ConnRight: WideString);

This method compares two databases specified by their ADO connection strings. Since all other methods deal with the results of the comparison, you have to call Compare and then poll Ready waiting for apsReady status before you do anything else. The program raises an exception if it can't connect to either of the databases.

The following example (in Delphi) shows you how to create an AdeptSQL Diff  interface object and start a comparison:

var
 Diff: OleVariant;
procedure TMyTestForm.btnCompareClick(Sender: TObject);
var
ws1, ws2: WideString;
n: Integer;
begin
Diff := CreateOleObject('AdeptSQL_Diff.Comparison');

 n := 30;
 while Diff.Ready <> apsEmpty do
begin
sleep(100); Dec(n);
if n = 0 then
raise Exception.Create('Timeout starting AdeptSQL Diff');
 end;

 ws1 := edConnStringLeft.Text;
 ws2 := edConnStringRight.Text;
 Diff.Compare(ws1, ws2);

 n := 500; // Comparison time can be much longer!
while Diff.Ready <> apsReady do
begin
sleep(100);
Dec(n);
if n = 0 then
raise Exception.Create('Timeout comparing databases');
 end;
end;

procedure CompareDoc(const FileName: WideString);

[Diff 1.90 or later]: This method runs the comparison using information from a previously saved comparison file.  The file determines both connection strings, DataDiff profiles for any previously compared tables and some other settings. Just as the Compare() method, CompareDoc() is asynchronous, so you must check the Ready status as shown above.

NOTE: There is currently no way to programmatically create (that is, configure and save) a comparison document using Diff's automation interface.  It is assumed that you first run the comparison manually, configure it as required, then save it into a file and later use that file to re-compare from script.  If you do need to generate a comparison file programmatically, you can do so in your own code, because comparisons (.ASQ files) are text files having rather simple INI-style format.

function CompareData(sKeys, sNormal, sIgnore, sFilter: WideString): DataDiff;

Invokes DataDiff to compare records of the selected table (matched pair of tables) and returns an object representing this data comparison. The schema comparison must be completed and the program be in apsReady state before data comparison can be attempted. The selection must be a single non-empty table (pair of matched tables), or the call would raise an exception. Call HasData() before to make sure the data comparison is possible.

When you start a data comparison programmatically, the column selection dialog is not displayed and all comparison parameters are taken from the parameters passed to CompareData:

• sKeys, sNormal, sIgnore are each a comma-separated list with the names of columns (a) to be used as the key, (b) to be compared as normal columns and (c) to be ignored, respectively. All these parameters are optional. Each column not mentioned in one of the lists will retain its default status as described here. The only situation when you must specify sKeys is when there is no PK or UNIQUE constraint in the table to determine the default key. 
• sFilter parameter is also optional. If specified, it must be a valid (for each of the tables in the matched pair) SQL condition for a WHERE clause or "*" (see below). It will be used to filter the records being compared. If a filter expression is not specified or is empty string, any filter stored in the comparison file will be applied (or the entire tables will be compared). 
  
  [Diff 1.90 or later]: If you need to specify different filter expressions for the two tables, separate them with "|" (for example: "Salary > 10000 | Salary > 10000 and Year = 2005").  You can use "*" to explicitly clear the filter, overriding any value from the loaded comparison document. The "*" works both as the whole sFilter value and with "|" (e.g. "Salary > 100 | *" - filtered records from the left-hand DB, all records from the right-hand one).

If the schema comparison is started with CompareDoc (and this particular pair of table has been compared before), the data comparison will use any filters, column settings and other details remembered from the last comparison. The above parameters will override these settings.

Note that, so far, the scripting modes, tolerances and other DataDiff comparison details are not accessible through the Automation. The only way to specify them is to put them into the project file.

The call is asynchronous: it returns a DataDiff object before the comparison is complete. Your script must poll the Ready() function of the newly created DataDiff object (not of the original schema comparison object!) until it returns apsReady and only then proceed exploring the data.

Selection methods.

All scripting and information functions described in the next sections work on one or more of selected objects. It is exactly as if you had selected the objects manually when running the program in interactive mode. In fact you can switch to the Diff window while it is being manipulated from a script and watch how the selection changes. The functions described below allow you to select the whole schema, or specific named items, or traverse the schema tree enumerating certain categories of objects. Please look at the sample scripts in SampleAutomation/ directory to see how this can be used to produce reports.

procedure SelectRoot;

Selects the root node of the compared schema (so that subsequent calls to MakeSQL/SaveSQL would script the whole schema). This method is equivalent to Select("",""), it was also known as SelectAll() (which is still supported but not recommended).

function Select(const Category: WideString; const Items: WideString): Integer;

Selects one, several or all schema objects in the specified category and returns the number of selected items. In the Category parameter you can specify "Tables", "Views", or a title of any other first-level collection node, exactly as it appears in the schema tree.

The Items parameter may be empty, which means selecting the category node itself, or it may contain one or more comma-separated names.  The names must be specified exactly as they are displayed in the schema tree, including any  square brackets "[]" and owner names. E.g. if you have configured the Diff to display table names with owner prefix, the owner must also be specified for each name in Items,  but if there is no owner prefixes in the displayed schema, there shouldn't be any in Items either.

The return value will be 0 if none of the specified item(s) are found in the schema, 1 if a single node has been selected (even if it is a category node). The result of Select may be >1 only when Items parameter contains a comma-separated list. If any (or even all) of the schema entities in the Items are not found, this will be reflected in the returned selection count.

Note that Select() can not be used to access summary collection nodes. For that, you must use a combination of SelectRoot() / SelectNext() / SelectChild() functions, as described below.

function SelectChild(const ChildName: WideString = ''): Boolean;

Selects a child node of the currently selected node. The optional ChildName parameter specifies the name of the child node (exactly as displayed in the schema tree). If ChildName is not specified or is an empty string, the first child node will be selected. The function returns FALSE if the selected node has no child nodes or if none of them match the specified name. Before calling this function you must make sure that exactly one node is selected. If no nodes or more than one node are selected, the function raises exception.

function SelectNext(bExtend: Boolean): Boolean;

Selects the next sibling node after the currently selected one. If bExtend=True, the previous node remains selected as well, which gives you one more way (besides Select()) to select multiple items. If bExtend=False, the function deselects the currently selected node (or the last of selected nodes, if several have been already selected). The function returns False if there is no more sibling nodes. Before calling this function, you must make sure at least one node is selected, otherwise the function raises exception. Having a multiple selection before SelectNext() is OK, the function will start at the last of the selected items.

function SelectParent(): Boolean;

Moves selection from the currently selected node(s) to its parent node. Returns False if there is no parent, which only happens when we are already at the root node. Before calling this function, you must make sure at least one node is selected, otherwise the function raises exception. Multiple selection is OK, since the selected nodes are always siblings and finding their common parent node is not a problem.

One example of using these schema-traversing functions is getting to the 'Summary' collections, e.g. when we intend to script all triggers:

diff.SelectRoot(); // we are at the 'Schema' node
diff.SelectNext(false); // we are at the 'Summary collections'
diff.SelectChild("Triggers");
s = diff.MakeSQL(sqlCREATE, 0);

function SelectFirstDifference(const Category: WideString): Boolean;

Selects the first changed node in the specified category (e.g. "Tables") or in the whole schema (if no category is specified). Returns TRUE if such a node is found.  The effect is similar to calling Select(Category, ""), followed by SelectNextDifference, except that it also sets the scope: subsequent calls to SelectNextDifference would only locate changes within the specified category.

function SelectNextDifference: Boolean;

Selects the next changed schema object (starting after the current selection). If there is no such object, the function returns FALSE and the selection remains unchanged. If the last call to SelectFirstDifference has specified a category (e.g. "Tables"), the search stops at the last changed item in this category, otherwise it traverses the whole schema.  SelectNextDifference (as well as SelectFirstDifference) always selects a particular schema object (e.g. a "Customers" table), not a category itself ("Tables") and not a subitem (e.g. not a particular changed column in "Customers").

Information functions

The following functions allow to obtain information about the currently selected item(s), including its type, name and comparison status.

function SelItemName: WideString;

Returns name of a selected item, as displayed in the schema view. Exactly one node must be selected, otherwise this method raises an exception. This method works on a node of any kind: root, category or schema item.

function SelItemType: WideString;

Returns a string describing what kind of node(s) is currently selected. At least one node must be selected, otherwise this method raises an exception. Multiple selection is acceptable, because only sibling items can be multi-selected and those are guaranteed to be of the same type. If the selected node is a schema entity (e.g. a specific table), the function will return the caption of its parent category (e.g. 'Tables'). If the node is a category, the function will return string 'Collection'. For the root node it will return 'Root'.

function GetDiffInfo: Variant;

This function returns an array containing 6 integer values - various difference counters for the currently selected item(s). If more than one item is selected, the counters for each of them add up. If a category node or the whole schema is selected, the counters represent the number of changes in all schema items under this category or across the whole schema.

Element	Description
a[0]	Selection count: the number of items actually selected. The same result can be obtained from SelCount() function described below. Note that (unlike the next 4 counters) for a category node or the root node this counter will be 1, not the number of subnodes. If you need the total number of items in the stats, just sum up the next 4 elements of this array.
a[1]	Unchanged count: the number of items which are identical between the two databases. Same as SameCount() function below.
a[2]	Changed count: the number of items which are present in each of the databases but have some differences. Same as DiffCount() function below.
a[3]	Left-only count: the number of items which are only present in the left-hand database. Same as LeftCount() function below.
a[4]	Right-only count: the number of items which are only present in the right-hand database. Same as RightCount() function below.
a[5]	Difference flags: a bit mask indicating that at least one schema item in the selection has a specified kind of difference. In other words, there is a bit for each of the 4 counters above and this bit is set if the counter is not zero. The bits are: dfSame = 1; dfChanged = 2; dfLeftOnly = 4; dfRightOnly = 8; The same bitmask is returned by DiffState() function described below.

JScript note: arrays in JScript are completely different and incompatible with the array variant type returned by GetDiffInfo. So if you need to call it in JScript, use the following code to convert the results into the format recognized by its scripting engine:

 var va = new VBArray(diff.GetDiffInfo());
 var a = va.toArray();
 var selCount = a[0];
	...
 

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

Each of these functions calls an internal version of GetDiffInfo and returns just one of the computed statistics. See description of GetDiffInfo for more information. Getting the integer counters returned from these functions might be more convenient than accessing the array returned by GetDiffInfo (especially in JScript), but if you need several of these counters it would be much more efficient to call GetDiffInfo once.

function SelCount: Integer;

This function returns the number of selected items, which is the same information GetDiffInfo returns in the 0-th element of the info array. But unlike the 5 counter functions described above, SelCount does not rely on GetDiffInfo to calculate its vaue (in fact, it is the other way around), so there is no overhead in calling SelCount directly.

function HasData: Bool;

Returns TRUE if data comparison can be successfully run on the currently selected schema item (see CompareData). This means that the current selection must be a single schema item, the item must represent a table (or a pair of matched tables) and this table must contain some data records. Returns FALSE if any other kind of schema item is selected (or several items, or none) or if the selected table is empty.

Scripting functions

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

Produces one of the 3 possible kinds of SQL:  CREATE, DROP or ALTER (SQLKind = 0, 1, or 2 accordingly) for all previously selected schema items.  The resulting SQL is returned as a Unicode string.  TargetDB determines which of the compared schemas is used to produce the SQL or which database the SQL should be applied to (except for CREATE scripts, which usually can't be applied to either DB). The following table explains the possible combinations:

	SQLKinds:	TargetDB = 0	TargetDB = 1
0	sqlCREATE	CREATEs are produced from the left-hand schema (and can be applied to an empty database, but probably not to either one of those compared)	CREATEs are produced from the right-hand schema (and can be applied to an empty database, but probably not to either one of those compared)
1	sqlDROP	DROPs are produced from the left-hand schema (and can be applied to the same database, if you need to delete the selected objects)	DROPs are produced from the right-hand schema (and can be applied to the same database)
2	sqlALTER	Update script is produced from the right-hand schema to be applied to the left-hand one	Update script is produced from the left-hand schema to be applied to the right-hand one

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 (as ANSI, not Unicode).

procedure SaveReport(const FileName: WideString);

Supposed to write a template-driven difference report into the specified HTML file. Not currently implemented, parameters are subject to change.