SQLCommand Data Object

Introduction

An SQLCommand Data Object represents an SQL Command on a data source. The SQLCommand Data Object can represent:

A query that returns a record set.
A DML statement that changes data in a table.

Creating an SQLCommand Data Object

How you create an SQLCommand Data Object depends on where the iEC 61131-3 code is running.

Create an SQLCommand Data Object in Program Tag

To create an SQLCommand Data Object in a Program Tag use one of these function blocks:

Function Block	Description
SQLCommandFromODBC	Creates an SQLCommand Data Object on an external database.
SQLCommandFromTagDB	Creates an SQLCommand Data Object on the Fernhill SCADA database.

Create an SQLCommand Data Object in an Operator Action

To create an SQLCommand Data Object in an Operator Action use one of these functions:

Function	Description
SQLCommandFromTagDB	Creates an SQLCommand Data Object on the Fernhill SCADA database.

SQLCommand Functions

Once created, use these functions to access an SQLCommand Data Object:

Function	Description
SQLCommandColumnCount	Returns the number of columns in an SQLCommand data object.
SQLCommandFetchRow	Fetches the next row in an SQLCommand data object.
SQLCommandGetValue	Returns the value of a column in the current row if an SQLCommand data object.
SQLCommandIsNullValue	Returns whether a column is null in the current row of an SQLCommand data object.
SQLCommandRowCount	Returns the number of rows in an SQLCommand data object.

Examples

Code for an Operator Action showing the Number of Tags in the database:

VAR CommandId, Count : DINT; END_VAR

(* Create the SQLCommand Data Object *)
CommandId := SQLCommandFromTagDB('SELECT COUNT(*) FROM TagCore');

(* Fetch the first row and if successful... *)
IF SQLCommandFetchRow( CommandId ) THEN

    (* Get the count *)
    Count := TO_DINT( SQLCommandGetValue( CommandId, 0 ) );
    
    (* Display the count *)
    MessageDialog( 
        'Tag Count', 
        CONCAT( 'Tag Count = ', TO_STRING( Count ) ) );
END_IF

Code for a Program Tag writing the number of tags in the database to another tag:

Program Main
    Var
        Timer : TON;
        SqlCmd : SQLCommandFromTagDB;
        Count AT %".Tag Count" : WORD;
    End_Var

    (* Run the TON function block *)
    Timer( IN:=NOT Timer.Q, PT:=T#30s );
    
    (* Run the SQLCommandFromTagDB block when the timer completes *)
    SqlCmd( 
        Req:= Timer.Q,
        SQL:='SELECT COUNT(*) FROM TagCore' );
    
    (* If the SQL command has completed... *)
    IF SqlCmd.DONE THEN
        
        (* Fetch the first row *)
        IF SQLCommandFetchRow( SqlCmd.CommandId ) THEN
            
            (* Get the value and write to the tag *)
            Count := TO_WORD( SQLCommandGetValue( SqlCmd.CommandId, 0 ) );	
        END_IF
    END_IF

End_Program

Further Information

SQL Language

To learn about the SQL Language supported in Fernhill SCADA.

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.

Operator Action Editor

To learn about editing Operator Actions.

Glossary

For the meaning of terms used in Fernhill SCADA.