Structured Query Language (SQL)

Help Contents

Introduction

SQL is a language for managing data in relational databases. In Fernhill SCADA you can use SQL in these areas:

Fernhill SCADA supports these SQL commands:

Example SCADA Queries

These examples show common queries to run on the SCADA database.

Read all Data Point Tags

To retrieve all Data Point Tags:

SELECT FullName,DisplayValue,"Timestamp",DataSource,TypeLabel
FROM TagDataPoint

The DisplayValue column is a string column that shows the value appropriate to the type of data point tag.

Read all Analog Data Point Tags

To retrieve all Analog Data Point Tags:

SELECT FullName,Value,"Timestamp",DataSource,TypeLabel
FROM TagDataPointAnalog

To retrieve other types of data point tag use one these tables:

Table NameDescription
TagDataPointAnalog All Analog Data Point Tags.
TagDataPointDigital All Digital Data Point Tags.
TagDataPointDword All Double Word Data Point Tags.
TagDataPointLword All Long Word Data Point Tags.
TagDataPointString All String Data Point Tags.
TagDataPointword All Word Data Point Tags.

Read Historic Data

To retrieve historic data for an Analog Data Point Tag:

SELECT ValueTime,Value FROM HistoricDataViewLREAL AS H
INNER JOIN TagDataPoint AS T
ON T.HistoricDataTable = H.TableName
WHERE T.FullName = 'Example Graphic.Tank Level'
ORDER BY ValueTime DESC
FETCH FIRST 1000 ROWS ONLY

The HistoricDataViewLREAL view provides a single view of all analog historic data. To retrieve other types of data use one of these views:

View NameDescription
HistoricDataViewBOOL Historic data for Digital Data Point Tags.
HistoricDataViewLREAL Historic data for Analog Data Point Tags.
HistoricDataViewTIME Historic data for Time Series Runtime Tag.
HistoricDataViewUDINT Historic data for Double Word Data Point Tags.
HistoricDataViewUINT Historic data for Word Data Point Tags.
HistoricDataViewULINT Historic data for Long Word Data Point Tags and Time Series Counter Tags.
HistoricDataViewWSTRING Historic data for String Data Point Tags.

Read Active Alarms

To retrieve all active alarms:

SELECT SourceName,ConditionName,UpdateTime,Message,Severity,StateAsText AS "State"
FROM Alarms
WHERE State = 1 OR State = 3 OR State = 7

The State column combines the enabled, active and acknowledged state of the alarm:

StateAlarm State
0Disabled, Inactive, Not acknowledged.
1Enabled, Inactive, Not acknowledged.
2Disabled, Active, Not acknowledged.
3Enabled, Active, Not acknowledged.
4Disabled, Inactive, Acknowledged.
5Enabled, Inactive, Acknowledged.
6Disabled, Active, Acknowledged.
7Enabled, Active, Acknowledged.

For more information on alarms see the Alarm Handling Concepts.

Read Recent Events

To retrieve the most recent events:

SELECT Severity,EventTime,Source,Message,EventType,UserName
FROM EventLog
WHERE EventTime >= Current_Timestamp - Interval '1' Day
ORDER BY EventTime Desc
FETCH FIRST 250 ROWS ONLY

Schema Information

You can explore the data schema in Fernhill SCADA using the SQL Test Tool. Choose an option from the File, Open Schema menu to browse the schema information.

Further Information

.NET Framework Data Provider

To learn about the .NET Framework Data Provider in Fernhill SCADA (FernhillSoftware.Data.FernhillScadaClient).

ODBC driver

To learn about the ODBC driver in Fernhill SCADA.

SQL Test Tool

To learn about the SQL Test Tool.

Glossary

For the meaning of terms used in Fernhill SCADA.