Chapter 4. Multi-Threaded ODBC Interface

All communication with the database is performed through an instance of the class ODBCThread. For example:

mysql = new ODBCThread();
flags = STORE_AND_FORWARD;
cachefile = "C:/temp/mysql.cache";
mysql.Configure ("myDSN", "username", "password", flags, cachefile);

This code is sufficient to create the database connection. At this point, the database is not connected, and the thread that handles the database has not yet been started.

The next step is to attach the callback functions that will alert the script to various events from the database thread. Whenever a callback function is executed, a special variable called SQLResult will be defined for the duration of the callback. This contains information about the result of the SQL command, a description of the command and error codes. This information is stored in an instance of the class ODBCThreadResult.

A callback is attached by simply assigning the code to run to the callback member of the ODBCThread object. For example, to print information when the connection fails, you could do this:

method MyApp.onConnectionFail()
{
    princ ("Connection closed: ", SQLResult.Description, "\n");
}

and then in the application constructor, do this:

thread.OnConnectionFailed = `(@self).onConnectFail();

The following callbacks are defined:

One of the most difficult concepts of using an asynchronous interface is the idea that a sequence of steps must be performed in order during startup, even though the script code cannot be executed sequentially. At each step, there could be an asynchronous request/event pair that breaks up the sequence into disjoint code fragments.

The ODBCThread class defines a startup state machine that helps to sequence these steps. As a script developer, you define the steps that will be performed by creating a series of initialization steps, or "stages" that will be performed after a successful database connection. These stages will not automatically be executed. It is up to you to initiate them. This allows you to choose whether to execute these stages on each connection, or only on the first connection, or to start executing the stages at a stage other than the first.

To create initialization stages, make repeated calls to the method "addInitStage":

AddInitStage(sqlCommand, onSuccess, onFailure);

The sqlCommand argument contains either a string containing valid SQL, or inline code to be executed. If the sqlCommand is a string, it is passed to the database thread for execution. If it is inline code, it will be executed immediately.

The onSuccess and onFailure arguments are inline code that will be executed when the sqlCommand has returned a result. Only one of these two will be executed for each execution of sqlCommand. The return value from onSuccess is ignored. If the return value from onFailure is nil, then the initialization sequence is aborted. It is sufficient to supply nil to onFailure to abort the sequence on any error, and to supply t to onFailure to continue the sequence if there is an error.

For example, the following sequence could be used:

// Drop the existing table.  If we get an error, continue
   thread.AddInitStage("drop table mytable", nil, t);

// Create a new table.  Once the table is created, set up an
// auto-incrementig primary key.  If either the table creation or
// the key definition fails, abort.
thread.AddInitStage("create table mytable ( myid counter, myvalue number, tstamp number )", nil, nil);
thread.AddInitStage("create unique index p_myid on mytable (myid) with primary disallow null", nil, nil);

// Make a call to getTableInfo to look up all tables in the
// database.  Since getTableInfo is asynchronous, we must use its
// callback to resume the initialization sequence by calling the
// special method "cbInitStage()" in the callback, and then
// letting the initialization sequencer call the real callback
// mapTable is an example of user-created code.
thread.AddInitStage(`(@thread).getTableInfo("", "", "", "TABLE", `(@thread).cbInitStage()),
                    `(@self).mapTable(@thread, "mytable", SQLTables), nil);

// We happen to know that the database we use does not provide
// primary key information so we have to set the primary key
// manually after the table has been mapped to  class.
thread.AddInitStage(nil, `(@self).SetClassKey((@self).tableclass, #myid), nil);

// Finally, we have passed through all the initialization steps,
// so we begin storing data.  beginDataStream in this example is
// user-generated code, which might set up timers or data event
// handlers that cause the script to write data to the database.
thread.AddInitStage(nil, `(@self).beginDataStream(@thread), nil);

Notice that the final two stages do not define the sqlCommand at all. This will cause their success code to run immediately and then to move on to the next initialization stage. If an initialization stage has no sqlCommand, it cannot fail.

At this point, the initialization stages are defined but have not run. They will not be run until the script calls:

thread.beginAsyncInit();

Commonly, you only want to run the initialization once, so you might handle it in the OnConnectionSuccess handler like this:

method MyApp.onConnect(thread)
{
    princ ("Connection succeeded\n");
    if (thread.is_first_connect)
    {
        thread.beginAsyncInit();
    }
}

Once you have defined the parameters for the database connection, defined the callback handlers and defined the initialization code, the connection is completely defined, but still is not running. To start the thread and begin connecting, call the Start method:

thread.Start();

At this point the thread is started and begins trying to connect to the database. As the thread runs it may call your callback functions in any order to indicate successes, failures, and errors.