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)?

Jamieson

duplicate of #1242344 – Prosector

resource usage and connection initialization time can be improved using a connection pool. For example based on the demo application(s) in the OmniThreadLibrary (requires Delphi 2007 or newer) at otl.17slon.com – Goran

Thanks - will check this out. But first I will get the thing goin- then can worry about the performance. – Jamieson

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.

Abscond

Thanks. And I even think I may have read this article too. I will work on re-writing it. One thing though: I am never getting "Coinitialize was not called" exception. Instead I am just getting access violations. Can this exception be "silent"? – Jamieson

Follow up on this. Will the application run "fine" as long as two queries are never done at the same time? And only crash at the time of simultaneous connections? – Jamieson

Sorry for the delay, was away for awhile. Sorry I can't help you on either of your comments. I've always gotten the "Coinilialize was not called" exception, and making the call cleared things up. Regarding multiple queries, I've never had an issue, but I suspect that if multiple calls were at the EXACT same time, issues may arise, but I can't say for sure. Thanks for the rep points too. I'm still a rookie at this community stuff... – Abscond

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;

Cockneyism

Thank you very much. It appears that you and Vic Adams were writing essentially the same answer at pretty much the same time. Because there is no way to "share" votes, I thought it would be nice to give Vic (only 31 reputation points) the vote. I hope you don't take exception to this. – Jamieson