DB Select transfer object
The DB Select transfer object retrieves one or more records from a table or database view. The transfer object thus serves as a data source.
Dialog Overview
General Properties
| Property | Description |
|---|---|
| Data Source | All relational database connections created in the Plug-ins section are available for selection here. If your desired database connection is not listed, you can set it up in the Plug-ins section as Microsoft SQL Server, Oracle, SAP HANA, ODBC, OLE DB, MySQL, or PostgreSQL. |
| Table or database view from which data is read. | |
| Available Columns | Columns in the table that have not yet been used. |
| Used Columns | Columns that are displayed as elements of the transfer object and are therefore available for the query. |
The distinction between available and used columns serves to provide clarity in the design area. Move columns by double-clicking the column name or using the <, >, <<, and >> buttons. Use the Up and Down buttons to sort the used columns.
Filters
Filters are configured graphically. The OPC Router uses this configuration to generate the appropriate database query at runtime. The values for the filters can be populated dynamically at runtime or defined using variables during the design phase.
| Property | Description |
|---|---|
| Filter | Graphically displays filters and filter groups. |
| Link | Specifies the AND and OR operators between filters and filter groups. |
| Column | Column used for filtering. |
| Comparison Operator | Operator for comparing values. |
| Preview | Displays the resulting query as an SQL statement, for example WHERE (TIMESTAMP > ?) AND (VALUE = ?). |
In the configuration interface, you’ll see the filter criteria as elements. Drag items from any other transfer objects onto the filter criteria to populate them. For timestamp filters, you can also use relative values, such as -1d for the previous day’s date.
An explanation of the various filter operators can be found here.
Query
| Property | Description |
|---|---|
| Sort Order | Ascending or descending. |
| Sort Column | Column by which the query results are sorted. |
| Do Not Modify Records | Only available for queries from tables. The transferred records are not modified. |
| Mark records after transfer is complete | Only possible for queries from tables. In a corresponding column of the source table, “1” is entered for each successfully transferred record and “2” for a failed transfer. |
| Column for marking | Only possible for queries from tables. Column in which the marker for a successful or failed data transfer is entered. |
| Marker values | Values that are entered in the marker column. The marker values must be unique. |
| Count failed transfers | Counts the failed transfers. |
| Delete records after a successful transfer | Only possible for queries from tables. Transferred records can also be deleted from the table. |
| Cancel transfer (if query result is empty) | Cancels the data transfer if the query result is empty. |
| Trigger read error (if query result is empty) | Cancels the data transfer if the query result is empty and triggers error logging. |
| Return an empty record set | Returns an empty record set if the query result is empty. |
| Limit the number of records to | Transfers only the number of records specified here. The sort order must be set accordingly. |
For “Mark records after transfer”, a simple primary key on the numeric column ID is required in the relevant table.
If you are using “Count failed transfers”, select the column in which the count of failed transfers should be incremented under “Error column”.
If you enable “Delete records after successful transfer”,” the records in the source table will be permanently deleted.
Notes
Use DB Select only in conjunction with filters to prevent the entire table from being loaded into memory. For example, when using transfer flags, filter by <Transferflag> <> 1.
Mark transferred records using a separate transfer flag column. You can also find the technical basics for this in the Transfer Status Trigger as well as under Examples and Concepts.
To optimize performance, you can filter by the transfer flag column, for example, Transferflag = 0 or Transferflag = 2.