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.

Okay

Catch the exception on the application level, then repeatedly try MyConnection.Connected := False; MyConnection.Connected := True; For the record, our legacy software does the same thing, and upon reconnection, from what I understand, all the queries connected to it have no choice but to be refreshed (opened again) manually - so keep track of them.. – Pertain

You my find my answer to this question useful. This is essentially answering the same question but in the context of SQL Server Always On. I suspect an AlwaysOn switchover generates exactly the symptoms that you are trying to address – Snooperscope

@JerryDodge, I found this similar answer: https://mcmap.net/q/935931/-how-can-i-detect-that-a-tadoconnection-lost-the-communication-with-the-server Problem is, you don't know which dataset or query raised the exception. so you cannot retry unless you track every dataset/query before and Open/Exec – Okay

@Kanitatlan, Indeed this is a very interesting solution (in my application I will need to use an interposer class b/c I already use TADODataSet and not other child class). However in the moment of fail other datasets might be already opened/active (in my case). so calling TADOConnection.Close will make them automatically inactive. Maybe save the state of all TADOConnection.DataSets before closing ADOConnection... – Okay

@zig, In our case we simply copy the connectionstring into each TDataset instance and allow ADO connection pooling to handle this issue. If you made that change and create a TADODataSet derivative along the lines of my TADOQuery derivative you could use the same approach. It should be a relatively simple refactor to change all TAdoDataSet usage to your new version. – Snooperscope

@Kanitatlan, "we simply copy the connectionstring into each TDataset instance and allow ADO connection pooling to handle this issue." Can you please elaborate on this? How does using connectionstring in the TDataSet instead of TADOConnection helps in a situation where the TDataSet was active before the fail? – Okay

@zig, if you provide a ConnectionString instead of a shared connection the connection management is done in ADO using pooling behavior. Having familiarized myself with the code this is only used for the utility queries (ie updates, inserts, deletes) and the displays all use a shared connection. This still seems to work fine with individual intercepted exceptions. I suggest you try it and see what happens – Snooperscope

@Kanitatlan, please make your comments an answer and I'll accept. – Okay

Answer 1

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?

Kirbee

Nice idea. but this will not allow me to re-open a dataset again once it fails. the exception for DataSet.Open will still occure. – Okay

Yes it will allow. when Dataset.Open; first firing WillExecute and code test connection exists with ConnectionTestString. If fails reconnect AdoConnection. After WillExecute, code continue with Dataset.Open... end-user doesn't aware re-connection. – Kirbee

But i changed that code in my program. 1-) in sql server # temp table auto-drop when connection id changed. So i can't access on new connection anymore. And 2-) when data lost is often because of user's very bad networking, they should know that. If i hide problem, it may couse bigger issues later... – Kirbee

"Yes it will allow..." I have tested it. the WillExecute reconnects ok. but an EOleException is fired after that in TCustomADODataSet.OpenCursor: The connection cannot be used to perform this operation. It is either closed or invalid in this context. I can't put my finger exactly which line of code is throwing it. – Okay

You're right. Strange. We used this method couple years ago. May be some internal libraries changed delphi6>7. May be need some modifications ADODB.pas and/or DB.pas. As i said i don't use and don't suggest anymore self reconnection. Sorry. – Kirbee

I see. so you say don't handle reconnects at all? you do nothing and raise the exception (and your program is dead b/c you must restart)? – Okay

Absolutely. I achieve that but give up. Just inform user. Otherwise you'll experience more serious and hard to catch problems. – Kirbee

I would love to do nothing. but my clients don't like this idea! lol they all have crazy network configurations which might cause disconnects. so I guess something must be done besides restarting my program. – Okay

Answer 2

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

Bamako