SQL Bridge (Transaction Groups)
Transaction Groups are the heart of the SQL Bridge module. They are execution engines that perform database tasks such as storing data historically, synchronizing database values to a device, and loading recipe values. A variety of group types, items types, and options means that Transaction Groups can be configured to accomplish a variety of tasks against a database table.
Transaction Group Overview​
At their core, Transaction Groups are user-configured collections of data references (called items) that are then linked to columns in a table in a SQL database. Items can be OPC references, tag references, expressions, or SQL queries, and transaction groups allow precise control of data flow to, or from, specified items to a configured table. To set up and use Transaction Groups, SQL knowledge is not required; all a group needs to run is a database connection. Ignition can automatically create and manage the database table for each group.
Transaction Groups are configured in the Ignition Designer. The group will then execute at a specific interval of time, or on a user-defined schedule. A trigger can be used to prevent planned executions when specified conditions are not met. On execution, the simplest groups (Historical groups) will create a new row in the database table with a separate column for each Item in the group. However, more complex variations are possible.
Transaction Group Applications​
There are four types of Transaction Groups, and they each handle data a little differently.
- Historical Group - A basic, one-way group that collects a data point from each item, and logs it to a column in the database.
- Standard Group - A flexible, two-way group that can log data to the database, or pull data from the database to write to OPC items or tags.
- Block Group - A standard group with an additional feature: each execution of the group can create, modify, or extract multiple rows from the database table, rather than just one.
- Stored Procedure Group - A group capable of invoking a stored procedure in the database, returning the results of any OUT or INOUT parameters to tags or OPC items.
Learn more about each type of group on the Types of Groups page.
Historical Data Logging​
Transaction groups can function as a flexible alternative to the Tag Historian, which is more focused on speed and efficiency than simplicity. The Historical Group is explicitly designed for this purpose, but the Block Group can allow multiple rows to be logged at once.
Recipe Management and Synchronization​
Transaction groups can furnish devices connected to Ignition with information stored in a database, based on a parameter from the device or elsewhere. Typically a Standard Group would be used for this application, but Block and Stored Procedure groups can accomplish this as well. See the Recipe example for more details.
More generally, Standard and Block groups can be used to synchronize device or tag data with a database table on a timer or schedule, allowing the database to act as a go-between for another system and a target device, or for the database to reflect realtime values for specified data points.
Block Data Transfers​
The Block Group supports the transfer of entire arrays of data to and from the database.
Stored Procedures​
The Stored Procedure Group allows you to use group items as inputs and outputs for your existing Stored Procedures, allowing query specifics to be managed external to Ignition.
