The SQL Database External TagProvider connects to solution databases at runtime, enabling data querying without local Tags. Configure this External TagProvider to display queried data on TrendCharts and other application areas, enhancing real-time access and utilization of database information within the Unified Namespace.
On this page:
Configuration
To set up SQL Database as an External Tags source, navigate to Unified Namespaces / External TagProviders and select the SQL Database Connector.
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. |
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. |
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:
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.
You can browse the available columns at the '+' button.
Columns to Discard: List of columns to be discarded, separated by comma (',') in TreeView Structure.
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: 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:
SELECT <Columns_Minus_Discarded> FROM <Table> WHERE [Digital1] = <Val1> AND [Digital2] = <Val2>
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.
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:
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 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: