KS DB Merge Tools logo KS DB Merge Tools
Documentation
KS DB Merge Tools for Cross-DBMS logo for Cross-DBMS
 
KS DB Merge Tools for Oracle logo
KS DB Merge Tools for MySQL logo
MssqlMerge logo
KS DB Merge Tools for PostgreSQL logo
KS DB Merge Tools for SQLite logo
AccdbMerge logo

Scripting support

KS DB Merge Tools for Cross-DBMS Pro supports its own scripting language that can be used from the command line and has a lot of features provided by GUI. Here is the script example which copies all data from MySQL Sakila DB to the blank PostgreSQL Pagila database with the same structure:

LogTo(
    fileName: 'c:\temp\myLog.txt',
    ifExists: Append,
    addTimestamp: true,
    bufferSize: 100);

Set $db1 = DbOpen(dbms: MySQL, connectionString: 'Server=myhost;Port=3306;User Id=root;Password=mypsw;Database=sakila;');
Set $db2 = DbOpen(dbms: PostgreSQL, connectionString: 'Host=phhost;Username=pguser;Password=pgpsw;Database=pagila_blank;Port=5436;');

UseObjectNamesFrom(db: $db1);

/* DependencyLevel sorting: process tables without foreign keys first, than tables dependant on processed tables and so on:*/

ForEach $table in GetCommonTables(
    nameRegex: '',
    commonPrimaryKey: true,
    sortBy: DependencyLevel,
    sortOrder: Asc,
    sortByDependencyLevelDb: $db1)

    DataMerge(
        changeType: New,
        fromDb: $db1,
        toDb: $db2,
        fromTable: $table,
        identityInsert: True,
        withTransaction: True,
        onError: StopScript);

Save such kind of text to text file with .ksdms extension and use the following command to run this script:

"c:\Program Files (x86)\KS DB Merge Tools for Cross-DBMS\XdbmsMergeCmd.exe" myscript.ksdms

To verify script syntax without execution, you can use /checkOnly switch:

"c:\Program Files (x86)\KS DB Merge Tools for Cross-DBMS\XdbmsMergeCmd.exe" myscript.ksdms /checkOnly

It will parse the script, validate its syntax and report to console output if it has any errors. You can also use the Script Editor application to prepare scipts: it has syntax highlight, allows to check and run scipts and shows the exit code and console output of XdbmsMergeCmd.exe:

for SQL Server, Scripting Editor

For many typical diff & merge tasks script can be generated automatically using the 'Generate automation script stub' toolbar button. Just click that button and application will generate a draft script for the current tab and open Script Editor application with that script. You will have to do few adjustments (like specify log file path) and script is ready for further testing and execution.

Scripting language

Here are the most common rules of this scripting language:

  • Script consists of list of statements, each statement must be terminated with semicolon (';')
  • There are 3 types of statements:
    • Function call that does performs some action (BatchDataDiff(), BatchObjectsDiff(), DataDiff(), DataDelete(), DataMerge(), dbExec(), LoadDiffProfile(), LogTo(), LogPrint(), ObjectsDiff(), UseObjectNamesFrom())
    • Assign to a variable some tabular data: 
      Set $selObjects =
| Name             |
| dbo.vContactInfo |
| dbo.vSalesPerson |
;
      or result of function call (Set $db = DbOpen())
    • ForEach operator that iterates a variable thru result of function that returns a list (ForEach $table in GetCommonTables() /*Do some action - DataMerge() or DataDelete()*/;), note that semicolon is required only after ForEach body action, not after GetCommonTables()
  • Each function has number of parameters, some parameters are mandatory and some optional, but parameter names are mandatory (LogPrint(text:'OK') is good, but LogPrint('OK') is the error)
  • Each function parameter has specified type, it can be:
    • String - strings are single-quoted
    • Boolean - True or False
    • Number - positive integer number
    • Custom value from specified list, applicable only for given parameter (like Append value for ifExists parameter of LogTo())
    • Database variable - result or DbOpen() function call
    • String variable - result or GetCommonTables() function call
    • Table variable - result or table assignment
  • Script supports block comments in C-style (/*here is some comment*/)
  • Some functions and their arguments may have synonym names, for example DbOpen and OpenDatabase - it is the same function. That's because of historical reasons: originally it was introduced as OpenDatabase, but then was renamed to DbOpen to be more consistent with other method names. Original name is left for the backward compatibility.
  • Most of the functions are related to some concrete application tabs, for example BatchObjectsDiff allows to produce the same export as on the Home tab.

Limitations

Here are some application features which are currently can be done only using GUI but not supported by scripting for now:

  • Data - diff/merge/delete only selected rows
  • Data - merge only selected columns
  • Data - Id-Remap Merge
  • Data - transaction rollback for merge/delete on combination of enabled withTransaction and non-Proceed onError
  • Data - detailed diff of particular column value
  • Objects - detailed export of table definitions, functionality provided in GUI by Table structure diff tab
  • Objects - object definition text diff (functionality provided in GUI by Text diff tab)
  • Objects - find (functionality provided in GUI by Find tab)

Return codes

  • 0: Success
  • 1: License validation error, run GUI mode to check
  • 2: Denied for Free
  • 3: Invalid command line arguments
  • 4: Script parsing errors
  • 5: Log initialization failure
  • 6: Script interrupted by onError: StopScript parameter
  • 7: Script contains non-supported function, such as ObjectsMerge() or ObjectsDelete()
  • 255: Script interrupted by any other unknown error (DB connection failure, some unsupported database feature, etc)

BatchDataDiff

Generate a data diff summary report, similar to the Export in the Batch data diff tab.

Parameter Type Optional Default value Description
changeType Custom Yes All Specify which tables needs to be exported:
All - all tables, regardless of their Total/New/Changed count values
New - only tables having new rows
Changed - only tables having changed rows or those which have different Total value
NewAndChanged - union of New and Changed rows
calcType Custom Yes Total Specify which counts are calculated:
Total - only Total counts
TotalAndNew - only Total and New counts
All - calculate all counts
showTables Boolean Yes True (*) Include regular tables
showViews Boolean Yes False (*) Include views
showCustomMappings Boolean Yes False (*) Include custom mappings from the diff profile which was loaded previously with LoadDiffProfile(). Mapping should be marked as 'Show in Batch data diff'
showCustomQueries Boolean Yes False (*) Include custom queries from the diff profile which was loaded previously with LoadDiffProfile(). Queries should be marked as 'Show in Batch data diff'
dataSlice String Yes True Open data slice from from the diff profile which was loaded previously with LoadDiffProfile().
fileName String No Report file name, with .xlsx or .json file extension

(*) These defaults are valid for non-data-slice mode. If data slice is specified, then default is True if data slice has corresponding objects, similar to behavior of the Batch data diff tab.

BatchObjectsDiff

Generate a schema/objects diff summary report, similar to the Export in the Home tab.

Parameter Type Optional Default value Description
changeType Custom Yes All Specify which object types needs to be exported:
All - all object types, regardless of their Total/New/Changed count values
New - only object types having new objects
Changed - only object types having changed objects
NewAndChanged - union of New and Changed rows
Unchanged - only object types having unchanged objects
fileName String No Report file name, with .xlsx or .json file extension

DataDelete (synonym: DeleteData)

Delete data from the given table of the given database. You can specify second database to compare and delete only new/changed/both rows.

Parameter Type Optional Default value Description
changeType Custom No All Specify which rows needs to be deleted:
All - all table rows
New - only rows that are new comparing to another database
Changed - rows that exist in both DBs but have different values
NewAndChanged - union of New and Changed rows
fromDb Database variable No Database to be processed
table (synonym: fromTable) String or variable No Table needs to be processed, either string with table name or table variable returned from GetCommonTables() function call
comparedToDb Database variable Yes Database to compare and identify New/Changed/NewAndChanged rows
onError Custom Yes Proceed Error processing logic:
Proceed - skip failed row and proceed to process the rest
StopCommand - stop current DataDelete execution and don't process rest of rows
StopScript - stop the whole script execution
withTransaction Boolean Yes False Process all rows as a single transaction. If onError is not Proceed, then changes will be applied as all or nothing. Using transactions can also improve performance
fileName String Yes Save generated SQL script to the specified .sql file (instead of execution)
ifExists Custom Yes Append Behavior if fileName already exists.
StopScript - stop script execution
Overwrite - overwrite existing file
Append - append new sql statements at the end of existing file

DataDiff

Generate a data diff report for the given table, view, custom mapping or custom query; similar to the Export in the Data diff or Query result diff tabs.

Parameter Type Optional Default value Description
tableType Custom Yes Table Table object type:
Table - regular table
View - view
CustomMapping - custom mapping from the diff profile which was loaded previously with LoadDiffProfile()
CustomQuery - custom query from the diff profile which was loaded previously with LoadDiffProfile()
table String or variable No Table that needs to be processed, either string with table name or table variable returned from GetCommonTables() function call
changeType Custom Yes All Specify which rows needs to be exported:
All - all table rows
New - only rows that are new comparing to another database
Changed - rows that exist in both DBs but have different values
NewAndChanged - union of New and Changed rows
Unchanged - rows that exist in both DBs and have no different values
limit Number Yes 0 Max number of rows to put into file, specify 0 to include all rows
fileName String No Report file name, with .xlsx or .json file extension

DataMerge (synonym: MergeData)

Merge data of the given table from one database to another

Parameter Type Optional Default value Description
changeType Custom Yes Specify which rows needs to be merged:
New - only rows existing in source and missing in target
Changed - rows that exist in both DBs but have different values
NewAndChanged - union of New and Changed rows
fromDb Database variable No Source database
table (synonym: fromTable) String or variable No Table needs to be processed, either string with table name or table variable returned from GetCommonTables() function call
toDb Database variable No Target database
identityInsert Boolean Yes True If target table ID column is identity/autoincrement:
True - import ID values from the source database,
False - ignore source ID values, generate new ID values for imported rows. Note that in this case processed rows will keep to be considered as New by application because they will have different IDs
onError Custom Yes Proceed Error processing logic:
Proceed - skip failed row and proceed to process the rest
StopCommand - stop current MergeData execution and don't process rest of rows
StopScript - stop the whole script execution
withTransaction Boolean Yes False Process all rows as a single transaction. If onError is not Proceed, then changes will be applied as all or nothing. Using transactions can also improve performance
fileName String Yes Save generated SQL script to the specified .sql file (instead of execution)
ifExists Custom Yes Append Behavior if fileName already exists.
StopScript - stop script execution
Overwrite - overwrite existing file
Append - append new sql statements at the end of existing file

DbExec

Run SQL statement on the specified database.

Parameter Type Optional Default value Description
db Database variable No Target database
sql String No SQL statement to run

DbOpen (synonym: OpenDatabase)

Specify database connection and return its reference as a variable. Connection is specified by combination of DBMS and connection string for Oracle, MySQL, SQL Server and PostgreSQL, or by combination of DBMS and database file name for Access and SQLite

Parameter Type Optional Default value Description
dbms Custom No DBMS:
Access
MySQL (this value also needs to be used for MariaDB)
SQLServer
SQLite
PostgreSQL
Oracle
connectionString String Yes Connection string text, mandatory for Oracle, MySQL, SQL Server and PostgreSQL
fileName String Yes Connection string text, mandatory for Access and SQLite

GetCommonTables

Returns a list of table names that exists in both databases. This list can be used only inside the ForEach statement, see example above.

Parameter Type Optional Default value Description
nameRegex String Yes Regex filter by table name
sortBy Custom Yes Name Specify by which table attribute we need to sort result:
Name - by table name
DelendencyLevel - by table dependency level. See Table DL for more information.
sortByDependencyLevelDb Database variable See description Each database has its own foreign keys and dependency level of the same tables can be different. If we do sorting by DependencyLevel - this parameter becomes mandatory. Otherwise it is optional
sortOrder Custom Yes Asc Specify sort order:
Asc - ascending
Desc - descending
commonPrimaryKey Boolean Yes If True - return only tables which have the same primary key definition in both databases, if False - only different. If not specified - return all tables

LoadDiffProfile

Load specified diff profile to process its items (custom mappings, queries and data slices). 'Load on start' diff profile option is not applied for XdbmsMergeCmd. Necessary diff profile needs to be loaded in the script using this LoadDiffProfile() function.

Parameter Type Optional Default value Description
fileName String No Diff profile file name

LogPrint

Add text to the log

Parameter Type Optional Default value Description
text String No Text of log entry

LogTo

Setup logging settings

Parameter Type Optional Default value Description
fileName String No Log file name
ifExists Custom Yes Append Behavior if fileName already exists.
StopScript - stop script execution
Overwrite - overwrite existing file
Append - append new log entries at the end of existing file
addTimestamp Boolean Yes True Specify timestamp of log entries
logDataResult Boolean Yes True Add an entry on each processed row like 'Merging [DatabaseLogID] = 1.. Done.'
logDataScript Boolean Yes False Add entry of each executed INSERT/UPDATE/DELETE statement
bufferSize Number Yes 0 Write log entries in batch mode, can improve performance, but some log entries can be lost in case of abnormal program termination

ObjectsDiff

Generate an objects/schema diff report for the given object types; similar to the Export in the Object list tab.

Parameter Type Optional Default value Description
objectType String No Object type, specified exactly the same as on the Home tab
changeType Custom Yes All Specify which objects needs to be exported:
All - all objects
New - only objects that are new comparing to another database
Changed - objects that exist in both DBs but have different definition
NewAndChanged - union of New and Changed objects
Unchanged - objects that exist in both DBs and have same definition
selectObjects Table variable Yes Single-column table with list of object names
fileName String No Report file name, with .xlsx or .json file extension

UseObjectNamesFrom

Compared DBMS may have different systems of naming of object names, for example SQL Server table dbo.MyUser can be matched with PostgreSQL table public.myuser. UseObjectNamesFrom allows to specify which object name will be used for export (for example if we call ObjectsDiff) and for filter in (when we specify selectObjects for ObejctsDiff).

Parameter Type Optional Default value Description
db Database variable No From which database we use object names

Last updated: 2024-10-21