4.2. Store and Forward

DataHub ODBC Scripting : Version 6.4
	Chapter 4. Multi-Threaded ODBC Interface

Store and Forward is a term used to describe a database connection where the data is stored locally to disk and then later forwarded to the database. The ODBCThread object performs an advanced form of store and forward that does more than simply store data for later delivery.

For any SQL statement given to the ODBCThread.ExecDirect method, you can optionally specify that this particular statement should be stored for later forwarding. Normally these will be INSERT or UPDATE statements, but they could be an SQL statement that must be executed at the database, such as stored procedures or ALTER statements. The ODBCThread guarantees that all storable SQL commands will be executed in the order in which they are specified, even if they are first stored to file.

The ODBCThread object uses a sophisticated store and forward technique that only writes to disk if the database is not connected, or has been paused. If the database is available, the commands will be transmitted directly to the database. This means that there is no penalty to using store and forward during normal operation.

ODBCThread also maintains an in-memory queue of pending operations. This queue helps to avoid writing to disk during busy periods or during short database or network outages. You can modify the depth of this queue to reduce the chance of involving the disk during busy periods. The queue depth defaults to 100 messages, but it can be modified by setting the MaxTransactions member of the ODBCThread. For example:

thread.MaxTransactions = 200;

For the thread to perform store and forward, the flag STORE_AND_FORWARD must be specified when initially configuring the thread, and the flag STORE_AND_FORWARD must also be specified for any call to ExecDirect that should be a candidate for store and forward treatment.

4.2.1. Time Delayed Writes

Time delayed writing is used to avoid maintaining a continuous connection to the database, or to make use of times of day where the network utilization is low. You can call the Pause and Disconnect methods at any time to cause the thread to pause output to the database, then disconnect. All further transactions will be written to the local disk cache until the database is reconnected. To resume writing to the database, the script just needs to call the Resume method. The database thread will automatically reconnect to the database when it can.

You can periodically test to see whether the disk cache has been transmitted by running a repeating timer that calls CacheIsEmpty. When CacheIsEmpty returns non-nil, the disk cache has been consumed. At this point the script can once again Pause and Disconnect the thread.

Using this method, the script can transmit the cached data based on the time of day, a process event, or even input from an operator.