The SQLCommandFromTagDB Function Block executes an SQL Command on the Fernhill SCADA database.
The function block provides status information to:
The SQLCommandFromTagDB Function Block is intended for use in a Program Tag that runs on the Fernhill SCADA Server. A Program Tag uses a continuous execution model, where the program code runs at a fixed interval. A program uses the function block to sample the state of the SQL Command as it executes.
If you want to run an SQL Command from an Operator Action, use the SQLCommandFromTagDB Function.
The SQLCommandFromTagDB Function Block has this syntax:
SQLCommandFromTagDB( REQ:=Expression, SQL:=Expression, BUSY=>Variable-Reference, COMMANDID=>Variable-Reference, DONE=>Variable-Reference, ERROR=>Variable-Reference, ERRORMSG=>Variable-Reference );
Note: All parameters are optional.
Parameter | Type | Direction | Description |
---|---|---|---|
REQ | BOOL R_EDGE | Input | A rising edge triggers the the execution of the SQL Command. |
SQL | STRING | Input | The SQL Command to execute. See Fernhill SCADA Structured Query Language (SQL) for the syntax of this parameter. |
BUSY | BOOL | Output | Set to TRUE while the SQL Command is in progress. |
COMMANDID | DINT | Output | When the DONE output is TRUE, this output parameter contains the identifier of the SQLCommand Object. |
DONE | BOOL | Output | Set to TRUE for 1 execution after the SQL Command has completed. |
ERROR | BOOL | Output | Set to TRUE if there was an error executing the SQL Command. |
ERRORMSG | WSTRING | Output | If ERROR is TRUE, the reason why the SQL Command failed. |
Once the SQL Command begins, additional requests from the input signal REQ are ignored until the SQL Command completes.
These timing diagrams show the relationship of the REQ, BUSY, DONE, and ERROR signals processing two requests. The first request completes with an error, the second request completes normally:
+--------+ +-+ +------+ REQ | | | | | | --+ +-----+ +-+ +------ +------+ +------+ BUSY | | | | --+ +-------+ +---------- ++ ++ DONE || || ---------++-------------++--------- +-------+ ERROR | | ---------+ +-----------------
The BUSY signal is TRUE while the SQL Command is in progress. The DONE signal is TRUE for 1 execution after the completion of the SQL Command.
You can use this function block to execute any valid Fernhill SCADA SQL statement.
Program MAIN VAR SqlText : STRING[1024]; (* The function block declaration *) SqlCMD : SQLCommandFromTagDB; (* The tag used to trigger the write operation Note: Uses a relative tag reference to 'Digital Trigger Tag' located in the same folder as this program *) TriggerTag AT %".Digital Trigger Tag" : BOOL; (* Parameters to store in the table *) EventTime : DATE_AND_TIME; EventDuration : TIME; ExecutionCount : DINT; Comments : WSTRING; (* The number of inserted rows *) RowCount : UDINT; END_VAR EventTime := SysGetDateAndTime(); EventDuration := T#5s; Comments := "Some comments about the event"; (* Build the SQL statement. Use TO_SQL_LITERAL() to simplify conversion of data to SQL Literals *) SqlText := CONCAT( 'INSERT INTO DatabaseTable.ResultsTab1 $n ', '(EventTime, EventDuration, Counter, Comments) VALUES (', TO_SQL_LITERAL(EventTime), ', ', TO_SQL_LITERAL(EventDuration), ', ', TO_SQL_LITERAL(ExecutionCount), ', ', TO_SQL_LITERAL(Comments), ' )' ); (* Call the SQLCommandFromTagDB function block *) SqlCMD( REQ:=TriggerTag, SQL:=SqlText ); (* The DONE output is active for one cycle when the function block completes *) IF SqlCMD.DONE THEN (* Was there an error ? *) IF SqlCMD.ERROR THEN (* Handle any error *) ELSE (* Get the number of inserted rows *) RowCount := SQLCommandRowCount(SqlCMD.COMMANDID); (* Increment the execution count *) ExecutionCount := ExecutionCount + 1; (* Any additional work on successful completion *) END_IF; (* Reset the original trigger *) TriggerTag := FALSE; END_IF; END_PROGRAMNotes on this example:
Var_Global CONSTANT (* This constant sets the number of values to retrieve *) MaxValueCount : INT := 5; End_Var Type ValueAndTimestamp : STRUCT Value : LREAL; Timestamp : DATE_AND_TIME; END_STRUCT; DataValues : ARRAY[1..MaxValueCount] OF ValueAndTimestamp; END_Type Program Main Var (* Timer to trigger other actions every 5 minutes *) ScanTimer : TON; (* SQL Command function block *) FetchDataCommand : SQLCommandFromTagDB; (* The number of values retrieved *) ValueCount : INT; (* Whether there was an error retreiving the data *) ProcessError AT %".ProcessError" : BOOL; (* The most recent values. Stored most recent entry first. Values[1] = Most recent entry Values[MaxValueCount] = Oldest entry *) Values : DataValues; End_Var Var_Temp TagHistory : WSTRING; SQLCommandText : STRING[256]; VTRef : REF_TO ValueAndTimestamp; End_Var (* Process the scan timer. Interval = 5 minutes *) ScanTimer( IN:= NOT ScanTimer.Q, PT:=T#5m ); (* Get the name of the historic data table storing the data *) TagHistory := %".AnalogTag.HistoricDataTable"; (* Construct an SQL command to retrieve the ImbalancePrice: a) Only results stored within the previously calculated interval are included b) Only results with good quality are included c) The results are sorted in descending order of time (most recent value first) d) The query only fetches the first 5 rows *) SQLCommandText := CONCAT( 'SELECT ValueTime,Value FROM ', TagHistory, ' WHERE ValueTime >= Current_Timestamp - Interval $'1$' Hour', ' AND Quality >= 192 ', 'ORDER BY ValueTime DESC FETCH FIRST 5 ROWS ONLY' ); (* Run the SQLCommandFromTagDB function block. A positive transition of REQ starts the query. When the query completes the DONE output is set to TRUE for 1 cycle. *) FetchDataCommand( REQ:=ScanTimer.Q, SQL:=SQLCommandText ); (* If the query has completed *) IF FetchDataCommand.DONE THEN IF FetchDataCommand.ERROR THEN (* There was an error executing the SQL query *) ProcessError := TRUE; ELSE (* Loop around the result set *) ValueCount := 1; (* While we have MaxValueCount or fewer items AND the SQL query provided a row *) WHILE (ValueCount <= MaxValueCount) AND SQLCommandFetchRow( FetchDataCommand.COMMANDID ) DO (* Get a reference to the array entry *) VTRef := REF( Values[ValueCount] ); (* Store the timestamp *) VTRef^.Timestamp := TO_DATE_AND_TIME( SQLCommandGetValue( FetchDataCommand.COMMANDID, 0 ) ); (* Store the value *) VTRef^.Value := TO_LREAL( SQLCommandGetValue( FetchDataCommand.COMMANDID, 1 ) ); (* Move to the next value *) ValueCount := ValueCount + 1; END_WHILE (* Check we have a full data set *) IF ValueCount < MaxValueCount THEN (* Not enough values retrieved to process *) ProcessError := TRUE; ELSE (* Do something with the data in array Values *) (* Clear the error indicator *) ProcessError := FALSE; END_IF END_IF END_IF End_ProgramNotes on this example:
Program MAIN VAR (* The function block declaration *) SqlCMD : SQLCommandFromTagDB; (* The tag used to trigger the query Note: Uses a relative tag reference to 'Digital Trigger Tag' located in the same folder as this program *) TriggerTag AT %".Digital Trigger Tag" : BOOL; (* Some variables to collect the query results *) TagCount : UDINT; TagValue : LREAL; TotalTagValue : LREAL; END_VAR (* Call the SQLCommandFromTagDB function block *) SqlCMD( REQ:=TriggerTag, SQL:="SELECT Value FROM TagDataPointAnalog" ); (* The DONE output is active for one cycle when the function block completes *) IF SqlCMD.DONE THEN (* Was there an error ? *) IF SqlCMD.ERROR THEN (* Handle any error *) ELSE (* SQL Command succesfully executed *) (* Reset some variables *) TagCount := 0; TotalTagValue := 0.0; (* For each row obtained from the SQLCommandFromTagDB... *) WHILE SQLCommandFetchRow( SqlCMD.COMMANDID ) DO (* Get the value of column 1 *) TagValue := TO_LREAL( SQLCommandGetValue( SqlCMD.COMMANDID, 0 ) ); (* Accumulate statistics *) TotalTagValue := TotalTagValue + TagValue; TagCount := TagCount + 1; END_WHILE (* Any additional work on successful completion *) END_IF; END_IF; END_PROGRAM
The SQLCommandFromTagDB Function Block is an extension to the IEC 61131-3 standard.
To learn how to use the SQLCommand data object.
To learn how to use data objects in IEC 61131-3 code.
To learn how IEC 61131-3 is used in Fernhill SCADA.
For the meaning of terms used in Fernhill SCADA.