Working with TADOQuery in thread

Question

I'm writing the application, which connects to the DB and repetitively (1 minute interval) reads the data from a database. It's something like RSS feed reader, but with local DB. If the data reading fails, I try to reestablish the connection. I've designed it with TADOConnection and TADOQuery placed on the form (so with no dynamic creation). My aim is to keep the application "alive" from the user's point of view, so I placed the connection and the reading part into a single thread. The question is, how to do it best way ?

My design looks like this:

• application start, the TADOConnection and TADOQuery are created along with the form
• open connection in a separate thread (TADOConnection)
• if the connection is established, suspend the connection thread, start the timer on the form, which periodically resumes another thread for data reading
• if the reading thread succeeds, nothing happens and form timer keeps going, if it fails, the thread stops the timer and resume connection thread

Is it better to create TADOConnection or TADOQuery dynamically or it doesn't matter ? Is it better to use e.g. critical section in the threads or something (I have only one access to the component at the same time and only one thread) ?

Thanks for your suggestions

Answer 1

This question is fairly subjective, probably not subjective enough to get closed but subjective any way. Here's why I'd go for dynamically created ADO objects:

• Keeps everything together: the code and the objects used to access the code. Using data access objects created on the form requires the Thread to have intimate knowledge of the Form's inner workings, that's never a good idea.
• It's safer because you can't access those objects from other threads (including the main VCL thread). Sure, you're not planing on using those connections for anything else, you're not planning on using multiple threads etc, but maybe you'll some day forget about those restrictions.
• It's future-proof. You might want to use that same thread from an other project. You might want to add an second thread accesing some other data to the same app.
• I have a personal preference for creating data access objects dynamically from code. Yes, an subjective answer to a subjective question.

Answer 2

Run everything in the thread. Have a periodic timer in the thread that opens the DB connection, reads the data, "posts" it back to the main thread, and then disconnects. The thread needs to "sleep" while waiting for the time, e.g. on a Windows even that is signalled by the timer. The DB components, which are local and private to the thread, can be created inside the thread when thread executions starts (on application startup), and freed when thread execution finishes (on application shutdown). This will always work, regardless of whether the DB conncetion is temporarily available or not, and the main thread does not even have to communicate with the "DB thread". It is an architcture that I use all the time and is absolulutely bullet-proof.