Delphi TIdTCPServer processing multiple requests

Question

I'm developing a messaging system in delphi using TidTCPServer and TidTCPClient.

First all clients log in with a username and a password. The server searches for the user data in SQL server using ADO components. After that the clients send multiple requests to the server every 10 seconds to tell the server that they're online and get the status of their contact list. All these requests end up with reading or modifying SQL tables.

Sometimes I get this error: Operation cannot be performed while executing asynchronously. It's raised by ADO. I think that the TCP server is working asynchronously and multiple access to ADODataset at the same time is the problem. What should I do? Should I put the incoming requests into a list and process them one by one until the list is clear?

Answer 1

TIdTCPServer is a multi-threaded component. Each client runs in its own worker thread.

ADO is a set of apartment-threaded COM objects. You cannot share them across thread boundaries unless you marshal them.

You will have to either:

1. give each thread its own database connection and query components. Optionally put the objects in a pool to control the number of active connections/queries that run at a time. The pool can handle the necessary marshalling when taking a connection out of the pool.

2. Delegate the queries to a dedicated thread that runs the queries and sends the results back to the appropriate thread.

3. Switch to another database framework that better supports threaded access, like dbExpress.

Personally, I use a pooled version of #3. When a client needs to execute a query, it grabs a TSQLConnection from the pool (connecting it to the database if needed), creates a TSQLQuery object, executes the query, reads the result, and then puts the TSQLConnection back into the pool. The pool has a dedicated thread that monitors which connections are in use, and closes connections that are idle for a period of time.