SqlCommandFromTagDB Function Block

Help Contents

Introduction

The SqlCommandFromTagDB function block executes an SQL Statement on the Fernhill SCADA database.

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

  1. 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:
    1. A FALSE to TRUE transition of a digital data point tag used to trigger the SQL statement.
    2. The SQL statement inserts a new record into a Database Table Tag.
    3. The SQL statement is complete when the DONE output is TRUE.

  2. 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:
    1. A TON Function Block triggers the query every 5 minutes.
    2. An Analog Data Point Tag called AnalogTag in the same folder as the program tag is the data source.
    3. A Digital Data Point Tag called ProcessError is set to TRUE if there is an error retrieving the data.

  3. 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.