KS DB Merge Tools for Cross-DBMS supports its own scripting language that can be used from the command line and has a lot of features provided by the Standard version GUI. Command line tool is available for Windows and Linux operating systems. Using on Windows requires a Standard license, and a Linux version is free to use, except for automated use by non-individuals.
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 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.
On Windows command line and Script Editor applications are installed to the application folder which is specified when you run .msi application installation package.
On Linux you need to download .tar.gz file, extract to some folder and allow execution. Here is the example of these steps for ~/bin/ksdbmergetools/for-cross-dbms installation folder:
mkdir -p ~/bin/ksdbmergetools/for-cross-dbms wget -qO- https://ksdbmerge.tools/xdbms/XdbmsMerge_Linux_x64_1.10.0.tar.gz | tar xvz -C ~/bin/ksdbmergetools/for-cross-dbms chmod u+x ~/bin/ksdbmergetools/for-cross-dbms/XdbmsMergeCmd
Application supports both /windows and --linux styles to define command line parameters. Parameters listed in the tables below are case-insensitive.
| Parameter | Description |
|---|---|
|
/? --? /help --help |
Show help, it is also shown if no or invalid parameters specified |
|
/checkonly --checkonly -c |
Validate script syntax without execution and print found errors |
|
/forcescriptrun --forcescriptrun -f |
Force execution of data or objects merge/delete functions, if they were used without specifying fileName parameter to save generated script. If fileName is missing and this parameter is not specified, then script execution is stopped with exit code 10 ('Deny script run' error) |
|
/accepteula --accepteula |
When running on Linux, suppress request to confirm the End-User License Agreement, can be applied only if you have read and accepted terms from file EULA.txt, located in the application folder. When running on Windows, this parameter is ignored because EULA is accepted during installation. |
Here are the most common rules of this scripting language:
Set $selObjects = | Name | | dbo.vContactInfo | | dbo.vSalesPerson | ;
Here are some application features which are currently can be done only using GUI but not supported by scripting for now:
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.
Generate a schema/objects diff summary report, similar to the Export in the Home tab.
| Parameter | Type | Optional | Default value | Description |
|---|---|---|---|---|
| changeType | Custom | No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Add text to the log
| Parameter | Type | Optional | Default value | Description |
|---|---|---|---|---|
| text | String | No | Text of log entry |
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 |
| logConnectionString | Boolean | Yes | False | During database open, add to the log database connection string (as is, including passwords if any; currently applicable only for SQL Server and Oracle connections) |
| 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 |
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 |
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-11-25