The SQL Database TagProvider connects to solution databases at runtime, enabling data querying without local Tags. Configure this TagProvider to display queried data on TrendCharts and other application areas, enhancing simplifying real-time access and utilization of database information within the Unified Namespace.
Configuration
To set up SQL Database as a TagProvidera new TagProvider connection navigate to Unified Namespace/TagProvider Connections and press the add button to open the New TagProvider Connection dialog. You can also create your TagProvider Connection directly from the AssetTree. To do this, navigate to Unified Namespace → TagProvider Connections and select the SQL Database Connector./Assets Tree and press 
to open the New TagProvider Connection dialog. In the configuration dialog, select the SQL Database Connector on the Provider field and insert the required connection parameters. After that, press the Ok button to finish the provider configuration, and it will create a folder automatically in the Assets Tree (Unified Namespace / Assets Tree).
| Info |
|---|
This TagProvider is Read-Only. |
Connection Parameters
Field | Description |
|---|---|
Provider | Specifies the system the TagProvider will interface with. In this case, it interfaces with the SQL Database Connector. |
Access Mode | Determines permissions for interacting with tags. Read allows data reading. |
Name | User-defined name for the TagProvider instance. Identifies the specific configuration in the system. |
Description | Explains the TagProvider's purpose or the systems it interfaces with. Provides context for its usage. |
ProtocolOptions | Defines specific communication settings for the connection to the device. True or False. |
Connection | Defines connection string for the SQL database connection settings, including database source, path, and additional parameters. |
Dataset DBs: Database connection configured on Datasets → DBs.
Is Project Historian DB: Flag indicating if the Dataset DB is default TagHistorian.
Is Trend XY: Flag indicating if Provider will be used to populate XY TrendChart (X-Axis is NOT Time-Based).
Table: Defines if Provider can connect to All tables, or a specific one.
Organization: Organization defined for user in InfluxDB Database.
If macro All is selected, you WILL NOT be able to define a filter.
X-Axis Column Name: Name of the column to be used in X-Axis.Field | Description |
|---|---|
Dataset DBs | Specifies the database to be used for storing and retrieving tag data. |
Is Solution Historian DB | Indicates if the database is used as a solution historian. |
Is Trend XY | Specifies if the data is to be treated as XY trend data, where X is NOT time-based. |
Table | Defines the table within the database to retrieve or store data from. |
X-Axis ColumnName | Specifies the column name to be used for the X-axis in trend data. |
Columns To Discard | Lists columns to be excluded from the data retrieval process. |
Filters | Specifies filters to be applied to the data retrieval process. |
Test | Verifies the connection settings to ensure successful communication with the specified database. |
Historian Integration
Configuring the Database Station
The Station syntax is:
You can browse the available columns at the '+' button.
List of columns to be discarded, separated by comma (',') |
You can browse the columns to be discarded at the '+' button.
Click '+' or '-' to add/remove filters, and Up/Down arrows to change the filter order.
Filters | Specifies filters to be applied to the data retrieval process. It defines |
a hierarchical order for columns, separated by comma (','), when assembling the TreeViewStructure. You can use an Asterisk (*) as wildcard for column filtering. Some usage examples are listed below. *Temperature: Will filter out any column that EndsWith the Temperature. LineMonitor*: Will filter out any column that StartsWith the LineMonitor. _*_Q: Will filter out any column that StartsWith an underscore ' _ ', and EndsWith Q. Is useful for filtering out TagQuality Info. |
You can browse the columns to be used as filter at the + button.
Click '+' or '-' to add / remove, and Up/Down arrows to change the filter order.
In the example illustrated in the image above, our queries would be assembled like:
| Code Block | ||
|---|---|---|
| ||
SELECT <Columns_Minus_Discarded> FROM <Table>
WHERE [Digital1] = <Val1> AND [Digital2] = <Val2> |
Test | Verifies the connection settings to ensure successful communication with the specified database. |
Connecting to a Solution Historian
The SQL Connector can be used with any SQL database and schema, but when used in conjunction with the SQL Historian tables, generated by the framework itself, some extended functional is available.
When the flag Is Solution
Connecting to a Project Historian Database
When the flag Is Project Historian DB is true, some Station parameters are automatically filled.
Is Trend XY: CheckBox is disabled. Default value is false.
X-Axis Column Name: Input field is disabled. Default value is UTCTimestamp_Ticks.
Columns To Discard: Input field is disabled. Default value is ID, LogType, NotSync, UTCTimestamp_Ticks, _*_Q.
Filters: Input field is disabled. Default value is empty.
n Runtime, the DataModel Structure will behave as follows:
In Runtime, the DataModel Structure will behave as follows:
Connecting to an External Database
In this configuration, the flag Is Project Historian DB is false. You can connect to any database (Project Owned or External) and access its contents in your Project.
Using Filters, you can customize your TreeView Structure to look like the result of a search query.
Example
Our Example will be a Multi-Line system, where different machines (PLCs) are acquiring data and sending them to a Main DataServer. All data is being stored in the same Table.
The result Table would look like this:
In order Our goal is to retrieve data from the Lines, based on which Machine stored them.
To achieve our goal, the ExternalTags Configuration will be:
With this parameters we , the TagProvider Configuration parameters are:
Connecting to a table called Custom Table.
Assigning column DateTime for X-Axis.
Discarding ID Column.
Using Machine and Line columns as filters.In Runtime, we can monitor the TreeView Asset created with our filters. The end result will be:
In this section:
| Page Tree | ||||
|---|---|---|---|---|
|