Using ODBC from C/C++

Help Contents

Introduction

You can use C/C++ and ODBC to access data in Fernhill SCADA. This article shows you how create a C/C++ program to do the following:

  1. Connect to Fernhill SCADA using ODBC.
  2. Execute an SQL query.
  3. Retrieve the results of the SQL query.

You can find the complete source code for this example in the file ODBCReadAnalogTag.c, which can be found in the Data Access Examples folder.

Locating the Data Access Examples Folder

Fernhill SCADA places a shortcut to the Data Access Examples on the Windows Start Menu. Use this table to find the shortcut based on the Version of Windows you are using:

Windows VersionShortcut Location
Windows 11
  1. Open the Start menu.
  2. Click All apps
  3. Scroll down and expand Fernhill SCADA Operator Tools.
Windows 10
Windows Server 2022
Windows Server 2019
Windows Server 2016
  1. Open the Start menu.
  2. Scroll down and expand Fernhill SCADA Operator Tools.
Windows 8
Windows Server 2012
  1. Open the Start screen.
  2. Click the down arrow.
  3. Scroll right until you see the section Fernhill SCADA Operator Tools
Windows 7
Windows Server 2008
  1. Click the Start button.
  2. Select All Programs.
  3. Expand the folder Fernhill SCADA Operator Tools.

For Linux distributions, the Data Access Examples can be found here:

/usr/share/fernhill-scada/data-access-examples

Development Environment

To compile the sample code, you will need a suitable development environment:

Operating System Development Environment
Windows Microsoft Visual Studio
Linux Requires the build-essential and unixodbc-dev packages. Use these commands to install these packages:
sudo apt-get update
sudo apt-get install build-essential
sudo apt-get install unixodbc-dev

Build the Sample Code

Choose the appropriate method to build the sample code depending on your operating system:

Operating System Build Commands
Windows Open a Developer Command Prompt for Visual Studio, then run this command:
cl ODBCReadAnalogTag.c odbc32.lib
Linux Open a Terminal, then run this command:
gcc ODBCReadAnalogTag.c -lodbc -o ODBCReadAnalogTag

Header File References

To compile this example, and reference ODBC functions, these header files are used:

#include <>
#include <>

#if defined(_WIN32)
#include <>
#endif
#include <>
#include <> 

Establishing a connection

You can use the SQLAllocHandle, SQLSetEnvAttr, and SQLDriverConnect to establish an ODBC connection to Fernhill SCADA:

int main( int argc, char **argv )
{
    SQLRETURN Ret;
    SQLHANDLE hEnv;
    SQLHANDLE hDbc;
    char ConnectionStringIn[ 256 ];
    SQLSMALLINT ConnectionStringOutLength;
    char ConnectionStringOut[ 256 ];

    // Allocate an environment handle
    Ret = SQLAllocHandle( SQL_HANDLE_ENV, 0, &hEnv );
    if ( Ret == SQL_SUCCESS )
    {
        // Indicate we are an ODBC V3 client
        Ret = SQLSetEnvAttr(
            hEnv,
            SQL_ATTR_ODBC_VERSION,
            (SQLPOINTER) SQL_OV_ODBC3,
            0 );
        if ( Ret == SQL_SUCCESS )
        {
            // Allocate a connection handle
            Ret = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
            if ( Ret == SQL_SUCCESS )
            {
                // Build a connection string
                strncpy(
                    ConnectionStringIn,
                    "Driver={Fernhill SCADA};Server=localhost",
		    255 );
		ConnectionStringIn[ 255 ] = '\0';

                printf( "Connecting to Fernhill SCADA (%s)...\n", ConnectionStringIn );

                // Establish the connection
                Ret = SQLDriverConnect(
                    hDbc,
                    0,
                    ConnectionStringIn,
                    SQL_NTS,
                    ConnectionStringOut,
                    256,
                    &ConnectionStringOutLength,
                    SQL_DRIVER_NOPROMPT );
                if ( Ret == SQL_SUCCESS )
                {
                    // Do something with the connection
                    QueryTagValue( hDbc, "PumpStation.TotalCurrent" );

                    // Disconnect
                    SQLDisconnect( hDbc );
                }
                else
                {
                    printf( "... connection failed\n" );

                    ReportOdbcError( 
                        "SQLDriverConnect",
                        Ret,
                        SQL_HANDLE_DBC, 
                        hDbc );
                }

                // Free the connection handle
                SQLFreeHandle( SQL_HANDLE_DBC, hDbc );
            }
        }

        // Free the environment handle
        SQLFreeHandle( SQL_HANDLE_ENV, hEnv );
    }

    return 0;
}

Executing a Command with a Parameter

The QueryTagValue function referenced in the previous code fragment is:

static void QueryTagValue( SQLHANDLE hDbc, char *TagName )
{
    SQLHANDLE hStmt;
    SQLRETURN Ret;
    char Command[ 256 ];
    double Value;

    // Allocate a statement
    Ret = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
    if ( Ret == SQL_SUCCESS )
    {
        // Bind the parameter
        Ret = SQLBindParameter(
            hStmt,
            1,
            SQL_PARAM_INPUT,
            SQL_C_TCHAR,
            SQL_VARCHAR,
            0,
            0,
            TagName,
            0, 0 );
        if ( Ret == SQL_SUCCESS )
        {
            // Build the command
            strncpy( 
		Command, 
		"SELECT Value FROM TagDataPointAnalog WHERE FullName = ?",
	       	255 );
	    Command[255] = '\0';

            // Execute the SQL command
            Ret = SQLExecDirect(
                hStmt,
                Command,
                SQL_NTS );
            if ( Ret == SQL_SUCCESS )
            {
                // Get the first row
                Ret = SQLFetch( hStmt );
                if ( Ret == SQL_SUCCESS )
                {
                    // Get the first column
                    Ret = SQLGetData(
                        hStmt,
                        1,
                        SQL_C_DOUBLE,
                        &Value,
                        sizeof( Value ),
                        0 );
                    if ( Ret == SQL_SUCCESS )
                    {
                        printf( "Value = %g\n", Value );
                    }
                    else
                    {
                        ReportOdbcError( 
                            "SQLGetData()",
                            Ret,
                            SQL_HANDLE_STMT, 
                            hStmt );
                    }
                }
                else
                {
                    ReportOdbcError( 
                        "SQLFetch",
                        Ret,
                        SQL_HANDLE_STMT, 
                        hStmt );
                }
            }
            else
            {
                ReportOdbcError( 
                    "SQLExecDirect",
                    Ret,
                    SQL_HANDLE_STMT, 
                    hStmt );
            }
        }

        SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
    }
}

Error Reporting

This function is used to report ODBC errors:

static void ReportOdbcError( const char *FunctionName, 
                             SQLRETURN FunctionReturnValue, 
                             SQLSMALLINT HandleType, 
                             SQLHANDLE Handle )
{
    SQLTCHAR SqlState[ 6 ];
    SQLSMALLINT iRecord = 1;
    SQLINTEGER NativeError;
    SQLTCHAR ErrorMsg[ 256 ];
    SQLSMALLINT ErrorMsgLen;

    printf( "%s returns %d:\n", FunctionName, FunctionReturnValue );

    SQLRETURN Ret =
        SQLGetDiagRec(
            HandleType,
            Handle,
            iRecord,
            SqlState,
            &NativeError,
            ErrorMsg,
            256,
            &ErrorMsgLen );
    while ( Ret == SQL_SUCCESS )
    {
        printf( "[%s] (%ld): %s", SqlState, NativeError, ErrorMsg );

        ++iRecord;
        Ret =
            SQLGetDiagRec(
                HandleType,
                Handle,
                iRecord,
                SqlState,
                &NativeError,
                ErrorMsg,
                256,
                &ErrorMsgLen );
    }
}

Further Information

Fernhill SCADA Structured Query Language (SQL)

To learn about the structured query language (SQL) supported by Fernhill SCADA.

Setting up an ODBC DSN

To learn about configuring ODBC data Source Names.

Using ODBC Connection Strings to Connect to Fernhill SCADA

To learn about the keywords Fernhill SCADA supports in ODBC connection strings.

Fernhill SCADA ODBC Driver

To learn how the Fernhill SCADA ODBC Driver can provide data to other applications.

Glossary

For the meaning of terms used in Fernhill SCADA.