SQLCommandFromODBC Function Block

Help Contents

Introduction

The SQLCommandFromODBC Function Block executes an SQL Command on an external ODBC database.

The function block provides status information to:

Functions and Function Blocks

The SQLCommandFromODBC 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.

Syntax

The SQLCommandFromODBC Function Block has this syntax:

SQLCommandFromODBC(
        REQ:=Expression,
        CONNSTR:=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 Command.
CONNSTR STRING Input The CONNSTR parameter tells the ODBC Driver Manager how to connect to the ODBC Data Source. It consists of a set of property names and values separated by semicolons.

For example to connect to the default instance of Microsoft SQL Server you might use this connection string:

Driver={SQL Server Native Client 10.0};Server=DataStore;UID=OdbcUser;PWD=OdbcPassword

To work out what to put in a connection string for a specific database:

  • Consult the documentation for the ODBC driver for your target database.
  • Try the website https://www.connectionstrings.com/. This is an independent resource dedicated to providing connection string information.
  • Search existing questions on Stack Overflow. If you cannot find the answer you can always ask your own question.
SQL STRING Input The SQL Command to execute on the external ODBC Database.
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.
ERRORFN WSTRING Output If ERROR is TRUE, the ODBC Function call that returned an error.
ERRORMSG WSTRING Output If ERROR is TRUE, the reason why the SQL Command failed.

Remarks

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.

Examples

  1. Query an external database using a DSN.
    Var_Global Constant
        (* ODBC Connection string *)
        ConnectionString : STRING := 'DSN=MicrosoftSQLServer';
    
        (* SQL Command to execute on the external database *)
        CommandText : STRING := 'SELECT Id FROM dbo.Table_1';
    End_Var
    
    Program Main
    
        Var
            (* The function block to run a command on an external database *)
            ODBCCommand : SQLCommandFromODBC;
            
            (* The tag that triggers the SQL Command to start *)
            TriggerTag AT %".StartQuery": BOOL;
            
            (* Information returned to the tag database *)
            RowCount AT %".RowCount": UDINT;
        End_Var
    
        (* Execute the SQLCommandFromODBC function block *)
        ODBCCommand(
            REQ:=TriggerTag,
            CONNSTR:=ConnectionString,
            SQL:=CommandText );
        
        IF ODBCCommand.DONE THEN
    
            (* Was there an error ? *)
            IF ODBCCommand.ERROR THEN
                (* Handle any error *)
            ELSE
                (* Get the number of rows returned by the command *)
                RowCount := SQLCommandRowCount(ODBCCommand.COMMANDID);
    
                (* Any additional work on successful completion *)
            END_IF;
            
            (* Reset the original trigger *)
            TriggerTag := FALSE;
        END_IF
    End_Program
    

Standards Compliance

The SQLCommandFromODBC Function Block is an extension to the IEC 61131-3 standard.

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.