1

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

  • Just to link it to another related question: [Threaded Delphi ADO Query](https://stackoverflow.com/questions/16247695/threaded-delphi-ado-query) – saastn Oct 25 '17 at 05:09

2 Answers2

8

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.
Cosmin Prund
  • 25,498
  • 2
  • 60
  • 104
  • +1 for the answer, and how would you create them ? create and free them periodically, or firstly create connection, periodically create and free query and finally free the connection ? –  Feb 14 '11 at 15:40
  • 1
    @daemon_x, it depends allot on the database you're using. I'd normally crate the connection and query once and keep them for the life of the thread (or until I run into some ADO Exception) but if your SQL server is licensed per connection, this might not be a good idea. In the inner loop (the loop that executes once per minute) I'd start and stop the transaction. Of course, not all databases accessible through ADO have transactions. – Cosmin Prund Feb 14 '11 at 15:45
  • @Cosmin Prund - sounds good, and yep I'm using MSSQL, so I'm able to use transactions, but with start and stop transaction you meaned TADOConnection.BeginTrans, CommitTrans ? Is it faster or better somehow than TADOQuery.Open, Close ? I really want just to read from the DB and I know transactions just for writing point of view (and only in SQL scripts, not ADO components :). –  Feb 14 '11 at 17:41
  • 2
    @daemon_x, if your database supports transactions, you're going to use transaction any way: Explicitly if you start and stop your transactions from code, or implicitly if you call `TADOQuery.Open()`. The trouble is, implicitly opened transactions need to stop at some time, and if you use those, you'll need to learn how your ADO driver handles them, what the default transaction isolation level is. Manually starting and stopping transactions is so much simpler! – Cosmin Prund Feb 14 '11 at 18:55
  • @Cosmin Prund - thanks for the explanation, so as I see I would use BeginTrans and immediately after setting SQL query text CommitTrans to get data, or am I wrong ? –  Feb 14 '11 at 19:00
  • 1
    @daemon_x, [Isolation @ Wikipedia](http://en.wikipedia.org/wiki/Isolation_(database_systems)) talks a little about what transactions are and how they work. In a single sentence, all work you do with a database happens in the context of one transaction. You can do multiple operations at once, then call Commit or Rollback for all of them. You get data from the database when you call `TADOQuery.Open()`, you can `.Close()` that query if you want to or you can `.Open()` an other one. All those operations need to happen between calls to `BeginTrans` and `CommitTrans` (or `Rollback`) – Cosmin Prund Feb 14 '11 at 19:24
  • Thanks a lot for your time. Sometimes I'm wondering, why there are so many differencies between tutorials with this issue. That's also the reason, why I'm asking for those details, because I just want the smooth code (for my better sleeping :) –  Feb 14 '11 at 20:00
1

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.

Misha
  • 1,816
  • 1
  • 13
  • 16