How can I detect that a TadoConnection lost the communication with the server?

Question

I need to detect when a TAdoConnection component has lost the connection with the server. I've tried using the OnDisconnect event but this only fires when the Close method is called or the Connected property is set to false.

Another option I've tried is using a TTimer and executing a query like this

SELECT 1 RESULT FROM DUAL

in the OnTimer event, catching any exception that occurs.

Is there a better option to detect that the connection was lost?

Answer 1

@Dimitry answer is very good. If it is critical to your application to know if the connectino is lost, the TTimer approach (with a minimal opperation).

If you just want to know when a statement fail due to a "lost communication", you can use the Application.OnException event and inspect the Exception properties.

I made the following code as example, using the ApplicationEvents component. Is just a draft with the idea, not suitable for production.

uses
  ComObj;

procedure TForm2.ApplicationEvents1Exception(Sender: TObject; E: Exception);
var
  EO: EOleException;
begin
  if E is EOLEException then
  begin
    EO := EOleException(E);
    //connection error (disconnected)
    if EO.ErrorCode = E_FAIL then
    begin
      try
        try
          ADOConnection1.Close;
        except
          ;
        end;
        ADOConnection1.Open;
        ShowMessage('Database connection failed and re-established, please retry!');
      except
        on E:Exception do
          ShowMessageFmt('Database connection failed permanently.  '
            + 'Please, retry later'#13'Error message: %s', [E.Message]);
      end;
    end
    else
      ShowMessage(E.Message + ' ' + IntToStr(EO.ErrorCode));
  end
  else
    ShowMessage(E.ClassName + #13 + E.Message);
end;

Best regards.

Answer 2

I see the DUAL table. Means, you are using the Oracle :)

For most (all ?) client / server DBMS's there is no way to detect, that a connection is losted, other than to ask a DBMS for some action. And there are a lot of reasons, why a connection is losted. May be a network failure, may be ..., may be a DBA shutdowned a DB.

Many DBMS API's, including Oracle OCI, have the special functions, allowing to ping a DBMS. The "ping" is a smallest possible request to a DBMS. The above SELECT requires much more job, than such ping.

But not all data access components, including ADO, allows to ping a DBMS, using the DBMS API ping call. Then you have to use some SQL command. So, the above SELECT is correct with ADO. Other option - BEGIN NULL; END;. It may be using less DBMS resources (no need for optimizer, no need to describe a result set, etc).

TTimer is OK. The query should be performed in a thread, where the corresponding connection is used. Not a must although, but it is a different issue.

The potential problem may be to close a connection, when a connection is losted. As a connection closing may raise an exception due to the DBMS API may be in a failed state.

Kind of that ...