Why TADOConnection in a Delphi service application does not connect?

I have a problem with TADOConnection in a Delphi service application.

I want to read a string from a Text file and put it into my ADOConnection's connection string and then connect the ADOConnection to my database. When I do this in a VCL Form application, everything is OK:

procedure TForm2.FormCreate(Sender: TObject);
Var
  ConnectionFile:TextFile;
  FilePath: string;
  ServerName, DatabaseName, Username, Password: string;
begin
  FilePath := 'Connection.CNF';
  if FileExists(FilePath) then
  Begin
    Try
      AssignFile(ConnectionFile,FilePath);
      Reset(ConnectionFile);
      ReadLn(ConnectionFile,SerVerName);
      ReadLn(ConnectionFile,DatabaseName);
      ReadLn(ConnectionFile,UserName);
      ReadLn(ConnectionFile,Password);
      CloseFile(ConnectionFile);
      AdoConnection1.ConnectionString := Format('Provider=SQLOLEDB;Data Source=%s;Initial Catalog=%s;User ID=%s;Password=%s;',[ServerName, DatabaseName, Username, Password]);
      adoConnection1.Connected:=True;
    Except
      ShowMessage('Invalid Connection String');
    End;
  End
  Else
    ShowMessage('Connection file does not exist');
end;

But, when I try to do same in a Service application, my ADOConnection does not connect to the database.

I found that when I connect my ADOConnection to a database by providing a connection string at design-time and then compile and install the Service, I can use my ADOConnection and change the Connection String, but my ADOConnection does not connect to a Database at design-time. When I compile and use the Service application, the ADOConnection can not connect to the Database:

procedure TTelemetryServiceF.ServiceStart(Sender: TService;
  var Started: Boolean);
Var
  ConnectionFile:TextFile;
  FilePath: string;
  ServerName, DatabaseName, Username, Password: string;
begin
  FilePath := 'C:\Connection.CNF';
  if FileExists(FilePath) then
  Begin
    Try
      AssignFile(ConnectionFile,FilePath);
      Reset(ConnectionFile);
      ReadLn(ConnectionFile,SerVerName);
      ReadLn(ConnectionFile,DatabaseName);
      ReadLn(ConnectionFile,UserName);
      ReadLn(ConnectionFile,Password);
      CloseFile(ConnectionFile);
      ADOConnection1.Close;
      ADOConnection1.:=False;
      ADOConnection1.ConnectionString := Format('Provider=SQLOLEDB;Data Source=%s;Initial Catalog=%s;User ID=%s;Password=%s;',[ServerName, DatabaseName, Username, Password]);
      ADOConnection1.Connected:=True;
    Except
      //      TestPoint('Connection Error');
    End;
  End
end;

Solution

ADO is a COM-based technology, and uses apartment-threaded COM objects which have an affinity to the thread they are created in.

In a VCL Form application, the COM library is automatically initialized for you in the main UI thread. But, in a Service application, TService events run in a separate worker thread, so you will have to manually initialize the COM library for that thread, eg:

procedure TTelemetryServiceF.ServiceStart(Sender: TService;
  var Started: Boolean);
var
  ...
begin
  CoInitialize(nil); // <-- ADD THIS!!!
  ...
end;

procedure TTelemetryServiceF.ServiceStop(Sender: TService;
  var Stopped: Boolean);
begin
  CoUninitialize; // <-- ADD THIS!!!
end;

Also, since the TService object is auto-created in the main thread, but most of its events (including OnStart) are called in a worker thread, dropping the TADOConnection onto the TService at design-time will create it in the wrong thread context. As such, you need to either:

create the TADOConnection object in the context of the service's worker thread. You can create the TADOConnection object directly in your event handler code, eg:

type
  TTelemetryServiceF = class(TService)
    ...
  private
    MyADOConnection: TADOConnection;
    ...
  end;

procedure TTelemetryServiceF.ServiceStart(Sender: TService;
  var Started: Boolean);
var
  ...
begin
  CoInitialize(nil);
  MyADOConnection := TADOConnection.Create(nil);
  // use MyADOConnection as needed...
  ...
end;

procedure TTelemetryServiceF.ServiceStop(Sender: TService;
  var Stopped: Boolean);
begin
  ...
  MyADOConnection.Free;
  CoUninitialize;
end;

or, if you want to drop the TADOConnection at design-time then you have two options:

drop it onto a separate TDataModule (even though TService already derives from TDataModule), set the project to NOT auto-create that DM, and then create an instance of it yourself in your OnStart event, eg:

uses
  ..., MyDataModule;

type
  TTelemetryServiceF = class(TService)
    ...
  private
    MyDataModule: TMyDataModule;
    ...
  end;

procedure TTelemetryServiceF.ServiceStart(Sender: TService;
  var Started: Boolean);
var
  ...
begin
  CoInitialize(nil);
  MyDataModule := TMyDataModule.Create(nil);
  // use MyDataModule.ADOConnection1 as needed...
  ...
end;

procedure TTelemetryServiceF.ServiceStop(Sender: TService;
  var Stopped: Boolean);
begin
  ...
  MyDataModule.Free;
  CoUninitialize;
end;

drop it onto the TService itself, but then marshal its Connection COM object across thread boundaries at runtime, eg:

uses
  ..., ActiveX, ADOInt;

type
  TTelemetryServiceF = class(TService)
    ADOConnection1: TADOConnection;
    ...
  private
    Strm: IStream;
    MyADOConnection: _Connection;
    ...
  end;

procedure TTelemetryServiceF.ServiceCreate(Sender: TObject);
begin
  CoInitialize(nil);
  ...
  OleCheck(CoMarshalInterThreadInterfaceInStream(_Connection, ADOConnection1.ConnectionObject, Strm));
  ...
end;

procedure TTelemetryServiceF.ServiceDestroy(Sender: TObject);
begin
  CoUninitialize;
end;

procedure TTelemetryServiceF.ServiceStart(Sender: TService;
  var Started: Boolean);
var
  ...
begin
  CoInitialize(nil);
  OleCheck(CoGetInterfaceAndReleaseStream(Strm, _Connection, MyADOConnection));
  // use MyADOConnection as needed...
  ...
end;

procedure TTelemetryServiceF.ServiceStop(Sender: TService;
  var Stopped: Boolean);
begin
  ...
  MyADOConnection := nil;
  CoUninitialize;
end;