Using ODBC from C/C++

Introduction

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

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

The data retrieved from Fernhill SCADA could be from any of the following:

The structure of this example program is:

  1. Use the SQLAllocHandle to create an environment handle
  2. Use the SQLSetEnvAttr to set the environment to ODBC Version 3
  3. Use the SQLAllocHandle to create a database handle handle
  4. Use the SQLDriverConnect to connect to Fernhill SCADA
  5. Use the SQLAllocHandle to allocate a statement handle
  6. Use the SQLBindParameter to bind any parameters
  7. Use the SQLExecDirect to execute an SQL statement
  8. Use the SQLFetch to fetch the first row
  9. Use the SQLGetData to get data from the first row

Header File References

Typically these header file references are required to reference the symbols and function prototypes of ODBC:

#include <sql.h>
#include <sqlext.h>

Establishing a connection

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

// Allocate an environment handle
SQLHANDLE hEnv;
SQLRETURN Ret = ::SQLAllocHandle( SQL_HANDLE_ENV, 0, &hEnv );
if ( Ret == SQL_SUCCESS )
{
    // Specify 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
        SQLHANDLE hDbc;
        Ret = ::SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
        if ( Ret == SQL_SUCCESS )
        {
            // Build a connection string
            _TCHAR ConnectionStringIn[256];
            _tcscpy_s( 
                    ConnectionStringIn, 
                    256, 
                    _T("Driver={Fernhill SCADA};Server=localhost") );

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

                // Disconnect
                ::SQLDisconnect( hDbc );
            }

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

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

Executing a Command with a Parameter

The QueryTagValue function referenced in the previous code fragment is:

static void QueryTagValue( SQLHANDLE hDbc, _TCHAR *TagName )
{
    // Allocate a statement
    SQLHANDLE hStmt;
    SQLRETURN Ret = ::SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
    if ( Ret == SQL_SUCCESS )
    {
        // Build the command
        _TCHAR Command[256];
        _tcscpy_s( Command, 256, _T("SELECT Value FROM TagDataPointAnalog WHERE FullName = ?") );
        // Bind the parameter
        Ret = ::SQLBindParameter(
                hStmt,
                1,
                SQL_PARAM_INPUT,
                SQL_C_TCHAR,
                SQL_VARCHAR,
                0,
                0,
                TagName,
                0, 0 );
        if ( Ret == SQL_SUCCESS )
        {
            // 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
                    double Value;
                    Ret = ::SQLGetData(
                            hStmt,
                            1,
                            SQL_C_DOUBLE,
                            &Value,
                            sizeof( Value ),
                            0 );
                    if ( Ret == SQL_SUCCESS )
                    {
                        _tprintf( _T("Value = %g\n"), Value );
                    }
                }
            }
        }
        ::SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
    }
}

Further Information

Fernhill SCADA Structured Query Language (SQL)

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

Setting up an ODBC Data Source Name (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.