DataHub^® ODBC Scripting : Version 7.2
	Chapter 5. Classes

`ODBCThread`

ODBCThread — configures the multi-threaded ODBC interface.

Synopsis

class ODBCThread
{
    Commands;              // Internal variable
    Callbacks;             // Internal variable
    OnExecuteStored;       // callback
    OnConnectionSucceeded; // callback
    OnConnectionFailed;    // callback
    OnFileSystemError;     // callback
    OnODBCError;           // callback
    NCached;               // Internal variable
    NUncached;             // Internal variable
    NStoredPrimary;        // Number of transactions written to level 1 cache
    NStoredSecondary;      // Number of transactions written to level 2 cache
    NForwarded;            // Number of transactions read from cache
    NStoreFailPrimary;     // Number of failures while writing to level 1 cache
    NStoreFailSecondary;   // Number of failures while writing to level 1 cache
    NForwardFail;          // Number of failures while writing to the database from cache
    NCommands;             // Number of commands ever queued to thread
    NResults;              // Number of commands results ever returned from thread
    NRejected;             // Number of commands that were rejected without queueing
    ReconnectSecs;         // Number of seconds to wait between database reconnection attempts
    ForwardDelay;          // Delay in milliseconds between transactions written from cache to database
    MaxTransactions;       // Maximum number of transactions to hold on queue
}

Description

As of this writing, the ForwardDelay member is not implemented.

The script developer should only modify the following members:

OnExecuteStored

OnConnectionSucceeded

OnConnectionFailed

OnFileSystemError

OnODBCError

ReconnectSecs

ForwardDelay

MaxTransactions

Methods

ODBCThread.CacheIsEmpty()

returns non-nil if both the level 1 and level 2 cache files are empty.

ODBCThread.Columns (catalog, schema, tablename, columnname, callback)

queries the database table for its column definitions. The catalog, schema, tablename and columnname are all strings. Some of these may accept wildcard patterns depending on the database being used. When the call completes, the callback code will be executed. The result is available in the SQLResult for the duration of the callback.

ODBCThread.Configure (DSN, username, password, flags, storagefile, maxfilesize)

sets the initial configuration for the database connection. The flags parameter can be either 0 or STORE_AND_FORWARD. If STORE_AND_FORWARD is not specified then no command on this connection will be stored, even if the individual command specifies the STORE_AND_FORWARD flag. The storagefile parameter specifies the name of a file to store the level 1 cache information for store and forward operation. The level 2 cache file name will be created from this file name.

The maxfilesize specifies the maximum number of bytes that a cache file can grow to. There can in fact be 3 cache files, each of this size, at any one time. The maxfilesize may be exceeded by the length of a single transaction for any file. If you set maxfilesize to 0, then 2.1 GB will be used. If the size exceeds this amount then it will be truncated to 2.1 GB. You may wish to intentionally limit the file size to a lower number. In a system where the data rate is always too high for the database to handle, a smaller cache file size will cause the ODBCThread to go through periods where its data is discarded while the cache file is caught up. A smaller file will make this discard/catch-up cycle faster.

Flags can be any combination of:

STORE_AND_FORWARD - If this flag is not set, then no file storage will take place. Any transactions that cannot be written to the thread immediately will be rejected. Any transactions in the queue that cannot be delivered to the database will be discarded.
NO_STORE_TO_SECONDARY - This tells the ODBCThread to only use the level 1 cache. If the queue between the script and the database thread becomes full, further transactions will be rejected until there is space in the queue. However, any transactions in the queue that cannot be delivered to the database will be stored in level 1 cache.
STORE_ALWAYS - This flag tells the ODBCThread to always store a transaction do disk before sending it to the database. This will normally cause the thread to read its queue faster, and write the transactions to disk more frequently. In case of a system crash, those transactions are more likely to be recoverable when the script re-starts. This option imposes a speed penalty if the disk is slow. On systems with fast disks, this penalty is normally minimal.
ALLOW_CACHE_RESTART - In case of a system or application crash, the ODBCThread will resume reading any disk files at the point where it left off when the application restarts. This flag tells the ODBCThread not to track its position in the disk file, and to restart at the beginning of the file during a crash recovery. This improves speed on systems with a slow disk, but means that some transactions may be sent to the database more than once. This should only be used if disk access is slow.
NO_FLUSH_ON_WRITE - The ODBCThread normally tries to update files as soon as possible after a write to disk. This is not efficient, but it improves the chance that more transactions will be recoverable in the case of a system or application crash. Specifying this flag will cause the ODBCThread to store data in memory longer and write to disk in larger blocks. This may improve performance for systems with a slow file system, but it increases the chance that transactions will be lost if the system crashes or shuts down.
We recommend using either:
```
STORE_AND_FORWARD
```
or
```
STORE_AND_FORWARD | STORE_ALWAYS
```
unless the impact of disk access is unacceptably high on the system.

ODBCThread.Connect ()

forces a connection attempt, even if the thread connection timer has not expired.

ODBCThread.DataSources (type)

lists all data sources (DSNs). The type parameter can be one of:

SQL_FETCH_FIRST - retrieve all DSNs.
SQL_FETCH_FIRST_USER - retrieve only user DSNs.
SQL_FETCH_FIRST_SYSTEM - retrieve only system DSNs.

The return value is an array of lists of the form: ("dsn_name" . "dsn_driver")

ODBCThread.Disconnect ()

forces the thread to disconnect from the database. If the thread is not paused then it will attempt to reconnect to the database on the next reconnect timer cycle.

ODBCThread.ExecDirect (flags, sql, callback)

executes an SQL statement on the database. Flags can be either 0 or STORE_AND_FORWARD. If the command cannot be executed immediately, and STORE_AND_FORWARD is set, and STORE_AND_FORWARD is also set on the thread, then the command will be stored to file and executed later. The SQL statement is a string. When the statement is executed, the callback will be called. The result is available in the SQLResult for the duration of the callback.

ODBCThread.GetFlags ()

retrieves the flags set by the .Configure method.

ODBCThread.GetMessageCount ()

retrieves the number of messages currently queued to the database thread.

ODBCThread.GetResultCount ()

retrieves the number of results currently queued from the database thread to the script thread.

ODBCThread.Insert (row, callback)

performs a database INSERT given an instance of a class that has been mapped to a column set in the database. The row must be an instance of a class returned from .ClassFromResultSet, .ClassFromTable, or .ClassFromThreadResult. When the insertion is complete, the callback is executed.

ODBCThread.QueueIsFull ()

returns non-nil if the message queue is full.

ODBCThread.IsPaused ()

returns non-nil if the thread is paused. See the information in ODBCThread.Pause().

ODBCThread.NoOp (callback)

sends a message to the database thread, and do nothing. When the message has arrived at the database thread, the method returns and runs the callback. This is a mechanism to synchronize execution in the script with actions that are queued on the database thread.

ODBCThread.Pause ()

pauses the thread. A paused thread will continue to store data to disk to be forwarded later, but it will not perform transactions on the database. If the database is disconnected and paused, the thread will not attempt to reconnect until the thread is resumed.

ODBCThread.PrimaryKeys (catalog, schema, tablename, callback)

queries the database for the primary keys for the given catalog, schema and tablename. The result is available in the SQLResult when the callback is executed.

ODBCThread.QuoteConversion (head, tail, character, replacement)

is used internally.

ODBCThread.Resume ()

resumes a thread that has been previously paused by .Pause().

ODBCThread.SlowInsert (row, callback)

is an alternate (slower) method to insert data. It acts the same as the .Insert method, except that it recomputes the SQL statement on each insert. The .Insert method computes the SQL statement ahead of time.

ODBCThread.SlowUpdate (row, callback)

is an alternate (slower) method to update data. It acts the same as the .Update method, except that it recomputes the SQL statement on each update. The .Update method computes the SQL statement ahead of time.

ODBCThread.Start ()

starts the thread and begins attempting to connect.

ODBCThread.Stop ()

closes the connection to the database and stops the thread.

ODBCThread.Tables (catalog, schema, tablename, tabletype, callback)

queries the database for all tables matching the catalog, schema, tablename and tabletype. It calls the callback when the transaction completes. Specifying an empty string ("") for any argument indicates no preference. The tabletype must be one of "TABLE", "VIEW" or "TABLE,VIEW". The result of this call is available in ODBCResult.

ODBCThread.Update (row, callback)

performs a database UPDATE given an instance of a class that has been mapped to a column set in the database. The row must be an instance of a class returned from .ClassFromResultSet, .ClassFromTable or .ClassFromThreadResult. When the update is complete, the callback is executed. The result of this call is available in ODBCResult.

ODBCThread.ValueString (value)

is used internally.

ODBCThread.AddInitStage (sqlString, onSuccess, onFailure)

adds an initialization stage to the sequential set of steps to be executed as part of the initialization after the database connections is made. See the section Configure Startup Actions above. The return value from this function is an index that can be given to .BeginAsyncInit.

ODBCThread.BeginAsyncInit (stage?=0)

starts executing the initialization stages in the order in which they were specified. If the stage argument is non-zero, being executing from that index in set. This index is provided as the return value from .AddInitStage.

ODBCThread.cbInitStage ()

is a provided callback that can be used to trigger the next initialization stage in the initialization sequence. If the stage specifies user-defined code instead of a string for the sqlString argument of .AddInitStage, that code must at some point call .cbInitStage in order for the sequence to continue.

ODBCThread.ClassFromResultSet (columnresult, keyresult, superclass?=nil, symclassname?=#UnboundODBCThreadTableClass)

creates a class from the table defined in the given result sets. The columnresult is the column definition for the table, and the keyresult is the result from calling .PrimaryKeys on the table, or the result from querying the table through the .Tables method. If the superclass is non-nil, the class will be derived from superclass, otherwise it will have no parent class. If symclassname is provided, that class name will be used instead of the default UnboundODBCThreadTableClass. The class produced by this call maps each column in the columnresult to a member variable in the class. In addition, information about the primary key and the source table is held in the class. Instances of this class are suitable for use with the .Insert method. If the table has a primary key, then instances of this class can also be used in the .Update method.

ODBCThread.ClassFromTable (tablename, tables, superclass?=nil, symclassname?=#UnboundODBCThreadTableClass)

creates a class from the table named tablename from the table set defined in tables. The tables argument is the value of SQLTables from a call to the .GetTableInfo method. See the discussion in .ClassFromResultSet for more information.

ODBCThread.ClassFromThreadResult (threadresult, superclass?=nil, symclassname?=#UnboundODBCThreadTableClass)

creates a class from an ODBCThreadResult instance. This instance is usually obtained from a call to .GetTableInfo or .Columns. See the discussion in .ClassFromResultSet for more information.

ODBCThread.constructor ()

is the constructor for this class. Do not override the constructor for ODBCThread in your own code. Instead, derive a new class from ODBCThread and then define a constructor for your derived class.

ODBCThread.CreateClass (symclassname, superclass, ivars, tablename, primary_key)

is the low-level call made from .ClassFromResultSet, .ClassFromTable and .ClassFromThreadResult. It constructs the necessary code to define a class that maps its member variables to columns in a result set.

ODBCThread.EvalSafe (code)

is used internally.

ODBCThread.GetDataSources (direction?=SQL_FETCH_FIRST)

queries the ODBC subsystem for the names of all DSNs. The type parameter can be one of:

SQL_FETCH_FIRST - retrieve all DSNs.
SQL_FETCH_FIRST_USER - retrieve only user DSNs.
SQL_FETCH_FIRST_SYSTEM - retrieve only system DSNs.

The return value is an array of lists of the form: ("dsn_name" . "dsn_driver")

This method is the only method that calls synchronously into the ODBC subsystem. The result is available as the return value from this function. The ODBC definition states that this call will be entirely satisfied by the driver manager, and so cannot block on the database. The database does not need to be connected, and the database thread does not have to be started for this method to succeed.

ODBCThread.GetInsertFormat (klass)

constructs the SQL statement that will be issued when the .Insert method is called. If you change the definition of the table by calling .SetClassKey, then you must also issue .GetInsertFormat and .GetUpdateFormat after .SetClassKey completes.

ODBCThread.GetTableInfo (catalog, schema, tablename, tabletype, callback)

produces a result that will be available in the special variable SQLTables for the duration of the callback. SQLTables is an array of arrays. Each element of the result is an array of two elements containing the table name and an instance of ODBCThreadResult corresponding to a call to .Columns for that table.

ODBCThread.GetUpdateFormat (klass)

constructs the SQL statement that will be issued when the .Update method is called. If you change the definition of the table by calling .SetClassKey, then you must also issue .GetInsertFormat and .GetUpdateFormat after .SetClassKey completes.

ODBCThread.HandleColumnInfo (tablename, results, callback)

is used internally.

ODBCThread.HandleFinalInfo (results, callback)

is used internally.

ODBCThread.HandleTableInfo (results, callback)

is used internally.

ODBCThread.NextInitStage ()

is used internally.

ODBCThread.SetClassKey (klass, keysym, ignore_if_set?=t)

sets the primary key for the class specified by klass. The class is the result of a call to .ClassFromResultSet, .ClassFromTable and .ClassFromThreadResult. Some databases (MS-Access in particular) do not provide information about the primary keys in a table. In order for .Update calls to work on this type of class, the .SetClassKey method must be called to tell the table which column is its primary key.


ODBCStatement		ODBCThreadResult