SQLCommandFromODBC Function Block

Help Contents


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.


The SQLCommandFromODBC Function Block has this syntax:

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


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.


  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';
    Program Main
            (* 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;
        (* Execute the SQLCommandFromODBC function block *)
            SQL:=CommandText );
        IF ODBCCommand.DONE THEN
            (* Was there an error ? *)
            IF ODBCCommand.ERROR THEN
                (* Handle any error *)
                (* Get the number of rows returned by the command *)
                RowCount := SQLCommandRowCount(ODBCCommand.COMMANDID);
                (* Any additional work on successful completion *)
            (* Reset the original trigger *)
            TriggerTag := FALSE;
  2. Insert a record into a table in a database:
    Program Main
            Trigger AT %".Trigger" : BOOL;
            SQLText : STRING[1024];
            EventTime : DATE_AND_TIME;
            FlowRate,Temperature,Pressure : LREAL;
            Cmd : SqlCommandFromODBC;
        (* Get the data values to insert *)
        EventTime := SysGetDateAndTime();
        FlowRate := %".srcFlowRate";
        Temperature := %".srcTemperature";
        Pressure := %".srcPressure";
        (* Construct the SQL statement.
           Note: FORMAT_DATE_TIME() is used to generate an ISO 8601 timestamp literal.
                 TO_SQL_LITERAL() is used to convert values to SQL Literals *)
        SQLText := 
                'INSERT INTO TestDB.dbo.Measurements (EventTime,FlowRate,Temperature,Pressure)',
                'VALUES (',
                '$'',FORMAT_DATE_TIME( EventTime, 'yyyy-MM-ddTHH:mm:ss.SSS' ),'$'',
                TO_SQL_LITERAL( FlowRate ),
                TO_SQL_LITERAL( Temperature ),
                TO_SQL_LITERAL( Pressure ),
                ')' );
        (* Run the SqlCommandFromODBC function block. 
           When Trigger transitions from FALSE to TRUE, the insert statement will start.
           Note: The actual UID and PWD values have been redacted *)
            CONNSTR:="Driver={SQL Server};Server=FHDEV;UID=******;PWD={*******}",
            SQL:=SQLText );
        (* If the SQL Command is complete... *)
        IF Cmd.DONE THEN
            (* Reset the trigger *)

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.


For the meaning of terms used in Fernhill SCADA.