Skip to main content
Version: 5.4

Database to other systems

When you want to securely transfer data, it is a common practice to do so using a handshake procedure. For databases like Microsoft SQL Server, the "Mark Transferred Values" function can be used for this purpose. In this process, the transfer status is recorded in a column of the table. The OPC Router defines the following states:

note

0 Not Transferred

1 Transferred

2 Transfer Error

It is advisable to configure the transfer column in the underlying table to disallow null values and accept "0" as the default value.

note

Certainly, the value doesn't have to be set as a default "0" (transfer pending); it can also be set as a default "1" (no transfer pending). In this case, it can be updated to "0" later in the process, for example, by an action initiated by a user when a transfer is intended to take place.

This typically ensures that applications to which this column is added will continue to run smoothly. When the SQL Server performs an insert, it can automatically populate this column with a value. As a result, the OPC Router is aware that this record has not been transferred yet and will transmit it in 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 can be helpful, for example, when you want to print a record at different stations or for similar scenarios.

Repetition in case of an error​

The OPC Router supports the possibility of retrying in case of an error using the Select Transfer object and the Transfer State Trigger, for example, when the target system is not reachable. To implement this, you can use an additional column to keep track of the error count. When the error count exceeds a specified threshold, the transfer is automatically canceled, and the record is marked as final-error. Additionally, a time delay (delay) for retries in case of errors can be configured.

It's also possible to enable an alerting mechanism for erroneous transfer states by using an additional OPC Router connection.

Step-by-Step Configuration of a Transfer Column Handshake​

  1. Add an additional 'TransferState' column to your table and set the default value to 0 (2), disallowing null values (1).\

  2. Add the Transfer State Trigger to your OPC Router connection and open it by double-clicking for editing. Select your database (1) and your table (2). Then, choose your recently created 'TransferState' column for marking (3) and switch to the 'Columns' tab (4).

  1. Select the columns you want to use and click OK to save the trigger with the settings (1).

    1. As an example, we send our print data to our Windows printer and activate production (1).
    1. When we write data to the table, and the Transfer State is set to 0, the connection is triggered, and the data is successfully sent to our printer.
    1. We can now also see this in the Microsoft Management Studio because our data has been marked with a Transfer State of 1, meaning they have been successfully transferred.