This tutorial demonstrates how to keep the DataHub updated every second with the latest values in a database.

This tutorial uses the same DSN and database as Tutorial 1, but creates a different table called "control" (see below). If you haven't done Tutorial 1 yet, please review Getting Started in that section to see how to set up your system for this tutorial.

As with Tutorial 1, you will need to create a table in the database. This new table should be named "control", and should contain at least three columns with names, data types, and other attributes exactly as specified here:

Any other columns in this table must be allowed to take on a null value.

Once this script is running, you can enter the name of any existing DataHub point in a row of the database in the CTRLNAME column. Make sure you enter the full point name, including the domain name, with the syntax domainname:pointname. Enter a corresponding value for the point in CTRLVALUE. The entered value will appear for that point in the DataHub. Any time the value changes in the database, the results get passed to the DataHub within a second. The point in the DataHub will continue to be updated once every second from the database as long as the two are both running.

The complete code for this tutorial is shown below, and is included in your DataHub distribution, in the scripts subdirectory, such as C:\Program Files\Cogent\DataHub\scripts\myscript.g. Please refer to Accessing Scripts in the DataHub Scripting manual for details on how to load and run a script.

The Code: ODBCTutorial3.g

/* All user scripts should derive from the base "Application" class */

require ("Application");

/* Get the Gamma library functions and methods for ODBC and/or
 * Windows programming.  Uncomment either or both. */

//require ("WindowsSupport");
require ("ODBCSupport");

/* Applications share the execution thread and the global name
 * space, so we create a class that contains all of the functions
 * and variables for the application.  This does two things:
 *   1) creates a private name space for the application, and
 *   2) allows you to re-load the application to create either
 *      a new unique instance or multiple instances without
 *      damaging an existing running instance.
 */

/* 
 * This application assumes that the table specified by the "tablename"
 * member variable exists in the DSN specified by the "DSN" member
 * variable below.
 * The table consists of at least the following columns:
 *    ID - integer, identity, non-null, counter
 *    CTRLNAME - text string, null
 *    CTRLVALUE - real, non-null
 * Any other columns in this table must be allowed to take on a 
 * NULL value.
 */
 
class ODBCTutorial3 Application
{
    /* User-defined values, may be changed as needed. */
    DSN = "DataHubTest";
    user = "test";
    password = "test";
    tablename = "control";

    /* These values get defined by the program.*/
        conn;
        env;
        tableclass;
        timerid;
}

/* Connect to the DSN and create a class that maps the table. */
method ODBCTutorial3.Connect ()
{
    local   ret;
	
    /* Create the ODBC environment and connection */
    .env = ODBC_AllocEnvironment();
    .conn = .env.AllocConnection();
	
    /* Attempt the connection. */
    ret = .conn.Connect (.DSN, .user, .password);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
        error (.conn.GetDiagRec());

    /* Create a class from the table */
    .tableclass = .conn.ClassFromTable (#DataEntry, nil, .tablename);
    
    /* Set the primary key.  This is redundant for MS-SQL and MYSQL since
       they can figure it out themselves, but Access requires it. */
    .conn.SetPrimaryKey (.tableclass, "ID");
}

/* Reload information from a database record into the DataHub.  This
   is being called on a timer.  We re-query the database for the
   given record, then update the DataHub points simply by assigning
   them. */
method ODBCTutorial3.Update ()
{
    local  result;
    result = .conn.QueryToClass (.tableclass, string ("select * from ", .tablename));
    with x in result do
    {
       datahub_write (x.CTRLNAME, x.CTRLVALUE);
    }
}

/* The mainline.  Connect to the database and begin storing data from
   the DataHub into the database. */
method ODBCTutorial3.constructor ()
{
    /* Connect to the DSN. */
    .Connect();
	
    /* Every second, read the 'control' database, and update the values.  
       This keeps the value in the DataHub always in sync with the database.
       The timer value can be fractional, such as 0.5 for twice per second.  */
    .timerid = every (1, `(@self).Update ());
}

/* Any code to be run when the program gets shut down. */
method ODBCTutorial3.destructor ()
{
    if (.timerid)
        cancel (.timerid);
}

/* Start the program by instantiating the class.  If your
 * constructor code does not create a persistent reference to
 * the instance (self), then it will be destroyed by the
 * garbage collector soon after creation.  If you do not want
 * this to happen, assign the instance to a global variable, or
 * create a static data member in your class to which you assign
 * 'self' during the construction process.  ApplicationSingleton()
 * does this for you automatically. */
ApplicationSingleton (ODBCTutorial3);