Setting up CSV and Database Comparisons
A database data comparison involves comparing a "left" data source (table, view, or recordset retrieved by a SQL query) with a "right" one.
To configure a data comparison, open the client configuration file and add a new [datadiff:<name>] section, where <name> is the unique name by which you want to identify this database comparison. In the database comparison job, you essentially specify what should be "left" data source and what should be the "right" one. It is assumed that you have already created two or more data sources as described in Setting up Data Sources.
The parameters that may be present in a [datadiff] section are listed below.
enable-driver-logging
Optional parameter. Turn it on if you need the database driver to return more detailed information in case of errors. Example:
enable-driver-logging=true |
Note that enabling this option may slow down execution.
server-left, server-right
Conditional parameter. This option lets you specify that the left or right data source of the comparison is defined in the server configuration file (not the client configuration file). The option must point to a named data source as it is defined in the server configuration file on the machine where DiffDog Server runs. Example:
server-left=products server-right=resourcesdb |
To point to a data source on the machine where the DiffDog Command Line Client runs, use the left and right options instead (see below).
left, right
Conditional parameter. This option sets the left or right side of the comparison. The option must refer to a named data source [datasource:<some_name>] as it was defined in the client configuration file on the machine where the comparison runs. Example:
left=hr right=testdb |
If you don't set these options, then the server-left or server-right options must be set instead. Combined comparison between client data sources and server data sources are also possible, for example:
left=hr server-right=testdb |
In the example above, the data source "hr" is on the machine where DiffDog Command Line client runs (without a server). The data source "testdb" is defined in the server configuration file on the machine where DiffDog Server runs.
map
Optional parameter. This option specifies which columns from the left side of the comparison should be compared to those from the right side of the comparison. For example, you might want to compare columns col1, col2, col3 from the "left" table with columns colA, colB, colC from the "right" recordset. To indicate a column, you can specify either the ordinal number of the column, starting with 1, or the column name. If you use the column name, the name must be enclosed within quotes. Example:
map = 1 => "col 2", 2 => "col1", "h" => 6, "i" => "x" |
For each pair of columns shown above, the part before "=>" means "on the left side" and the part after "=>" means "on the right side".
In case of CSV files, you can refer to columns by using either the column name (if the first row is a header), or by using c1, c2, and so on, where the number corresponds to the column index, starting with 1.
map=1=>c1,2=>c2 |
If you don't define column mappings, the application will attempt to map columns automatically based on the name and data type. However, this is not guaranteed to work correctly if the columns on the left and right have different names and types.
xml-aware
Optional parameter. When enabled, this option performs XML (not text) comparison for database fields of XML type. This applies only to fields whose data type is recognized by the driver as XML. Database fields which store XML as varchar or clob are not affected by this option. If you need to treat such fields as XML type, use the sql option instead of table and perform type conversion to XML by means of SQL queries.
If you set this option, any XML comparison options present in the configuration file or specified at the command line will apply as well. On Windows, if DiffDog is installed on the same machine as the DiffDog Command Line Client, then XML options present in the registry will apply as well. DiffDog Server does not read the Windows registry options. You can always view the current configuration options of the client by running the showcfg command. To view the configuration options of the server, run the showcfg command of the server executable.
For reference to all XML comparison options, see the diff command.
whitespace-mode
Optional parameter. Applicable only for database fields of text type such as varchar, clob, and so on. The option defines how whitespace characters should be treated for comparison. Whitespace characters are any of the following: space, tab, carriage return, and line feed.
normalize | Text A is equal to Text B if, after normalization, characters in Text A correspond to those in Text B. “Normalization” means that multiple consecutive occurrences of whitespace characters are replaced by a single space character. In addition, the leading and trailing whitespace characters are trimmed for each line of text. |
strip | Text A is considered equal to Text B if, after stripping whitespace characters, characters in Text A correspond to those in Text B. In other words, any whitespace characters are stripped from the text and considered not relevant for comparison. |
ignore-case
Optional parameter. Applicable only for database fields of text type such as varchar, clob, and so on. Valid values:
true | Perform a case-insensitive comparison (for example, "A" and "a" are treated as equal). |
false | (Default) Perform a case-sensitive comparison (for example, "A" and "a" are not treated as equal). |
server-output-path
Optional parameter. Specifies the path to which the comparison results will be saved on the server machine, for example:
server-output-path=/path/on/server.xml |
If DiffDog Command Line Client and DiffDog Server run both on the same machine, you can use server-output-path and client-output-path interchangeably.
If this parameter not set, the comparison output will be displayed at the command line (assuming that you did not set the option -quiet=true).
client-output-path
Optional parameter. Specifies the path to which the comparison results will be saved on the client machine, for example:
client-output-path=/path/on/client.xml |
If this parameter not set, the comparison output will be displayed at the command line (assuming that you did not set the option -quiet=true).
output-mode
Optional parameter. Specifies the output format of the comparison result. Valid values are text, xml, and sql, for example:
output-mode=xml |
When this option is not set, the default value is text.
Note that the value sql is meaningful only when the right side of the comparison is a database. When output-mode=sql, the comparison report will include SQL scripts required to merge data from the left to the right side.
treat-null-as-empty
Optional parameter. Enable this option only if you need to treat NULL database values as empty strings.
treat-null-as-empty=false |
For example, by default, a comparison between the two recordsets illustrated below will result in a difference, because the second row on the left is empty and not equal to the second row on the right. However, if you set the treat-null-as-empty option to true, both recordsets will be considered equal.
