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'