SqlCommandFromTagDB Function Block

Introduction

The SqlCommandFromTagDB Function Block executes an SQL Statement on the Fernhill SCADA database.

The function block provides status information to:

Monitor the progress of the SQL Statement.
Report whether the SQL Statement succeeded or failed.

Functions and Function Blocks

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 Statement as it executes.

If you want to run an SQL Command from an Operator Action, use the SqlCommandFromTagDB Function.

Syntax

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.

Parameters

Parameter	Type	Direction	Description
REQ	BOOL R_EDGE	Input	A rising edge triggers the the execution of the SQL Statement.
SQL	STRING	Input	The SQL Statement to execute. See Fernhill SCADA Structured Query Language (SQL) for the syntax of this parameter.
BUSY	BOOL	Output	Set to TRUE while the SQL statement 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 statement has completed.
ERROR	BOOL	Output	Set to TRUE if there was an error executing the SQL statement.
ERRORMSG	WSTRING	Output	If ERROR is TRUE, the reason why the SQL statement failed.

Remarks

Once the SQL statement begins, additional requests from the input signal REQ are ignored until the SQL statement 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 statement is in progress. The DONE signal is TRUE for 1 execution after the completion of the SQL statement.

You can use this function block to execute any valid Fernhill SCADA SQL statement.

Examples

Append a record to a Database Table Tag.

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 := SYS_GET_TIMESTAMP();
    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.REQID);

            (* Increment the execution count *)
            ExecutionCount := ExecutionCount + 1;
            
            (* Any additional work on successful completion *)
        END_IF;
        
        (* Reset the original trigger *)
        TriggerTag := FALSE;
    END_IF;

END_PROGRAM

Notes on this example:

A FALSE to TRUE transition of a digital data point tag used to trigger the SQL statement.
The SQL statement inserts a new record into a Database Table Tag.
The SQL statement is complete when the DONE output is TRUE.

Query historic data from an Analog Data Point Tag using a SELECT statement.

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_Program

Notes on this example:

A TON Function Block triggers the query every 5 minutes.
An Analog Data Point Tag called AnalogTag in the same folder as the program tag is the data source.
A Digital Data Point Tag called ProcessError is set to TRUE if there is an error retrieving the data.

Query multiple Analog Data Point Tag values using a SELECT statement.

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 Statement succesfully executed *)
            
            (* Reset some variables *)
            TagCount := 0;
            TotalTagValue := 0.0;

            (* For each row obtained from the SqlCommandFromTagDB... *)
            WHILE SQLCommandFetchRow( SqlCMD.REQID ) DO
                
                (* Get the value of column 1 *)
                TagValue := TO_LREAL( SQLCommandGetValue( SqlCMD.REQID, 0 ) );

                (* Accumulate statistics *)
                TotalTagValue := TotalTagValue + TagValue;
                TagCount := TagCount + 1;
            END_WHILE

            (* Any additional work on successful completion *)
        END_IF;
    END_IF;

END_PROGRAM

Further Information

SQLCommand Data Object

To learn how to use the SQLCommand data object.

Data Objects

To learn how to use data objects in IEC 61131-3 code.

IEC 61131-3

To learn how IEC 61131-3 is used in Fernhill SCADA.

Glossary

For the meaning of terms used in Fernhill SCADA.