Implementation details - j5 - 28.0 - Administration & Configuration - Hexagon

j5 IP.21 Connector Configuration

Language
English
Product
j5
Search by Category
Administration & Configuration
j5 Version
2019

This section lists the SQL statements used to retrieve data from IP.21. A question mark (?) denotes a parameter passed in. A variable use (for example, $TAGNAME) denotes where a value is formatted into the SQL statement.

IP_AnalogDef, IP_AnalogDblDef And Extra Analog Tables Current Value Read

SELECT name, ip_input_value, ISO8601(ip_input_time) AS ip_input_time,

ip_input_quality, ip_alarm_state, ip_eng_units,

ip_description, ip_high_limit, ip_low_limit

FROM IP_AnalogDef WHERE name = ?

IP_DiscreteDef and Extra Discrete Tables Current Value Read

SELECT name, ip_input_value, ISO8601(ip_input_time) AS ip_input_time,

ip_input_quality, ip_alarm_state,

ip_description, ip_high_limit, ip_low_limit

FROM IP_DiscreteDef WHERE name = ?

IP_TextDef and Extra Text Tables Current Value Read

SELECT name, ip_input_value, ISO8601(ip_input_time) AS ip_input_time,

ip_input_quality, ip_description

FROM IP_TextDef WHERE name = ?

Current Value Multiple Reads

As above, but the WHERE clause is

WHERE NAME IN (?,?,?,?)

Historical Value Write

INSERT INTO "$TAGNAME"(ip_trend_time, ip_trend_value) VALUES(?, ?)

Historical Value Read

SELECT a.name, a.ip_trend_value as ip_input_value, ISO8601(a.ip_trend_time) as ip_input_time,

a.ip_trend_qlevel as ip_input_quality, a.ip_description

FROM "$TAGNAME" a

WHERE a.ip_trend_time = (

SELECT MAX(b.ip_trend_time) FROM "$TAGNAME" b

WHERE b.ip_trend_time <= ? AND ip_trend_qlevel = 'Good')

Historical Data Changes Read

SELECT name, ip_trend_value as ip_input_value, ISO8601(ip_trend_time) as ip_input_time,

ip_trend_qlevel as ip_input_quality, ip_description

FROM "$TAGNAME"

WHERE ip_trend_time BETWEEN ? AND ?

ORDER BY ip_trend_time asc

Historical Data Changes Read Multiple

SELECT name, ip_trend_value as ip_input_value, ISO8601(ip_trend_time) as ip_input_time,

ip_trend_qlevel as ip_input_quality, ip_description

FROM "IP_AnalogDef"

WHERE ip_trend_time BETWEEN ? AND ?

AND name IN (?,?,?,?,?)

ORDER BY ip_trend_time asc

Aggregate Read (not TRENDSUM)

SELECT AGGREGATES.NAME as name, "$AGGREGATE" as ip_input_value,

ISO8601(ts) as ip_input_time, AGGREGATES.STATUS as ip_input_quality,

ip_description

FROM AGGREGATES JOIN "IP_AnalogDef" ON AGGREGATES.NAME = "IP_AnalogDef".NAME

WHERE AGGREGATES.NAME = ? AND TS BETWEEN ? AND ?

AND REQUEST = ? AND PERIOD = DELTA_TIME(?, ?)

Aggregate Read (TRENDSUM)

SELECT '$TAGNAME' as name, SUM(IP_TREND_VALUE) as ip_input_value,

'$TIMESTAMP' as ip_input_time, 'Good' as ip_input_quality

FROM "$TAGNAME"

WHERE IP_TREND_TIME >= ? AND IP_TREND_TIME < ?

AND IP_TREND_QLEVEL = 'Good'