CPB Mailing List
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Performing A Query in A Thread That Links To A Form Grid--[Info not a problem]
Performing A Query in A Thread That Links To A Form Grid
Introduction
As a relational database grows more complex, it is not unusual to have a
number of SQL queries displayed in grids - and those queries typically need
to be refreshed if any one of the underlying tables change. The problem is
that the underlying tables grow larger, refreshing the queries takes longer
and longer. If you have implemented refreshing the query in the AfterPost
of each table, this can cause extremely long and annoying pause between
finishing an edit and being able to move to the next row in a grid.
The answer to this problem lies in the C++ Builder TThread class, which
allows a program to separate itself into separate independent threads of
execution. Your normal C++ Builder program consists of a single thread,
often referred to as the "main thread". You can, within that thread, spawn
other threads, and those threads can affect the controls on any of the
forms in your project - handled carefully.
Sharing, Synchronization, Events, and Deadlock
A thread is a class like any other. Its interface can consist of
properties, variables, and methods. That interface can be accessed by any
thread, which can access the thread class instance. However - that sharing
must be handled carefully.
Two threads are running. Under pre-emptive multitasking, a thread can be
suspended by the operating system at any time, during any instruction.
Thus, complex operations may not be completed before a thread is suspended
to let some other thread run. If the other thread accesses an only partly
completed data structure of the first thread, it may operate incorrectly or
it may abort.
TThread provides a special method called "Synchronize". It takes the name
of a method in the thread. When called, it calls the specified method, but
it does so in the context of the main thread. This allows threads to access
main thread data structures without difficulty. Threads can use this not
only to access main thread form controls and data structures, but can also,
via those controls and data structures, indirectly communicate with any
other threads.
Under a pre-emptive multitasking operating system, it is improper for a
thread to use resources to do nothing but wait - for instance by executing
an endless loop. The Windows API provides "events", which allow threads to
signal each other, and to wait for an event without consuming resources.
Events are created, destroyed and activated with the Windows API, using
CreateEvent, CloseHandle and SetEvent respectively. A thread waits on an
event with WaitSingleObject.
Events come with their own built in danger. If a thread is waiting for an
event from the main thread, and the main thread waits on an event from the
thread, deadlock can result, with neither thread continuing to execute. For
that reason, WaitSingleObject provides for a timeout interval, after which,
even if an event has not been signaled, the thread proceeds.
Databases and Threads
Every thread must have its own TSession object to arbitrate access to
databases. The main thread automatically gets a hidden TSession object, but
your thread must create its own before working with any database or data
aware objects.
Interestingly, the main thread can use datasets and datasources within
another thread in its data aware controls. And that is what makes this
project possible.
The Design
The design needs to meet the following requirements:
Query refreshing must proceed with minimum inteference in the peformance of
the main thread.
Query refreshing must show as little as possible of its thread orientation
to the main thread or to any of the form controls.
With this in mind, the design plays out as follows:
There will be two threads - the main thread with its main form, and the
query thread, which executes the SQL whose result is displayed in a grid on
the main form.
The query thread will create its own data source and table, and connect
those to the grid in the main thread.
After the query thread starts and creates / attaches everything, it will
wait on an event in a loop. When the event is signaled, it will detach
everything from the grid, refresh the query, and then reattach to the grid
(reasons given below).
The main thread will signal the query thread by calling the Refresh()
function of the thread, which will set the event.
Terminate() on the thread will set the event, and the thread will check
every loop iteration to see if the Terminated property (set by Terminate())
is set.
The Implementation
Here is the header file. The thread implementation is entirely in the
header file for convenience.
//--------------------------------------------------------------------------
-
#ifndef MainFormH
#define MainFormH
//--------------------------------------------------------------------------
-
#include <vcl\Classes.hpp>
#include <vcl\Controls.hpp>
#include <vcl\StdCtrls.hpp>
#include <vcl\Forms.hpp>
#include <vcl\DBTables.hpp>
#include <vcl\DB.hpp>
class TThreadedQuery; // Forward declaration to be resolved below
class TThreadedSQLForm : public TForm
{
__published: // IDE-managed Components
TDBGrid *RealTableGrid;
TDBGrid *SQLGrid;
TLabel *RealTableLabel;
TLabel *SQLTableLabel;
TButton *BackgroundRegenButton;
PVTable *Table;
TDataSource *Source;
void __fastcall FormShow(TObject *Sender);
void __fastcall BackgroundRegenButtonClick(TObject *Sender);
private: // User declarations
TThreadedQuery *myThreadedQuery;
public: // User declarations
__fastcall TThreadedSQLForm(TComponent* Owner);
__fastcall ~TThreadedSQLForm(void);
};
//--------------------------------------------------------------------------
-
extern TThreadedSQLForm *ThreadedSQLForm;
//--------------------------------------------------------------------------
-
class TThreadedQuery: public TThread
{
public:
typedef TThread Superclass;
private:
TSession *Session;
TQuery *Query;
TDataSource *Source;
HANDLE RefreshOrTerminateRequestedEvent;
public:
__fastcall TThreadedQuery(void): TThread(TRUE)
{
RefreshOrTerminateRequestedEvent =
CreateEvent(NULL,FALSE,FALSE,NULL);
FreeOnTerminate = TRUE;
Session = new TSession(NULL);
Session->SessionName = "Thread"+IntToStr(ThreadID);
Query = new TQuery(NULL);
Query->Name = "ThreadedSQL";
Query->DatabaseName = "d:\\test\\Threaded SQL";
Query->SessionName = Session->SessionName;
Source = new TDataSource(NULL);
Source->Name = "ThreadedQuerySource";
Source->DataSet = Query;
Priority = tpLower; // Keep the priority lower than the main
thread
Resume();
};
__fastcall ~TThreadedQuery(void)
{
delete Source;
delete Query;
delete Session;
CloseHandle(RefreshOrTerminateRequestedEvent);
};
void __fastcall Refresh(void) // Called by client
{
SetEvent(RefreshOrTerminateRequestedEvent); // Windows API
};
void __fastcall Terminate(void)
{
Superclass::Terminate();
SetEvent(RefreshOrTerminateRequestedEvent); // Windows API
};
private:
void __fastcall LinkToGrid(void)
{
ThreadedSQLForm->SQLGrid->DataSource = Source;
};
void __fastcall UnlinkFromGrid(void)
{
ThreadedSQLForm->SQLGrid->DataSource = NULL;
};
void __fastcall Execute(void)
{
Query->SQL->Add("select * from Test");
Query->Active = TRUE;
Synchronize(LinkToGrid);
while (!Terminated)
{
WaitForSingleObject(RefreshOrTerminateRequestedEvent,INFINITE);
// Windows API
if (!Terminated)
{
Synchronize(UnlinkFromGrid);
Query->Active = FALSE;
Query->Active = TRUE;
Synchronize(LinkToGrid);
};
};
};
};
#endif
The body is relatively simple, mostly concerned with starting and
terminating the thread:
//--------------------------------------------------------------------------
-
#include <vcl\vcl.h>
#pragma hdrstop
#include "MainForm.h"
//--------------------------------------------------------------------------
-
#pragma resource "*.dfm"
TThreadedSQLForm *ThreadedSQLForm;
//--------------------------------------------------------------------------
-
__fastcall TThreadedSQLForm::TThreadedSQLForm(TComponent* Owner)
: TForm(Owner)
{
}
//--------------------------------------------------------------------------
-
__fastcall TThreadedSQLForm::~TThreadedSQLForm(void)
{
myThreadedQuery->Terminate();
}
//--------------------------------------------------------------------------
-
void __fastcall TThreadedSQLForm::FormShow(TObject *Sender)
{
myThreadedQuery = new TThreadedQuery();
}
//--------------------------------------------------------------------------
-
void __fastcall TThreadedSQLForm::BackgroundRegenButtonClick(TObject
*Sender)
{
myThreadedQuery->Refresh();
}
//--------------------------------------------------------------------------
-
The Rationale Behind Disconnecting
Because the Synchronize method executes in the context of the main thread,
it is a safe bet that the main thread cannot progress while the method
being synchronized is being executed. Since that method, in this case,
would be opening and closing the query, it might as well be run in the main
thread if it were in the body of the method being synchronized. Thus, the
thread disconnects the query from the grid, the query is closed and opened
in the thread, and then the query is reconnected in a Synchronize, giving
true parallel operation.
Caution
Remember that all of the tables used by the thread end up implicitly owned
by the thread as if the thread were another user. This means that attempts
to gain exclusive access from the main thread to a table used by the query
in the thread will fail, since the thread has its own database session. You
will need to signal the thread to release the table prior to attempting
exclusive access.
Conclusion
Developing a threaded query refresh is not only fairly simple, but it
reveals very little of its nature to the caller. Obviously, some work would
need to be done to make the thread a particpant in data awareness so that
the main thread could simply be connected to it at design time. Have fun
getting that done!
W Komornicki's Home Page |
Main Index |
Thread Index