data-diff, compare-data
The data-diff command runs one or more database data comparison jobs configured previously. By default, the comparison result is displayed at the command line in XML format.
Running *.dbdif files with DiffDog Server is most convenient if DiffDog Server runs either on the same computer as DiffDog desktop, or on a Windows machine. If DiffDog Server runs on a different machine or operating system, the following limitations apply:
•If the comparison involves CSV files, running .dbdif files is supported only on Windows servers. For the comparison to be successful, all the CSV file paths that were valid on the desktop machine must be valid on the server machine.
•If database connections are involved, the server machine must be configured and capable to handle the database connection. Namely, the database drivers and any other prerequisites required by the connection must be present on the target operating system as well. For example, if the .dbdif file includes a connection that requires an ODBC driver from the database vendor, that driver must be installed on the server machine as well. Note that some database connection methods supported on Windows are not supported on Linux and macOS. For more information, see Supported Databases.
If you set up comparison jobs directly in DiffDog Server (as opposed to using *.dbdif files), you can run them regardless of the platform.
Syntax
diffdogserver data-diff [options] {job} ...
diffdogcmdlclient data-diff [options] {job} ...
Notes
•The job argument refers to a [datadiff] section defined in the client configuration file.
•The job argument can be specified more than once in order to run multiple database comparison jobs in one run of the command.
•The --server and --port options apply to diffdogcmdlclient only. They specify the connection details of the server.
•Control the output kind (text, XML) by setting the output-mode option, either in the configuration file or on the command line (see Options below).
•The path of the output file is set from either the client-output-path or server-output-path options (see Example below).
•To get the comparison outcome as a numeric value, check the value of the %ERRORLEVEL% environment variable after running this command. The possible values are:
Exit code | Meaning |
0 | No differences exist |
1 | Differences exist |
2 | An error has occurred |
Options
Specifies the path to a client configuration file containing the datadiff information and the details of the connection to DiffDog Server.
If you do not set the --c option, then diffdogcmdlclient attempts to read the client configuration file from the same directory as the executable. If there is no configuration file in the same directory, then the default values displayed by the showcfg CLI command and in the topic Client Configuration File are used.
|
Sets the reporting level to show information, warning, or error messages. Valid values are:
•none: Suppress all logging •info: Report information, warning, and error messages •warning: Report error and warning messages •error: (Default) Report only error messages
|
Specifies the port on which DiffDog Server listens for requests. If this option is not set, then the port specified in the Client Configuration File will be used (29800 by default). The --port option overrides the setting in the config file.
|
Specifies the server on which DiffDog Server listens for requests. If this option is not set, then the server specified in the Client Configuration File will be used (127.0.0.1 by default). The --server option overrides the setting in the config file.
|
Prevents the standard output from being displayed at the command line. Valid values are:
•true: Do not display standard output in the terminal window. •false: (Default) Display standard output in the terminal window.
|
Specifies the output format of the generated report. The following values are available: text, sql, xml, html. The default option is sql.
Generating the report in SQL format is meaningful when the right side of the data comparison is a database. It contains SQL statements that merge data from the left to the right side of the comparison. For example, if there are missing rows on the right, INSERT statements are generated. Conversely, if there are extra rows on the right, DELETE statements are generated. For changed values, UPDATE statements are generated.
However, when the left side of the comparison is a database and the right side is a CSV file, then the default option is text.
|
Examples
The following command runs a single database data comparison job called reports.
diffdogserver data-diff reports
It is assumed that the client configuration file contains a [datadiff:reports] section, such as the listing below:
[datasource:left_data]
type = sqlite
path = c:\comparisons\db\Nanonull_Left.sqlite
table = "main"."products"
[datasource:right_data]
type = adonet
connection = Data Source=SQLSERV16;Initial Catalog=NANONULL;MultipleActiveResultSets=True;Password=7hiu57;Persist Security Info=True;User ID=altova
assembly = System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
class = System.Data.SqlClient
table = [NANONULL].[dbo].[PRODUCTS]
[datadiff:reports]
left = left_data
right = right_data
map = 1 => 1, 2 => 2, 3 => 3
output-mode=text
client-output-path=c:\comparisons\db\result.txt
In the configuration file above, the [datasource:left_data] and [datasource:right_data] are two data sources that provide data for the left and right side of the comparison. The left_data points to table products from a local SQLite database. The right_data points to table PRODUCTS of a SQL Server database. Both tables have a primary key, so it is not necessary to specify a key column using the option key-column-by-name.
The [datadiff:reports] section sets both data sources above as, respectively, the left and right side of the comparison. In addition, it maps the columns that are to be compared. In this example, the first, second, and third columns from the left are mapped with the first, second, and third columns from the right, respectively. The comparison result is set to be in text format and to be saved to a local text file.
For a complete set of options that can be defined in a data source, see how to set up data sources. For information about the options you can define in a database comparison job, see Set up CSV and Database Comparisons.