I have never worked with threads before, and now I'm trying to create a thread with a query to check database status. The query is as follows:
select (*) as DBCount from v$datafile where status in 'OFFLINE';.
This query returns the total number of all the databases that are offline. Now I want to create a thread in Delphi that will execute this query in the background of my application when I run it and display the results on a label.
Multithreading is hard. You may be better off using a simple threading framework like AsyncCalls.
Following StackOverlow threads may give you more information on how to solve your problem:
How Do I Choose Between the Various Ways to do Threading in Delphi?
Delphi thread that waits for data, processes it, then resumes waiting
This is fairly easy to do using AsyncCalls. Let's assume your non-threaded code looks like this (ignoring all error handling):
Query := 'select (*) as DBCount from...';
ExecuteSQL(SqlConnection,Query);
SqlResult := GetSqlResult(SqlConnection);
SqlRow := GetSqlRow(SqlResult);
MyLabel.Text := SqlRow[0];
...Go on to do other things...
Where the second line is blocking (waiting for the server to reply). Your new code would look like this:
uses AsyncCalls; //added to your existing uses statement
...
procedure DoesSomething();
var Thread: TAsyncCall; //your interface to AsyncCalls
procedure AsyncSqlCall(); //this is a LOCAL procedure
Query := 'select (*) as DBCount from...';
ExecuteSQL(SqlConnection,Query);
SqlResult := GetSqlResult(SqlConnection);
SqlRow := GetSqlRow(SqlResult);
EnterMainThread;
try
Assert(GetCurrentThreadId = MainThreadId);
MyLabel.Text := SqlRow[0];
finally
LeaveMainThread;
end;
begin //this begins proc DoSomething()
...
Thread := LocalAsyncCall(@AsyncSqlCall);
...Go on to do other things...
end;
All we've done is put the blocking SQL calls in a local proc and told AsyncCalls to execute it in another thread, while the main thread continues executing. The only tricky part was using the VCL, which is not thread-safe. So I had that line safely run in the main thread.
If at some point you need to be sure the Async thread has completed, you would execute this line to block the main thread till AsyncSqlCall terminates:
Thread.sync;
The really nice thing here is that AsyncCalls handles all the stuff about creating a thread pool, creating threads, etc. Though not shown in this example, you can pass variables to your thread, and return a value. You don't have to use a local proc, but doing so gives it access to all local vars. You could make all this global, and then launch the Async thread in one routine, and test for its completion in another.
Restrictions:
Your Async thread must not touch (read or write) anything but its own variables, and your main thread must not touch them while the Async thread is running. YOU must code it that way. Nothing will stop you from creating total chaos. In the above example, your main thread must not touch Query, SqlConnection, SqlResult, and SqlRow. If any part of your code used one of those vars before the Thread.sync call, your code would work -- but throw exceptions in weird places you never expected. So keep it simple.
Your Async thread must not use the VCL. The sample above shows one of several ways to safely get around this limitation.
Finally:
AsyncCalls is not a full Multi-Threading framework. It's just a way of calling procs & functions asynchronously (i.e. without waiting). Don't try to push it too far -- by which I mean, don't try to make it the basis of a fully multitasking program.
In Delphi there is TThread class. You can create such thread using simple wizard from Delphi IDE (in Turbo Delphi: File->New->Delphi Projects->Delphi Files->Thread Obcjet). In its constructor create connection to database, and in Execute method you can create loop that query database, update some global variable (NOT declared as threadvar) and sleep some time.
Here you can find a discussion about using threads on DataBase.
There are some code that can be usefull for you.
Regards.
