Using ODBC from C/C++

1. Fernhill SCADA
2. Help
3. Data Access
4. Open Database Connectivity (ODBC)
5. Using ODBC From C/C++

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:

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:

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:

cl ODBCReadAnalogTag.c odbc32.lib

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

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.

Fernhill SCADA Version 3.99 (20230929.1). Copyright © 2012-2023 Fernhill Software Ltd: All rights reserved.