Database to Other Systems

If you want to transfer data securely, it has proven effective to do so using a handshake procedure. For relational databases such as Microsoft SQL Server, the “Mark Transferred Values” function is ideal for this purpose.

You can find the technical details on this in the relational database transfer objects and the transfer status trigger.

In this process, the transfer status is recorded in a column of the table. By default, the OPC Router uses the following values for this purpose:

note

0 Not transferred
1 Transferred
2 Error during transfer

These values correspond to the OPC Router’s current default values for the transfer status flag and can be adapted to the specific application as needed.

Regardless of this, you should specify the default value with which new records are initially created for the transfer column in the database table. The column should not allow NULL values and should be assigned a default value that matches the desired process.

note

If new records are to be queued for transfer immediately, the value for “Not transferred” is a suitable default for the table—in the standard implementation, this is 0.

If, on the other hand, new records are not to be processed automatically at first, the column in the table can also be preset with the value for “Transferred” or another business-appropriate status; in the standard system, for example, with “1.” In this case, the data record is not processed until a downstream process specifically sets the transfer status to the value for “Not transferred”.”

This generally ensures that applications to be integrated—for which this column is added—continue to run without errors, since the SQL Server can automatically populate this column with a value during an INSERT operation. The OPC Router thus knows that this record has not yet been transferred and transmits it during the next cycle.

tip

You can add multiple transfer status columns to a table if you want to use the same record in different process steps.

This is helpful, for example, if you want to print or further process a data record at different stations.

Retry on Error

With DB Select and the transfer status trigger, the OPC Router supports retries in the event of an error, such as when the target system is unreachable. To do this, use an additional column to track the number of errors. This automatically aborts the transfer when an error count threshold is exceeded and marks the data record as permanently corrupted. Additionally, a time delay for retries in case of an error can be configured.

It is possible to enable an alert for error-prone transfer states via an additional OPC Router flow.

Step-by-Step Configuration of a Transfer Column Handshake

1. Add an additional column named TransferState to your table and set the default value to 0, disallowing null values.

   

2. Add the Transfer Status Trigger to your OPC Router flow and double-click it to open it for editing. Select your database, your table, and then the newly created TransferState column to highlight it.

   

3. In the trigger, select the columns you want to use and save the settings.

   

4. Create the actual flow to the target system. In the example shown, print data is sent to a Windows printer and production is activated.

   

5. Now write data to the table and set the transfer status to 0. This triggers the flow, and the data is sent to the target system.

   

6. In Microsoft SQL Server Management Studio, you can then see that the record has been marked with a transfer status of 1, indicating that it was successfully transferred.