Structured Query Language (SQL)

Help Contents


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 Historical Data

To retrieve historical data for an Analog Data Point Tag:

SELECT ValueTime,Value FROM HistoricDataViewLREAL AS H
ON T.HistoricDataTable = H.TableName
WHERE T.FullName = 'Example Graphic.Tank Level'

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

View NameDescription
HistoricDataViewBOOL Historical data for Digital Data Point Tags.
HistoricDataViewLREAL Historical data for Analog Data Point Tags.
HistoricDataViewTIME Historical data for Time Series Runtime Tag.
HistoricDataViewUDINT Historical data for Double Word Data Point Tags.
HistoricDataViewUINT Historical data for Word Data Point Tags.
HistoricDataViewULINT Historical data for Long Word Data Point Tags and Time Series Counter Tags.
HistoricDataViewWSTRING Historical 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 see Alarm Conditions.

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

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

ODBC driver

To learn about the ODBC driver in Fernhill SCADA.

SQL Test Tool

To learn about the SQL Test Tool.


For the meaning of terms used in Fernhill SCADA.