How to resume/retry a broken TADOConnection across the application?

Question

I have a data module with a global TADOConnection (with the default KeepConnection set the true). There are numerous datasets and queries in my existing application that use this global TADOConnection.

I was wondering if there is some smart way to resume/retry the ado connection in case of a short network disconnection? (this situation happens sometimes with clients who have a not so stable connections).

Its easy to reproduce what I need. simply open TADOConnection on start-up. open some TADODataSet, and then disable and enable your "Local Area Connection". if you try to refresh the dataset, an EOleException exception is raised

"Connection failure"

or

"[DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation"

If I restart the application all is good.

No events are fired by the TADOConnection at the time of network disconnections. and TADOConnection.Connectedremains true

of course I could use a try/catch for every TDataSet.Open or Execute but I'm looking for some "centralized" solution for my large application. so in case of "Connection failure" I could know which dataset is trying to open, and retry.

Answer 1

The idea could be to catch the connection error then manage it with retries. The Suggestion:

This function returns the exception descripion

function GetStrException(ExceptObject: TObject; ExceptAddr: Pointer):String;
var
  Buffer: array[0..1023] of Char;
begin
    ExceptionErrorMessage(ExceptObject, ExceptAddr, Buffer, SizeOf(Buffer));
    Result:=Buffer;
end;

this is a simple idea to test!

procedure TForm1.Button2Click(Sender: TObject);
var
 s,error:String;
begin
 //a select as an example
 S := 'SELECT COUNT(*) FROM MyTable';
 TRY
   WITH ADOQuery1 DO BEGIN SQL.Clear; SQL.Add(s);OPEN;END;
   Memo1.Lines.ADD(ADOQuery1.Fields[0].AsString);
 EXCEPT
   error:=(GetStrException(ExceptObject,ExceptAddr));
   //using MySql my error case is when the connection is lost, so the error is "Server has gone away"
   if pos(error,'has gone away')>0 then
     begin
       Showmessage('Connection Error, please try again');
       try
          sleep(1000)
          AdoConnection1.close;
          AdoConnection1.open;
       except
         Showmessage('The connection Error persists, please contact the support');
       end;
     end;
 END;
end;

A good solution is centralize the insert/update and select procedure, then catch the error and try to adjust the situation one or two times before show message to the user

Answer 2

No never firing at the time of network disconnections. But you can check connection before every command. So if AdoConnection disconnected, you can reconnect and execute your command after this.

If you wanna centralized solution and you have 1 Connection, you can do that like this;

Const
  ConnectionTestString=' '; //Yes, it's work on Sql Server. If doesnt your db, you can use 'Select 1'

Procedures;

Procedure TDM.GetCommandResult_Conn(CText:String;Connection : TAdoConnection);
var Ado_Ds_Tmp:TAdoCommand;
Begin
    Ado_Ds_Tmp:=TAdoCommand.Create(self);
    try
      Ado_Ds_Tmp.Connection:=Connection;
      Ado_Ds_Tmp.ParamCheck := False;
      Ado_Ds_Tmp.CommandText:=CText;
      try
        Ado_Ds_Tmp.Execute;
      except
        DM.RaiseExceptionCreate('Error ! Command, ('+StrToList(CText, ' ')[0]+')');
      end;
    finally
      Ado_Ds_Tmp.Destroy;
    end;
end;

procedure TDM.ADOConnection1WillExecute(Connection: TADOConnection;
  var CommandText: WideString; var CursorType: TCursorType;
  var LockType: TADOLockType; var CommandType: TCommandType;
  var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus;
  const Command: _Command; const Recordset: _Recordset);
var
   ErrorLogFileName : string;
   ErrorFile : TextFile;
   ErrorData : string;
   Msg : String;
begin
  try
    if (CommandText<>ConnectionTestString) then begin
      DM.GetCommandResult_Conn(ConnectionTestString, Connection);
    end;
  except
    try
      try
        Connection.Connected := False;
      except
      end;
      try
        Connection.ConnectionString := AdoConnectionString;
        Connection.Mode:=cmShareDenyNone;
      finally
        try
          Connection.Connected := True;
          // If you wanna log for frequency 
          ErrorLogFileName := ChangeFileExt(Application.ExeName,'.error.log');
          AssignFile(ErrorFile, ErrorLogFileName);
          if FileExists(ErrorLogFileName) then
            Append(ErrorFile)
          else
            Rewrite(ErrorFile);
          try
            ErrorData := Format('%s : %s : %s (%s / %s)',[DateTimeToStr(Now), 'Disconnected but we reconnect.', '', 'UserName : '+DBUser, 'Client : '+GetComputerNetName]);
            WriteLn(ErrorFile,ErrorData);
          finally
            CloseFile(ErrorFile)
          end;
        except
          DM.RaiseExceptionCreate('ReConnection Failed!');
        end;
      end;
    except
    end;
  end;
end;

Any question?