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, retry on failure, and a step-by-step example configuration.
Relevant Concepts and Fundamentals
- Database Transfer Object for SELECT, INSERT, UPDATE, and DELETE operations on tables and views.
- Stored Procedures for calling stored procedures and the specifics regarding parameters, result sets, and transactions.
- Relational Database Triggers for event-driven reactions to data changes or transfer statuses.
- 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: