Chapter 2. Tutorials

This script creates and inserts a new row into a database whenever a trigger point changes value. The data that gets inserted into the row is an ID for the entry (the primary key), the value of a specified point, the timestamp of the change, and the name and quality of the point.

The tutorials in this manual use SQL commands to query the database. The syntax for these commands may vary slightly from one ODBC database to another. If a given tutorial doesn't work right away, check the syntax of the SQL commands used here against the syntax that your database uses.

Getting Started

To run this code or the other tutorials in this manual, you will need to do the following:

The Code: ODBCTutorial1.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 ODBCTutorial1 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 ODBCTutorial1.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 ODBCTutorial1.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 ODBCTutorial1.AddRecord (sym, newvalue)
{
    local    record = new DataEntry();
    .FillRecord (record, sym, newvalue);
    .conn.Insert (record);
    record;
}

/* The mainline.  Connect to the database and begin storing data from
   the DataHub into the database. */
method ODBCTutorial1.constructor ()
{
    local    ret;
	
    /* Connect to the DSN. */
    .Connect();
	
    /* Add a record when a point changes.  */
    .OnChange (#$DataSim:Square, `(@self).AddRecord (#$DataSim:Sine, $DataSim:Sine));
    
    /* Add more points like this:
     *   .OnChange (#$DataSim:Square, `(@self).AddRecord (#$MyDomain:MyPt, $MyDomian:MyPt));
     * Have the trigger point's value get written like this:
     *   .OnChange (#$DataSim:Square, `(@self).AddRecord (#$DataSim:Square, $DataSim:Square));
     */  
}

/* Any code to be run when the program gets shut down. */
method ODBCTutorial1.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 (ODBCTutorial1);

Modifying the Code

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