How to work with database and UI under threads?

Question

I am interested in make better programs with more responsive design and capabilities. Nowadays, when I create my programs that access data remotely, my interface freezes and there is no animated GIF to work on that condition.

I was told by David Hefferman that animated GIF that are created in the VCL do not respond even in threads because the VCL is in the main thread, and the same goes to databases.

My doubt here is how to work with threads, specifically in databases, so I have lots of questions about it.

Do I have to implement my entire database in thread functions and procedures? If that is correct, then I can't use database by dropping components to the Form, right? But what about the user input and grids? Will they work correctly with those threads or will I have to user regular TEdit instead of TDBEdit to then send it's content to a insert/update sql command?

The main objective in here is to create Delphi application that access remote databases like MySQL using Zeos but not freezing for every drop of consult made in the server. At least the smaller ones. It would be very ugly if the system were to download a list of records to a table and the user could still input things. For those cases I would like very much that my animated GIF (or other solutions) could work.

Thank you for any help at all!

Answer 1

In my experience, the best approach is to drop your database components on a Data module and then create this data module dynamically in each thread. Database components typically work fine if they are created and initialized in the thread that is using them.

There are, however, caveats - if you are connecting to a Firebird database, you should make sure that only one thread at the time is establishing a connection. (Use a critical section around the code that connects to the database.) This holds for Firebird 1.5, 2.0 and 2.1 but may not be necessary anymore for Firebird 2.5 (I didn't yet have opportunity to test it).

EDIT (in answer to EASI's comment): Yes, connecting to a database can take some time. If you frequently need to execute short operations, it is best to keep threads connected and running for a longer period of time.

I can think of two ways to do that. 1) Keep threads alive and connected and run a message loop inside. This loop would receive commands from the main thread, process them and return a result. 2) Keep threads initialized and connected in a thread pool and activate them when you need to perform a database operation.

Basically, both approaches are the same, the difference is in the level which handles 'receive and process command' loop.

The second approach can be easily implemented in the OmniThreadLibrary by using the IOmniConnectionPool.SetThreadDataFactory mechanism. See Adding connection pool mechanism to OmniThreadLibrary and demo 24_ConnectionPool for more information. Alternatively, you can use the high-level abstraction Background worker where you can establish database connection on a per-thread basis in a Task initialization block.