This tutorial demonstrates how to find a particular row and update it, as well as write new rows, depending on the point.

This tutorial uses the same DSN, database, and table as Tutorial 1. 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.

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: ODBCTutorial2.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");
require ("Time");
require ("Quality");

/* 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
 *    PTVALUE - real, non-null
 *    PTTIME - datetime, null
 *    PTNAME - text string, null
 *    PTQUALITY - text string, null
 * Any other columns in this table must be allowed to take on a 
 * NULL value.
 */
 
class ODBCTutorial2 Application
{
    /* User-defined values, may be changed as needed. */
    DSN = "DataHubTest";
    user = "test";
    password = "test";
    tablename = "datatable";

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

/* Connect to the DSN and create a class that maps the table. */
method ODBCTutorial2.Connect ()
{
    /* 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. */
    mykey = .conn.SetPrimaryKey (.tableclass, "ID");
}

/* Fill a database record with new information from a point change */
method ODBCTutorial2.FillRecord (record, sym, newvalue)
{
    local    timestamp;
	
    timestamp = localtime (PointGetUnixTime(sym));
    timestamp = format ("%d-%02d-%02d %02d:%02d:%02d",
        timestamp.year + 1900, timestamp.mon + 1,
        timestamp.mday, timestamp.hour, timestamp.min,
        timestamp.sec);
    record.PTNAME = string (sym);
    record.PTVALUE = number (newvalue);
    record.PTTIME = timestamp;
    record.PTQUALITY = GetQualityName(PointMetadata(sym).quality);
    record;
}

/* Write a new record into the database based on a point change. */
method ODBCTutorial2.AddRecord (sym, newvalue)
{
    local    record = new DataEntry();
    .FillRecord (record, sym, newvalue);
    .conn.Insert (record);
    record;
}

/* Write a data point into a field of a record.  This is called
   from a DataHub point change event.  This method will replace
   an existing record that is cached with the point at startup.  If
   there was no existing row in the database, this will create one
   and then update it in subsequent calls. */
method ODBCTutorial2.UpdateRecord (sym, newvalue)
{
    local    record = getprop (sym, #dbrecord);
    if (!record)
    {
        record = .AddRecord (sym, newvalue);
        setprop (sym, #dbrecord, record);
    }
    else
    {
        .FillRecord (record, sym, newvalue);
        .conn.Update (record);
    }
}

/* Find an existing record in the database for this point.  If it
   exists, associate the record with the point. */
method ODBCTutorial2.GetExistingRecord (sym, klass)
{
    local    result = .conn.QueryToClass (klass, string
                                          ("SELECT * FROM ",
                                           klass.__table,
                                           " WHERE PTNAME = '", sym, "'"));
    if (array_p(result))
    	setprop (sym, #dbrecord, result[0]);
}

/* Start updating the database whenever a point changes.  If the
   overwrite argument is non-nil or absent, then this method will
   cause an existing record in the database to be overwritten each
   time.  If overwrite is nil, every point change will create a
   new row in the database. */
method ODBCTutorial2.WatchPoint (sym, tableclass, overwrite?=t)
{
    /* Grab an existing record for this point if it exists */
    .GetExistingRecord (sym, tableclass);
    if (overwrite)
        .OnChange (sym, `(@self).UpdateRecord (this, value));
    else
        .OnChange (sym, `(@self).AddRecord (this, value));
}

/* The mainline.  Connect to the database and begin storing data from
   the DataHub into the database. */
method ODBCTutorial2.constructor ()
{
    local    ret;
	
    /* Connect to the DSN. */
    .Connect();
	
    /* Register points that we want to save.  The WatchPoint method takes
       an optional third arguement.  If it is nil, every point change will
       add a row to the table.  If it is absent or non-nil, then every point
       change overwrites the existing row in the table for that point. */
    .WatchPoint (#$DataSim:Square, .tableclass, nil);
    .WatchPoint (#$DataSim:Sine, .tableclass);
}

/* Any code to be run when the program gets shut down. */
method ODBCTutorial2.destructor ()
{
}

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

Modifying the Code

There are several ways you can modify the code. These modifications are made in the ODBCTutorial2.constructor method, towards the end of the script.