Types of Groups
The SQL Bridge Module provides four different types of transaction groups that you can use in your projects. Each of these different types of groups vary in their operation and use for data logging and database to PLC synchronization.
The limited version of SQL Bridge module only has access to the Historical Group
Standard Group
The standard group is called such because it's a flexible, general use group that can be adapted to a variety of situations. The data model is row based, with items mapping to columns and the data corresponding to a specific row of a table.
This group contains items, which may be mapped to the database, or used internally for features such as triggering or handshakes. Items that are mapped to the database target a specific column of a single specific row, chosen according to the group settings. Items can be mapped in a one-way fashion, or bi-directionally, in which the value of the database and the item will be synchronized.
The group may also insert new rows instead of updating a specific row. In this manner, data can be inserted for historical purposes based on a timer, with an optional trigger.
Group Settings
The standard group uses a timer-based execution model shared by all groups, and the normal trigger settings. Additionally, there are several settings specific to the group type.
| Property Name | Description |
|---|---|
| Automatically create table | If the target table does not exist, or does not have all of the required columns, it will be created/modified on group startup. If not selected and the table doesn't match, an error will be generated on startup. |
| Use custom index column | If selected, you may enter any column name to hold the index. If unselected, the table index will be named <table name>_ndx. |
| Store timestamp to | Specifies whether or not to store a timestamp with the record, and the target column. The timestamp will be generated by the group during execution. For groups that update a row, the timestamp will only be written if any of the values in the group are also written. |
| Store quality code to | If selected, stores an aggregate quality for the group to the specified column. The aggregate quality is the combined quality of all of the items that write to the table. For more information about quality values, see Data Quality. |
| Delete records older than | If selected, records in the target table will be deleted after they reach the specified age. This setting is useful for preventing tables from growing in an unbounded manner, which can cause disk space and performance problems over time. |
Table Action
This section details how the group interacts with the table on each execution, and is not available for the Historical Group type. This means when the Timer or Schedule is active, and the Trigger condition are met. The group can insert a new row, or update the first, last or a custom record.
| Property Name | Description |
|---|---|
| Insert New Row | This option will make the group insert a new record into the database every time the group executes. This is the forced behavior of the Historical group. |
| Update / Select | This option will either update or select from matching rows based on the option selected below it. The Update Mode property above determines whether an update (OPC to DB), select (DB to OPC), or both (Bi-directional) are used when the group executes.
|
Typical Uses
Standard groups can be used any time you want to work with a single row of data. This can include:
- Historical logging - set the group to insert new records, and log data historically either on a timer, or as the result of a trigger. Flexible trigger settings and handshakes make it possible to create robust transactions.
- Maintain status tables - Keep a row in the database updated with the current status values. Once in the database, your process data is now available for use by any application that can access a database, dramatically opening up possibilities.
- Manage recipes - Store recipe settings in the database, where you have a virtually unlimited amount of memory. Then, load them into the PLC by mapping DB-to-OPC using a custom where clause with an item binding in order to dynamically select the desired recipe.
- Sync PLCs - Items in the group can be set to target other items, both for one-way and bidirectional syncing. By adding items from multiple PLCs to the group, you can set the items of one PLC to sync with the others. By creating expression items that map from one PLC item to the other, you can manipulate the value before passing it on.
Block Group
The block group is so named because it writes "blocks" of data to a database table, consisting of multiple rows and columns.
A block group contains one or more block items. Each block item maps to a column in the group's table, and then defines any number of values (OPC or SQLTag items) that will be written vertically as rows under that column. The values may be defined in the block item in two modes. The first, List mode, lets a list of value-defining items to be entered. These value items may either by OPC items, SQLTag items, or static values. The second mode, Pattern mode, can be useful when OPC item paths or tag paths contain an incrementing number. You may provide a pattern for the item's path, using the wildcard marker {?} to indicate where the number should be inserted.
Block groups are very efficient, and can be used to store massive amounts of data to the database (for example, 100 columns each with 100 rows- 10,000 data points- will often take only a few hundred milliseconds to write, depending on the database). They are also particularly useful for mirroring array values in the database, as each element will appear under a single column, and share the same data type.
Like the standard group, the block group can insert a new block, or update the first, last or a custom block. Additionally, the group can be set to only insert rows that have changed in the block.
In addition to block items, the group can have other OPC items, tag references, and Expression items. These items can be used for triggers, handshakes, etc. They may also target a column to be written, and will write their single value to all rows in the block.
Group Settings
Beyond the differences in the data, namely that the block group works with multiple rows instead of just 1. This group type shares many settings with the Standard Group.
| Property Name | Description |
|---|---|
| Automatically create table | If the target table does not exist, or does not have all of the required columns, it will be created/modified on group startup. If not selected and the table doesn't match, an error will be generated on startup. |
| Automatically create rows | If the target rows do not exist, they will be created on group execution. If not selected and the rows don't match, no records will be updated. |
| Use custom index column | If selected, you may enter any column name to hold the index. If unselected, the table index will be named <table name>_ndx. |
| Store timestamp to | Specifies whether or not to store a timestamp with the record, and the target column. The timestamp will be generated by the group during execution. For groups that update a row(s), the timestamp will only be written if any of the values in the group are also written. |
| Store quality code to | If selected, stores an aggregate quality for the row to the specified column. The aggregate quality is the combined quality of all of the items that write to that row. For more information about quality values, see Data Quality. |
| Store row id | Each row will be assigned a numeric id, starting at 0. If selected, this id will also be stored with the data. |
| Store block id | If selected, an incremental block id will be stored along with the data. This number will be 1 greater than the previous block id in the table. |
| Delete records older than | If selected, records in the target table will be deleted after they reach the specified age. This setting is useful for preventing tables from growing in an unbounded manner, which can cause disk space and performance problems over time. |
Table Action
This section details how the group interacts with the table on each execution, and is not available for the Historical Group type. This means when the Timer or Schedule is active, and the Trigger condition are met. The group can insert a new row, or update the first, last or a custom record.
| Property Name | Description |
|---|---|
| Insert New Block | If selected, each row of the block will be inserted when the group executes, even if the data has not changed. |
| Insert changed rows | - This option will only insert the rows that have new data when the group executes. This is particularly useful for recording history for many data points on an "on change" basis, provided there is a unique id column defined. The "store row id" feature is useful for this, as well as the ability to reference the item path in an item's value property. |
| Update / Select | This option will either update or select from matching rows based on the option selected below it. The Update Mode property above determines whether an update (OPC to DB), select (DB to OPC), or both (Bi-directional) are used when the group executes.
|
Typical Uses
Block groups are useful in a number of situation where you need to deal with a lot of data efficiently. Mirroring/Synchronizing array values to DB - Arrays are often best stored vertically, which makes them perfect for block groups. Pattern mode makes configuration a breeze by allowing to you specify the array as a pattern, and set the bounds.
- Recipe management - Like standard groups, but when set points are better stored vertically than horizontally.
- Vertical history tables - Group data points by data type (int, float, string), create a copy of the item that stores item path, and then use the insert changed rows option to create your own vertically storing historical tables. Create additional copies of the block item that refer to quality and timestamp in order to get further information about the data point.
Historical Group
The historical group makes it easy to quickly log data historically to a SQL database. This group inserts records of data into a SQL database, mapping items to columns. Full support for triggering, expression items, hour & event meters and more means that you can also set up complex historical transactions. Unlike the standard group, the historical group cannot update rows, only insert. It also cannot write back to items (besides trigger resets and handshakes).
Group Settings
The settings of the historical group are identical to the settings in the Standard Group, but limited to inserting rows.
Typical Uses
- Basic historical logging - Recording data to a SQL database gives you incredible storage and querying capabilities, and makes your process data available to any application that has DB access. +Shift tracking - Use an expression item to track the current shift based on time, and then trigger off of it to record summary values from the PLC. Use a handshake to tell the PLC to reset the values.
Stored Procedure Group
The stored procedure group lets you quickly map values bi-directionally to the parameters of a stored procedure.
This group is similar to the others in terms of execution, triggering, and item configuration. The primary difference is that unlike the other group types, the target is not a database table, but instead a stored procedure.
Items in the group can be mapped to input (or inout) parameters of the procedure. They also can be bound to output parameters, in which case the value returned from the procedure will be written to the item. Items can be bound to both an input and output at the same time.
Parameters may be specified using either parameter names or numerical index. That is, in any location where you can specify a parameter, you can either use the name defined in the database, or a 0-indexed value specifying the parameter's place in the function call.
You cannot mix names and indices. That is, you must consistently use one or the other.
If using parameter names, the names should not include any particular identifying character (for example, "?" or "@", which are used by some databases to specify a parameter).
Group Settings
The stored procedure group's settings look and act the same as those of the Historical Group. The primary difference, of course, is that instead of specifying a table name and column names, you'll specify a Stored Procedure and it's parameters.
| Property Name | Description |
|---|---|
| Store timestamp to | Specifies whether or not to store a timestamp with the record, and the target column. The timestamp will be generated by the group during execution. For groups that update a row, the timestamp will only be written if any of the values in the group are also written. |
| Store quality code to | If selected, stores an aggregate quality for the group to the specified column. The aggregate quality is the combined quality of all of the items that write to the table. For more information about quality values, see Data Quality. |
| Procedure Name | The name of the Stored Procedure (SP) that you will be using. You must look into the SP definition to see what inputs and outputs are available. |
Typical Uses
- Call stored procedures - The stored procedure group is the obvious choice when you want to bind values to a stored procedure. It can also be used to call procedures that take no parameters (though this can also be accomplished from Expression Items/SQLTags).
- Replace RSSQL - The stored procedure group is very popular among users switching from RSSQL, given that application's heavy use of stored procedures.
Known Issues
When using Oracle, you must use indexed parameters.