/*
* This script demonstrates the use of the threaded ODBC interface
* to insert data into a database based on a timer.
*/
require ("Application");
require ("ODBCThreadSupport");
require ("Time");
require ("Quality");
class ODBCThreadDemo Application
{
DSN = "MySQLLocal"; // The DSN name to use for the
// database connection
username = "test"; // The user name for connecting to
// the database
password = "test"; // The password for connecting to
// the database
tablename = "table1"; // The name of the database table
cachefile = "c:/tmp/testcache.txt"; // Base name for the
// disk cache file
tableclass;
thread;
}
/* This method will be called every time the connection is
* established to the database. If there is something we only want
* to perform on the first connection, we can test is_first_connect
* to perform the code only once.
*/
method ODBCThreadDemo.onConnect()
{
princ ("Connection succeeded\n");
if (.thread.is_first_connect)
{
// Start the sequence defined by the AddInitStage calls in
// the constructor
.thread.BeginAsyncInit();
}
}
/* If we get a connection attempt failure, or the connection fails
* after having been connected, this method is called.
*/
method ODBCThreadDemo.onConnectFail()
{
princ ("Connection closed: ", SQLResult.Description, "\n");
}
/* Map the table in the set of table definitions that matches the
* name in .tablename into a Gamma class. This lets us easily
* convert between class instances and rows in the table.
*/
method ODBCThreadDemo.mapTable(name, tabledefinitions)
{
.tableclass = .thread.ClassFromTable(name, tabledefinitions);
}
/* Set up the timer or event handler functions to write to the table.
*/
method ODBCThreadDemo.startLogging()
{
/* You can modify and/or add similar timers or event handlers
* for each data point that you want to log. Please refer to
* the "Methods and Functions from Application.g" section of
* the documentaton for more details about the timer and event
* handler funtions.
*/
// Log a new row of data every 3 seconds.
.TimerEvery(3, `(@self).writeData(#$DataSim:Sine));
// Log a new row of data at 20 seconds past each minute
// of each hour, etc.
.TimerAt(nil, nil, nil, nil, nil, 20,
`(@self).writeData(#$DataSim:Triangle));
// Log a new row of data for the point DataSim:Square when
// it changes.
.OnChange(#$DataSim:Square, `(@self).writeData(this));
// Log a new row of data for the point DataSim:Sine when
// DataSim:Square changes.
.OnChange(#$DataSim:Square, `(@self).writeData(#$DataSim:Sine));
}
method ODBCThreadDemo.writeData(pointsymbol)
{
local row = new (.tableclass);
local pttime, ptltime;
local timestring;
/* Generate a timestamp in database-independent format to the
* millisecond. Many databases strip the milliseconds from a
* timestamp, but it is harmless to provide them in case the
* database can store them.
*/
pttime = WindowsTimeToUnixTime(PointMetadata(pointsymbol).
timestamp);
ptltime = localtime(pttime);
timestring = format("{ts '%04d-%02d-%02d %02d:%02d:%02d.%03d'}",
ptltime.year+1900, ptltime.mon+1,
ptltime.mday, ptltime.hour,
ptltime.min, ptltime.sec,
(pttime % 1) * 1000);
/* Fill the row. Since we mapped the table into a Gamma class,
* we can access the columns in the row as member variables of
* the mapped class.
*/
row.ptname = string(pointsymbol);
row.ptvalue = eval(pointsymbol);
row.pttime = timestring;
/* Perform the insertion. In this case we are providing no
* callback on completion.
*/
.thread.Insert(row, nil);
}
/* Write the 'main line' of the program here. */
method ODBCThreadDemo.constructor ()
{
// Create and configure the database connection object
.thread = new ODBCThread();
.thread.Configure(.DSN, .username, .password,
STORE_AND_FORWARD, .cachefile, 0);
/* Use this to delete the table on the first connection after
* the script starts. BE CAREFUL - re-running the script will
* start over and delete the table again.
*/
// .thread.AddInitStage(format("drop table %s", .tablename),
// nil, t);
/* Use this to create the table if it does not exist. Note: this
* might not work for all databases. When in doubt, create the
* table manually. The 't' in the onFail argument says to ignore
* errors and continue with the next stage.
*/
// thread.AddInitStage(format
// ("create table %s (ptid int
// auto_increment primary key, ptname
// varchar(64), ptvalue double, pttime
// datetime )", .tablename), nil, t);
/* Query the table and map it to a class for eash insertion.
* We want to run an asynchronous event within the asynchronous
* initialization stage, so to do that we specify the special
* method cbInitStage as the callback function of our asynchronous
* event (GetTableInfo). We deal with the return from the
* GetTableInfo in the onSuccess argument of the init stage.
*/
.thread.AddInitStage(`(@.thread)
.GetTableInfo("", "",
(@.tablename),
"TABLE,VIEW",
`(@.thread).cbInitStage()),
`(@self).mapTable(@.tablename,
SQLTables),
nil);
/* Do not start writing data to the table until we have
* successfully created and mapped the table to a class. If we
* wanted to start writing data immediately, then we would create
* the table class beforehand instead of querying the database
* for the table definition. Then, even if the database were
* unavailable we could still cache to the local disk until the
*database was ready.
*/
.thread.AddInitStage(nil, `(@self).startLogging(), nil);
/* Set up the callback functions for various events from the
* database thread
*/
.thread.OnConnectionSucceeded = `(@self).onConnect();
.thread.OnConnectionFailed = `(@self).onConnectFail();
.thread.OnFileSystemError = `princ("File System Error: ",
SQLResult, "\n");
.thread.OnODBCError = `princ("ODBC Error: ", SQLResult, "\n");
.thread.OnExecuteStored = nil;
/* Now that everything is configured, start the thread and begin
* connecting. All of the logic now will be driven through the
* onConnect callback and then through the init stages.
*/
.thread.Start();
}
/* Any code to be run when the program gets shut down. */
method ODBCThreadDemo.destructor ()
{
}
/* Start the program by instantiating the class. */
ApplicationSingleton (ODBCThreadDemo);