Read Data Scripts

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

 

0

Timestamp

1

Value. If tag value data type is digital text, this should be the text value

2

Quality

3 (optional)

If tag value data type is digital text, this should be the numeric value

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:

Text Box: Select ValueTimeStamp, Value, Quality From DataTable Where tagidentifier = '{TagId}' and ValueTimeStamp >= '{StartTime}' and ValueTimeStamp < '{EndTime}' order by ValueTimeStamp ASC

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.

 

Format

Description

d

Day of the month, use single digit when possible.

dd

Day of the month, always use double digits.

ddd

Abbreviated name of the day of the week.

dddd

Full name of the day of the week.

h

The hour using a 12-hour clock, use single digit when possible.

hh

The hour using a 12-hour clock, always use double digits.

H

The hour using a 24-hour clock, use single digit when possible.

HH

The hour using a 24-hour clock, always use double digits.

m

Minute of the hour, use single digit when possible.

mm

Minute of the hour, always use double digits.

M

Month of the year, use single digit when possible.

MM

Month of the year, always use double digits.

MMM

Abbreviated name of the month.

MMMM

Full name of the month.

s

Second of the minute, use single digit when possible.

ss

Second of the minute, always use double digits.

t

Just the first character of the AM/PM label.

tt

The full AM/PM label.

yy

The year as a two-digit number.

yyyy

The year as a four-digit number.

:

The time separator.

/

The date separator.

 

Current

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

Text Box: use ctc_data_training; declare @tagid int; declare @datatype int; select @tagid = t.tag_id, @datatype = t.tag_datatype from ctc_tag t join ctc_source s on s.source_id = t.source_id where t.tag_name = '{TagId}' and upper(source_name) = upper('{Source}'); if @datatype = 4 --numeric select top 1 data_ts, data_val, data_qual from ctc_data where tag_id = @tagid and data_ts < getdate() order by data_ts desc; else select top 1 data_ts, data_text, data_qual from ctc_data where tag_id = @tagid and data_ts < getdate() order by data_ts desc;

 

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.

Text Box: use ctc_data_training; declare @tagid int; declare @datatype int; select @tagid = t.tag_id, @datatype = t.tag_datatype from ctc_tag t join ctc_source s on s.source_id = t.source_id where t.tag_name = '{TagId}' and upper(source_name) = upper('{Source}'); if @datatype = 4 --numeric select data_ts, data_val, data_qual from ctc_data where tag_id = @tagid and data_ts between '{StartTime}' and '{EndTime}' and data_ts < getdate() order by data_ts; else select data_ts, data_text, data_qual from ctc_data where tag_id = @tagid and data_ts between '{StartTime}' and '{EndTime}' and data_ts < getdate() order by data_ts;

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.

Text Box: use ctc_data_training; declare @tagid int; declare @datatype int; select @tagid = t.tag_id, @datatype = t.tag_datatype from ctc_tag t join ctc_source s on s.source_id = t.source_id where t.tag_name = '{TagId}' and upper(source_name) = upper('{Source}'); if @datatype = 4 --numeric select top 1 data_ts, data_val, data_qual from ctc_data where tag_id = @tagid and data_ts < '{StartTime}' and data_ts < getdate() order by data_ts desc; else select top 1 data_ts, data_text, data_qual from ctc_data where tag_id = @tagid and data_ts < '{StartTime}' and data_ts < getdate() order by data_ts desc;

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.

Text Box: use ctc_data_training; declare @tagid int; declare @datatype int; select @tagid = t.tag_id, @datatype = t.tag_datatype from ctc_tag t join ctc_source s on s.source_id = t.source_id where t.tag_name = '{TagId}' and upper(source_name) = upper('{Source}'); if @datatype = 4 --numeric select top 1 data_ts, data_val, data_qual from ctc_data where tag_id = @tagid and data_ts > '{EndTime}' and data_ts < getdate() order by data_ts asc; else select top 1 data_ts, data_text, data_qual from ctc_data where tag_id = @tagid and data_ts > '{EndTime}' and data_ts < getdate() order by data_ts asc;

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.

Text Box: SELECT ts, value, CASE status WHEN 0 THEN 192 WHEN 4 THEN 192 ELSE 0 END FROM history WHERE name = '{tagid}' and ts between CAST( '{starttime}' AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS') AND CAST( '{endtime}' AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS') AND REQUEST = 3 AND PERIOD = TRUNC (DELTA_TIME(CAST( '{endtime}' AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS'), CAST( '{starttime}' AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS')) / {resolution}) ORDER BY ts ASC

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.

Text Box: SELECT ts_start, {if: {AggregType} = "Average" then: "avg"} {if: {AggregType} = "Minimum" then: "min"} {if: {AggregType} = "Maximum" then: "max"} {if: {AggregType} = "StDev" then: "std"} {if: {AggregType} = "Total" then: "sum"} {if: {AggregType} = "Range" then: "rng"} {if: {AggregType} = "Variance" then: "var"}, CASE status WHEN 0 THEN 192 WHEN 4 THEN 192 ELSE 0 END FROM aggregates WHERE name = '{tagid}' and ts between CAST( '{starttime}' AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS') AND CAST( '{endtime}' AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS') AND REQUEST = 1 AND PERIOD = {StepInterval} * 10 ORDER BY ts ASC