scheduling services on startup

Question

I have developed an Application using BDS 2006 which uses MySQL database(connected using DataModule and MyDAC components).
Now I want to start my application on the start-up of the system(Windows XP).So I included the application shortcut in the start up folder .

Now on the start-up, my application starts prior to the MySQL service getting started. Hence I get an error Cannot connect to MySQL.

So I inserted a blank from on start of my application and performed the check to see if MySQL is running or not.If not running then wait until it is running.

function ServiceGetStatus(sMachine, sService: PChar): DWORD;
  {******************************************}
  {*** Parameters: ***}
  {*** sService: specifies the name of the service to open
  {*** sMachine: specifies the name of the target computer
  {*** ***}
  {*** Return Values: ***}
  {*** -1 = Error opening service ***}
  {*** 1 = SERVICE_STOPPED ***}
  {*** 2 = SERVICE_START_PENDING ***}
  {*** 3 = SERVICE_STOP_PENDING ***}
  {*** 4 = SERVICE_RUNNING ***}
  {*** 5 = SERVICE_CONTINUE_PENDING ***}
  {*** 6 = SERVICE_PAUSE_PENDING ***}
  {*** 7 = SERVICE_PAUSED ***}
  {******************************************}
var
  SCManHandle, SvcHandle: SC_Handle;
  SS: TServiceStatus;
  dwStat: DWORD;
begin
  dwStat := 0;
  // Open service manager handle.
  SCManHandle := OpenSCManager(sMachine, nil, SC_MANAGER_CONNECT);
  if (SCManHandle > 0) then
  begin
    SvcHandle := OpenService(SCManHandle, sService, SERVICE_QUERY_STATUS);
    // if Service installed
    if (SvcHandle > 0) then
    begin
      // SS structure holds the service status (TServiceStatus);
      if (QueryServiceStatus(SvcHandle, SS)) then
        dwStat := ss.dwCurrentState;
      CloseServiceHandle(SvcHandle);
    end;
    CloseServiceHandle(SCManHandle);
  end;
  Result := dwStat;
end;  

code source

// if MySQL not running then sleep until its running
procedure TForm1.FormCreate(Sender: TObject);
begin
  while(ServiceGetStatus(nil, 'MySQL5.5') <>4 ) do
   begin
     sleep (200);
   end;  
end;

I would like to know if my approach is correct? If not suggest the same.
Also can this be done without the programming by using windows?

Answer 1

Sleeping in the main thread is never a good idea.

It's better to do the waiting in a thread and post a message to the main thread when MySQL is running.

Answering the comment from @mghie:

Why would waiting on the event be any better (or any different) than calling Sleep()?

An event-driven GUI is considered good programming practice. There is no waiting involved. When the event is fired, the GUI is informed about the status change of the database connection. If you would be waiting in a Sleep() loop, the application appears non-responsive. And calling Application.ProcessMessages to somewhat take care of that, is really not a good practice.

An example how to wait until MySQL is running in a thread:

const
  WM_MySQL_READY = WM_USER + 1;  // The unique message id 

type
  TForm1 = class(TForm)
  ...
  private
    procedure OnMySqlReady( var Msg: TMessage); message WM_MySQL_READY;
  ...
  end;

In your thread:

Constructor TMyThread.Create( OwnerForm : TForm);
begin
  Inherited Create( false); 
  FOwnerForm := OwnerForm; // Keep for later use 
  Self.FreeOnTerminate := true;
end;

procedure TMyThread.Execute;
var 
  SQL_started : boolean;
  sleepEvent : TSimpleEvent;
begin
  sleepEvent := TSimpleEvent.Create;
  try
    repeat
      SQL_started := (ServiceGetStatus(nil, 'MySQL5.5') = 4);
      sleepEvent.WaitFor(200);  // Better than sleep();
    until SQL_started or Terminated;
  finally
    sleepEvent.Free;
  end;
  // Inform main thread
  PostMessage( FOwnerForm.Handle,WM_MySQL_READY,WPARAM(SQL_started),0);  
end;

Ok, I misunderstood @mghie a little, his question was why the TSimpleEvent.WaitFor() is better than Sleep() inside the thread.

For a background see: thread-sleep-is-a-sign-of-a-poorly-designed-program.

In short, Sleep() puts the thread into a deep sleep and control is not given back at best periodic rate (if ever in some corner cases).

TSimpleEvent.WaitFor() on the other hand is much more responsive with regards to timing and waking up. (Remember that Windows is not a true real-time OS and timing is not guaranteed). Anyway rule of thumb, in threads, use TSimpleEvent.Waitfor() instead of Sleep().

Should need arise to halt the wait for connection to the MySQL server, some adjustment to the code can be made:

constructor TMyThread.Create(OwnerForm: TForm; cancelEvent : TSimpleEvent);
begin
  inherited Create(false);
  FOwnerForm := OwnerForm;  // Make sure it's assigned
  FCancelEvent := cancelEvent; // Make sure it's assigned
  Self.FreeOnTerminate := true;
end;

procedure TMyThread.Execute;
var
  SQL_started : boolean;
  cancel : boolean;
begin
  repeat
    SQL_started := (ServiceGetStatus(nil, 'MySQL5.5') = 4);
    cancel := (FCancelEvent.WaitFor(200) = wrSignaled);
  until SQL_started or Terminated or cancel;
  // Inform main thread
  PostMessage( FOwnerForm.Handle,WM_MySQL_READY,WPARAM(SQL_started),0);
end;

To abort the thread prior to the connection is made, just call MyEvent.SetEvent.

---

You can even present a splash screen from a thread if you want to inform the user about what's going on during the wait.

See Peter Below's Threaded Splashscreen for Delphi for such an example. Note that this code does not make use of any VCL components or anything that involves synchronizing with the main thread.

You might also want to look at: Show a splash screen while a database connection (that might take a long time) runs.