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:

In an Operator Interface List Page. A SELECT Statement defines what is displayed in the list.
In a Text Report Tag. You can run an SQL query and insert the result set at any point in a report.
In a PROGRAM Tag using the SQLCommandFromTagDB Function Block. The SQLCommandFromTagDB Function Block executes an SQL Command on the Fernhill SCADA Database.
When linking data into an external spreadsheet, SQL is used to define what data is transferred to the spreadsheet. The ODBC driver provides the connection between the spreadsheet and Fernhill SCADA.
In commands to access Fernhill SCADA from a common runtime language program using ADO.NET. The Fernhill SCADA .NET Framework Data Provider provides the link between the client program and Fernhill SCADA.
In commands to access Fernhill SCADA from a C/C++ program using ODBC. See Using ODBC from C/C++.
In commands to access Fernhill SCADA from a Python program using ODBC. See Using ODBC from Python.
The SQL Test Tool uses SQL to define what to display.

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 Name	Description
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 Name	Description
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:

State	Alarm State
0	Disabled, Inactive, Not acknowledged.
1	Enabled, Inactive, Not acknowledged.
2	Disabled, Active, Not acknowledged.
3	Enabled, Active, Not acknowledged.
4	Disabled, Inactive, Acknowledged.
5	Enabled, Inactive, Acknowledged.
6	Disabled, Active, Acknowledged.
7	Enabled, 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
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

Fernhill SCADA .NET Framework Data Provider

To learn about the .NET Framework Data Provider for 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.