SQL Tag Template

PARCview can read and present information from any OLE DB or ODBC compatible data sources that can return time-sequenced values. This includes data stored in Microsoft SQL Server, ORACLE, and other relational databases.

PARCview uses SQL query templates to retrieve data from SQL databases. A SQL Tag Template is a set of SQL scripts that each return a specific data set based on the nature of the request from PARCview. Examples of these requests include building a tag list or retrieving tag data points. Each script is expected to return data in a specific column order, but it can be queried out in different ways depending on how it is stored in its native source and what constraints or criteria are desired.

A SQL Tag Template will typically correspond to one data source. PARCview uses the template’s scripts to retrieve the requested data for all tags in this source. Note, however, that multiple sources can use the same template, and multiple templates can access the same SQL database.

SQL Tag Templates feature tokens for dynamic text-to-value substitution. At execution the tokens are replaced in the query with appropriate argument values retrieved from PARCview. For example, queries will use information from PARCview displays, like using the start and end times of a trend as the start and end time bounds in data queries. User-defined tokens can also be used as custom parameters for retrieving a tag list, if needed.

Template Name: Each template must have a unique name. It does not have to match the corresponding source name. The SQL Tag Template for a SQL-type source is defined in Source configuration, in the server column. Open the Template Name drop-down menu to select from existing templates. To create a new template, make sure no template is selected, then enter a new template name.

Refresh Delay: Throttles frequent requests for current values by only executing once per delay interval.

DSN: The DSN field contains the connection string that PARCview uses to connect to the SQL Server. This field typically contains a file path to a .UDL file, with the syntax:

FILE NAME=\\UDL File Path\UDL File Name.UDL

This field also accepts an OLE DB connection string, with the syntax:

Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=SQL USER NAME;Initial Catalog=SQL DATABASE NAME;Data Source=SQL SERVER INSTANCE NAME

Use PARCview DSN: Check Use PARCview DSN to always use PARCview’s direct connection as defined in Site Connection Configuration. This option is useful when querying data out of the dataPARC configuration database, such as MDE, Alarm Event, Logbook, or Tag Limit data or when a linked server to the other database is created in the dataPARC configuration database.

Test DSN: Validates the DSN.

Script Type: There are three basic script types: tag queries that are used by PARCview to get tag information, read data queries that return tag values for a given time frame, and write data queries that modify tag values in their source. See Script Definitions.

Test Execute: Executes the SQL script. The Evaluate SQL Template window will ope

Date/Time Parameter Format

Format: The format of that dates and times will be displayed. Use time key letters, such as H for hour, M for month, m for minute, etc.

Test Value: Displays an example output of the Date/Time format.

Script Definitions

SQL Tag Templates contain scripts for different types of data requests. Each script must return a specific type of data set, depending on the nature of the request. This section will cover the different types of scripts and their purposes, the requirements for each script and some examples, as well as using tokens in scripts for dynamic text-to-value substitution.

There are three basic script types: tag queries that are used by PARCview to get tag information, read data queries that return tag values for a given time frame, and write data queries that modify tag values in their source.

Required script**     Recommended script*

Tag Scripts

Read Data Scripts

Write Data Scripts

Other Scripts

Using Tokens

SQL Tag Templates use tokens to dynamically replace text in a script with values retrieved from PARCview, like the start and end times of a trend or a tag id, just prior to the execution of the query. Tokens are delimited in the query text with the following syntax: { token }

In this example {TagId}, {StartTime} and {EndTime} are text substitution tokens, which will be replaced with arguments supplied by PARCview at runtime.

Available Tokens

*If a source does not require a user name or password for making a connection, the UserID and Password tokens can be used to configure multiple sources to use the same SQL Tag Templates, differing only in one or two parameter values. For example, the structure of the queries between sources might be nearly identical except that they access different tables names, which could be supplied in the User Name and Password fields in the source’s configuration.

Using Logic with Tokens

Simple logic can be used with tokens to build if/then/else statements and to replace null values with a default value.

If/Then/Else syntax:

Notes: The text in the “then:” and “else:” statements can contain tokens. Nesting of tokens is not supported. The text in the “if:” statement cannot contain quotation marks.

IsNull/Replace syntax:

Custom Tokens

User-defined tokens can be used in the tag list script. Custom tokens are any that PARCview finds in the script that are not one of the predefined tokens. The values that replace these tokens are supplied by the attributes of a UTag, specified in a tag switch:

Location.Source.TagName/ TagSwitch

Tag Scripts

While the tag list and tag info queries return similar information, they serve different purposes and are both required.

Tag List

The Tag List script should return the following fields, accessed by index:

The TagList script will return a list of tags available for this template. The first 5 columns are required and must be returned in the listed order.

Up to 9 optional columns on the end of the record can be used to provide values for custom tokens. These values will be known by their column names, and so the column name must correspond to a custom token name. The names of the first five columns returned in the record set are unimportant and are ignored.

These optional columns are usually needed to uniquely identify a tag in the source database where the pre-defined TagID token is not adequate to find the appropriate data.  The order of the rows is the order in which the tags will be listed in the Tag Browser.

Values are supplied to user-defined tokens via tag switches. For example, if the tag list script has a  custom token named “Long” for the long tag name, use that token name as the tag switch to specify the value to supply to the query for the corresponding parameter:

Location.Source.TagName/ Long=LongTagName

Note: If the template is being used by manually defined SQL tags (see System Config_SQL Tag) this script should be blank.

Tag Info

The Tag Info script should return the following fields, accessed by index:

 The TagInfo script is used when a Tag is added to a trend and initial values for its attributes are needed.  The Plot Min and Plot Max fields should be numeric. This script requires the predefined token {TagId}

Interpolation Type

 Interpolation controls how PARCview estimates values at times between when actual values are found. When interpolation is Continuous, PARCview will conceptually draw a straight line between adjacent points and return the value on the line at the requested timestamp. State interpolation (or step interpolation) assumes that the value remains constant between samples. When the interpolation type is State, PARCview holds the earlier value as constant up until the new timestamp. Manual interpolation is mostly used for intermittent data such as lab results. This interpolation mode is used to normalize process data to lab tests.

Draw Mode

This attribute is used by the trend. Usually the Interpolation and Draw Modes will be set consistently. That is, if a tag’s value will be interpolated by connecting contiguous points, then the draw mode should likely be set to be Continuous.

Value Data Type

The Value Data Type refers to how the value is stored and displayed. It can be entered either as a numeric code that follows the variant-type definitions defined by Microsoft ( http://msdn.microsoft.com/en-us/library/cc237865.aspx) or specified with a text string. Common values are listed below.

 In addition to simple objects, PARCview recognizes a compound value called Digital Text. Digital text tags have both an integer numeric code as well as a corresponding string representation. An example might be a lab quality tag, where a value of 0 = Fail and 1 = Pass. The read data scripts use an optional column to return data for these tags.

If the value data type is omitted, then PARCview will attempt to figure out a tag’s data type by examining the Value field type returned by one of the queries that returns data.

Limit

The Limit script should return the following fields, accessed by index:

The Limit script should return a single row that specifies the Limits and Target for the given limit type and grade. This script uses the predefined tokens {LimitType}, {BasisValue} and {BasisTime}.

Read Data Scripts

All read data scripts should return the following fields, accessed by index:

These scripts make use of the {TagId}, {StartTime}, {EndTime} and other time-based tokens to supply values to the queries.

Value

The data type of the Value column can be either text or numeric. If the tag is defined as digital text, then the Value field should contain the string representation, and the numeric equivalent (typically integer) should be returned after the Quality field.

Quality

The Quality column should come back using an OPC HDA valid quality value (Good=&H400C0 or Bad=&H40000), or the alternative OPCDA codes (Good=192, Bad=0, Uncertain=64). If the Value column has a null then the Quality is always considered bad for that row. It is important that the values come back in ascending time order.

Timestamp

Internally, the PARCview data server conforms to the rules of an OPC-compliant Historical Data Access (OPCHDA) server. By specification, a data request between StartTime and EndTime is interpreted as containing the StartTime and up to, but not including the EndTime. In SQL terms this might lead to a query of the form:

This is slightly different than a query that uses the SQL BETWEEN operator, as that would include the EndTime. In practice, however, PARCview’s SQL.NET dataseries is tolerant of users not completely specifying their scripts according to the specifications.

In the Data Prior script, the WHERE clause should always specify Timestamp < '{StartTime}'

In the Data After script, if the Data script has used a BETWEEN clause so that EndTime is included, then the WHERE clause should always specify Timestamp > '{EndTime}'

If the Data script does not return values that include the EndTime, then the Data After script’s WHERE clause should always specify Timestamp >= '{EndTime}'

Date Format

Use the Date/Time Parameter Format section to specify how values returned from the StartTime, EndTime and other time-based tokens should be formatted before being supplied to the query.

Current

The Current script should return a single row that will represent the most recent value  available.

Refresh Delay

Use the refresh delay settings to throttle down frequent requests for current values.  For example, if the refresh delay is 5M, then even if a trend is updating every 10 seconds, it will only run the current query for SQL Tags using this template every 5 minutes. This avoids unnecessary loading of SQL Server data that may not change very frequently.

Data

The Data script will return historical tag values for a given tag from a given start time up to but not including a given end time.

The Data script will also be used to bring back current values if there is not a Current script defined.  It will do this by using the last row returned when passed a start time and end time that represents the last hour.  For performance reasons it is preferable to use the Current script.

Data Prior

The Data Prior script should return a single row that will represent the last value just prior to the StartTime. This value is used for calculating interpolated values at the start time of the query if real values do not exist.

Data After

The Data Prior script should return a single row that will represent the first value just after the EndTime. This value is used for calculating interpolated values at the end time of the query.

Projection

The Projection script returns future or projected data for use in trends. Use the {ProjTime} token to retrieve the current time in the trend for use as the start time of the data query.

For SQL.NET sources, specifying a projection script is enough for PARCview to use the projected data. For other sources, the projection script’s template name must be specified in the source configuration.

Data Plot

The Data Plot script should return a plot-reduced data set for trends. In most cases, PARCview’s OPCHDA server or the source’s native OPCHDA server will calculate the plot-reduced set. It is unlikely that a plot-reduced set will be stored in SQL or calculated using SQL queries.

This script is primarily used by IPSQL-type sources, which use SQL as the interface to access historical data.

Data Aggregate

The Data Aggregate script should return a single aggregated value given an aggregate type and step interval. In most cases, PARCview’s OPCHDA server or the source’s native OPCHDA server will calculate aggregated values. It is unlikely aggregates will be stored in SQL or calculated using SQL queries for arbitrary time intervals.

This script is primarily used by IPSQL-type sources, which use SQL as the interface to access historical data.

Write Data Scripts

These scripts modify data in the target database. Make sure the DSN for this template has the appropriate permissions.

Insert

This script should specify an insert statement to insert a single row for a value in WriteValue at time in WriteTime for the tag id in TagId.

Update

This script should specify an update statement to insert a single row for a value in WriteValue at time in WriteTime for the tag id in TagId.

Delete

This script should specify a delete statement to delete a single record at time in WriteTime for the tag id in TagId.

Delete Range

This script should specify a delete statement to delete all records between StartTime and EndTime for the tag id in TagId.

Evaluate SQL Tag Template

The Evaluate SQL template is used to test the SQL Tag template.

Application Button

Save and Close: Saves the template and closes the window.

Cancel: Closes the window.

Quick Access Toolbar

Right-click options in the application menu or ribbon to add them to the QAT.

Home Ribbon

Parse Template: Builds the list of tokens in the Parameters table.

Create Script: Replaces tokens with test values

Execute Script: Runs the query with test values.

Hide Script: Hides the SQL Script text box.

Horizontal: Moves the SQL Script text box beneath the Template text box.

Word Wrap: Enables word wrapping in the Template and SQL Script text boxes.

Time Parameter Format: Used to specify how values returned from the Start Time, End Time, and other time-based tokens should be formatted before being supplied to the query. Useful if the SQL Server where queries will be executed has a different datetime format than the PC where SQL templates are constructed, e.g. month/day/year vs. day/month/year.

Save and Close: Saves the template and closes the window.

Cancel: Closes the window.

Template, SQL Script, and Parameters

First build the script in the template section using tokens. Then parse the template to build the list of parameters. Add test parameter values, then create the script.

Name: A token value from the template script.

Value: A test value to replace the token.

Execution Results

The Execution Results section will be populated with tags produced by the query after Execute Script has been run.

Messages

Information about the execution and any errors are displayed in the Messages section.