Examples and Concepts
This section brings together typical implementation patterns for relational databases in the OPC Router. The focus is on reusable concepts for reliable data exchange between database tables and other systems, such as OPC UA, printers, MQTT, or other target systems.
The examples supplement the descriptions of the plug-ins, transfer objects, and triggers. They do not primarily showcase individual database drivers, but rather typical business and technical patterns for operation.
Typical Use Cases
- Read data records from a table and pass them on to other systems
- Mark transferred data records in the database
- Detect failed transfers and retry
- Responding to changes in database tables using triggers
- Using stored procedures for encapsulated database logic
Available Examples
- Database to Other Systems describes a proven handshake procedure with a transfer status column, retries in case of error, and a step-by-step sample configuration.
- Retrieve Latest Record reads the most recent record from a table, queried cyclically and sorted by timestamp.
- Retrieve the latest record not yet transferred adds a column for the transfer flag to the same query.
- Retrieve all records not yet transferred fetches all unmarked records in a single pass.
Relevant Concepts and Fundamentals
- Relational database transfer objects, including the current individual objects DB Select, DB Insert, DB Update, and DB Delete.
- Stored procedure transfer objects for calling stored procedures and the specific considerations regarding parameters, result sets, and transactions.
- Relational database triggers for event-driven responses to data changes or transfer states.
- Database filter operators for defining filter conditions in transfer objects and triggers.
Recommended Procedure
The following procedure has proven effective for robust integrations:
- Define the business role of the table, for example, as a source table, target table, or transfer table.
- Determine whether the transfer should be cyclical via a transfer object or event-driven via a trigger.
- Use transfer status or handshake columns if records need to be traceably read, confirmed, or reprocessed.
- Limit SELECT queries using appropriate filters, sorts, and, if necessary, transfer flags.
- For larger data sets, also review the performance notes for the database plug-in being used.
Database-Specific Additions
Depending on the relational database plug-in used, additional notes may be relevant, for example regarding authentication, SQL dialect, driver properties, or performance.
For Microsoft SQL Server, you can find further information at: