DB Log Table transfer object
The transfer object DB Log Table compares the structure of a database table at runtime with a target structure stored in the transfer object and then writes the incoming data records to this table. If the table does not exist, it is created; if it differs from the target structure, it is corrected according to the configured behavior. The transfer object thus functions as a transfer destination.
[DB Log Table TO in the configuration area]
After double-clicking the transfer object, configure the properties on the "Log Table" and "Optionen/Verhalten" tabs.
Tab: Log Table
On this tab, you define the database connection, the target table, and the desired column structure.
[DB Log Table TO [Log Table] tab]
| Property | Description |
|---|---|
| Data Source | All database connections created in the Plug-ins section are available for selection here. If the desired connection does not yet exist, you can create it in the Plug-ins section as Microsoft SQL Server, Oracle, SAP HANA, ODBC, OLE DB, MySQL, PostgreSQL or MS Access. |
| Table name | Name of the table whose structure will be matched and into which data will be written. If the table does not exist, it will be created with the structure defined below. |
Columns
In the column table, you define the target structure of the destination table. Use the Add button to create a new row, and the button at the beginning of the row to remove a column. Each row describes a column in the table.
By default, the column table displays the four properties <Ui>Spaltenname</Ui>, <Ui>Datentyp</Ui>, <Ui>Pflichtfeld</Ui>, and <Ui>Standardwert@db-log-tabelle</Ui> in this fixed order:
| Property | Description |
|---|---|
| Column Name | Name of the column in the database table. The name must be unique within the table. |
| Data Type | Data type of the column. Depending on the selected data type, the fields “Length”, “Precision”, and “Decimal Places” (advanced options) are enabled. If an integer data type (Int16, Int32, Int64) is selected and the column is simultaneously marked as “Primary Key”,” the OPC Router automatically activates “Automatic Counter”.” |
| Required Field | If this checkbox is selected, the column must be assigned a value when writing; it must not remain empty (NULL). If it is disabled, the column may remain empty. |
| Default value | Value that is entered if no input value is provided for the column. The field is always writable. For required fields, a default value must be specified so that a NULL value does not appear in the table when writing without an input value. |
When using a connection to MS Access, the data type Int64 is currently not configurable for technical reasons. The transfer object indicates this in the dialog. In this case, use Int32 or Decimal.
Use the “Show advanced options” button above the column table to display additional properties; they are appended to the right of the four standard columns, whose order remains unchanged:
| Property | Description |
|---|---|
| Length | Maximum length of the column. Relevant only for text and binary columns. |
| Precision | Total number of digits for decimal columns. Relevant only for the Decimal data type. |
| Decimal Places | Number of decimal places for decimal columns. Relevant only for the Decimal data type. |
| Primary Key | Marks the column as part of the primary key. When writing data, the primary key determines whether a record is inserted or updated. |
| Auto-Increment | The column is automatically filled by the database (Auto-Increment/Identity). Available only for primary key columns. An input value bound to this column is not written. |
Hiding the advanced options does not change the behavior: Values that have already been configured (e.g., primary key or auto-increment) are retained and continue to apply during schema synchronization and when writing data, even if the corresponding columns are not currently displayed.
No value is written to a column with “Automatic Counter” because the database assigns the value itself. A primary key without an automatic counter, on the other hand, must be populated via a connected input.
Tab: Options/Behavior
On this tab, you specify how the OPC Router handles discrepancies between the existing table and the defined target structure.
[[DB Log Table TO tab: Options/Behavior]
For each of the three scenarios, the following actions are available: “Do nothing”,” “Error”,” and “Correct”:
| Property | Description |
|---|---|
| Deviating Column | Behavior when an existing column has a different data type than specified in the target structure. “Do nothing”: Deviation is ignored. “Error”: The transfer is terminated with an error. “Correct”: The affected column is deleted and recreated. |
| Extra Column | Behavior when the table contains a column that is not in the target structure. “Do nothing”, “Error”, or “Correct” (the extra column is removed). |
| Missing Column | Behavior when a column from the target structure is missing from the table. “Do nothing”, “Error”, or “Correct” (the missing column is added). |
When running “Correct” for mismatched or extra columns, the affected columns are deleted (and, in the case of mismatched columns, recreated). All data contained in these columns will be permanently lost.
If the selected database does not support modifying or removing existing columns via automatic structure synchronization, the actions for mismatched and redundant columns are not available. Adding missing columns is still possible.
Check the expected number of incoming records
Select this option to verify that the incoming data meets the expected size before writing it. Under “Expected records”,” use the drop-down menus (“More than”,” “Less than”,” “Exact”) and the specified number to define the minimum, maximum, or exact number of records required for the transfer to proceed.
Writing the Records
After the structure comparison, the transfer object writes the incoming values to the table. The primary key determines whether a record is inserted or updated. For columns without an associated input value, the following applies:
- Columns that are not required fields remain empty (NULL), even if a default value is specified for them.
- Columns with “Automatic Counter” are populated by the database.
- Required fields use their “Default Value”. If this is missing, an error is triggered.