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:
These examples show common queries to run on the SCADA database.
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.
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. |
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. |
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.
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
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.
Fernhill SCADA .NET Framework Data Provider
To learn about the .NET Framework Data Provider for Fernhill SCADA (FernhillSoftware.Data.FernhillScadaClient).
To learn about the ODBC driver in Fernhill SCADA.
To learn about the SQL Test Tool.
For the meaning of terms used in Fernhill SCADA.