Ok to use TADOConnection in threads

Question

I have created an TCPip server application. The application has one global TADOConnection. This global ado connection is used both for main thread queries and also within threaded processes.

Is this ok? Does the ADOConnection have built in mechanisms to handle multiple queries at the same time?

My application works find in testing environments (2-5 connections). But deployed in a production environment I am getting "unexplainable" access violations at the point the TADOQuery linked to the ADOConnection are set to opened.

Should I be using ADOConnection or should all queries just make the connection to the database on their own (which is probably a bit more resource costly)?

Answer 1

Each thread needs to have its own connection object. The following link provides more information: http://delphi.about.com/od/kbthread/a/query_threading.htm

Some key points from the article:

1] CoInitialize and CoUninitialize must be called manually before using any of the dbGo objects. Failing to call CoInitialize will result in the "CoInitialize was not called" exception. The CoInitialize method initializes the COM library on the current thread. ADO is COM.

2] You cannot use the TADOConnection object from the main thread (application). Every thread needs to create its own database connection.

Answer 2

@M Schenkel, see this question Is Delphi’s TADOConnection thread-safe?. Each thread need its own connection because ADO is a COM-based technology and It uses apartment-threaded objects.

see this sample

procedure TMyThread.Execute;
begin
   CoInitialize(nil);
   try
     try
       // create a connection here
     except
     end;
   finally
     CoUnInitialize;
   end;
end;