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

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

	Although DataHub Scripting is included with every Cogent DataHub license, ODBC scripting requires that the Cogent DataHub Database feature be licensed as well.

	The code for this and other example scripts can be found in the DataHub distribution archive, typically at this location: C:\Program Files\Cogent\Cogent DataHub\scripts\ Please refer to Chapter 3, An Explanation of the Tutorial Code for more information about the code.

As with Tutorial 2, 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 instance. Any time the value changes in the database, the results get passed to the DataHub instance within a second. The point in the DataHub instance will continue to be updated once every second from the database as long as the two are both running.

The Code: ODBCTutorial4.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 ("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 ODBCTutorial4 Application
{
    /* User-defined values, may be changed as needed. */
    DSN = "DataHubTest";
    user = "test";
    password = "test";
    tablename = "Table1";

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

/* Connect to the DSN and create a class that maps the table. */
method ODBCTutorial4.Connect ()
{
    local   ret;
    .is_connecting = t;

    protect
    {   
        /* Create the ODBC environment and connection */
        if (!.env)
            .env = ODBC_AllocEnvironment();
        if (!.conn)
            .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");
            
     .is_connected = t;
    }
    unwind
    {
        .is_connecting = nil;
        if (.is_connected)
            princ ("    Connection successful\n");
        else
            princ ("    Connection failed\n");
    }
}

/* Disconnect from the database server */
method ODBCTutorial4.Disconnect ()
{
    if (.conn)
    {
        try
        {
            princ ("Disconnecting database\n");
            .conn.Disconnect();
            destroy(.conn);
        }
        catch
        {
            princ ("Disconnection failed: ", _last_error_, "\n");
        }
        .conn = nil;
    }
    .is_connected = nil;
}

/* Try to reconnect to the database if it's not currently
   connected. */
method ODBCTutorial4.Reconnect()
{
    if (!.is_connected && !.is_connecting)
    {
        .Connect();
    }
}

/* Reload information from a database record into the DataHub 
   program.  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 ODBCTutorial4.Update ()
{
    local  result;
    
    if (.is_connected)
    {
        try
        {
            result = .conn.QueryToClass (.tableclass,
					 string ("select * from ", .
						 tablename));
            with x in result do
            {
               datahub_write (x.CTRLNAME, x.CTRLVALUE);
            }
        }
        catch
        {
            /* If the query fails then disconnect and do not try
	       again until a successful connection has been made
	       based on the reconnect timer. */
            princ("Query failed.  Disconnecting - ",
		  _last_error_, "\n");
            .Disconnect();
        }
    }
}

/* The mainline.  Connect to the database and begin storing data from
   the DataHub program into the database. */
method ODBCTutorial4.constructor ()
{
    /* Every second, read the 'control' database, and update the
       values.  This keeps the value in the DataHub instance always 
       in sync with the database.  The timer value can be fractional, 
       such as 0.5 for twice per second.  */
    .TimerEvery (1, `(@self).Update ());
    
    /* Start a timer that will reconnect to the database
       every 5 seconds if the connection has been lost. */
    .TimerEvery(5, `(@self).Reconnect());
    
    /* Try connecting now.  If this fails, the timer will try again
       later. */
    .Reconnect();

}

/* Any code to be run when the program gets shut down. */
method ODBCTutorial4.destructor ()
{
    .Disconnect();
    if (.env)
        destroy(.env);
}

/* 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 (ODBCTutorial4);