Threading DbExpress queries

Question

I have only done "GUI" database access in Delphi with DbExpress components, but now I'd like to execute one query on background. I read some where that TSQLConnection is not thread safe and I have to create new connection for each thread. I see that there's CloneConnection in TSQLConnection, but help claims that new connections are owned by the original connection.

So,

1) What is proper way to execute TSQLQuery located on datamodule threaded?

2) Can I use TSQLConnection.CloneConnection? Should I free the cloned connections or left them untouched when thread ends?

Simple example code (or URL) would be very helpful.

Answer 1

This is not specific to DbExpress (which I have no experience with), but the best idea for moving some functionality into a background thread is to first develop it in the main thread, debug it, and once you are sure it is performing fine, then move it.

What you can do is put everything onto a TDataModule. If the data module is able to work on its own, needing no other components on other forms or data modules, and you create it in the background thread, then you can be fairly certain that things will work. You don't need to clone the connection, as it will be created and destroyed in the same thread where all access to the database is performed.

Some important tips for database work with background threads:

• Handle all exceptions, as unhandled exceptions in a secondary thread can / will crash your application. Exception handling in the Application object will not work for you.

• Do not access anything in the GUI thread. That means most importantly that TDataSource is not usable, and no data sensitive controls can be used.

• If the components you use do anything with SendMessage or PostMessage you will have to create a standard message loop in the background thread.

• If the components you use do anything with OLE, call both OleInitialize() and OleUnitialize() in the context of the background thread.

If you want to make your life easier, consider using either OmniThreadLibrary or AsyncCalls. Which one depends on how long your background database connections will live and whether you want to reuse them.