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?

Eshman

SQL Server allows Qry.ExecSQL with SQL.Text = ' ' as a query (but not ''). This can be executed hundreds of times per second. – Liggitt

Answer 1

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 ...

Arvie

Hi da-soft. if we use TADOConnection in the main thread should we "ping" the sql from the main thread? also should we use the same TADOConnection or use a different "ping" TADOConnection? – Collusive

Answer 2

@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.

Thoria

Answer 3

I have same problem on connection pool. I developed a TADOSQLConnectionPool class to help reusing connection for threads DB works. When I want to assign a connection to a thread I tried to check it's health by running minimum job as "Select 1". In this way, I will be sure about connection.If it fails,I will dispose all connections to recreate at next request.

Eggshell

Answer 4

This is one of the reasons to drop ADO and use DBX. Ado architecture is server cursor based and this requeris not to lost the connection to the server anytime. If connection is lost in some circumstances, the connection will be unable to get up again. In the other hand DBX is able to reconnect near to ever because of its disconnected arquitecture.

Opaque

This is not true. ADO can use as server as client cursors and allows to apply a disconnected dataset model. From about.com: In order to create a disconnected ADO recordset, you must first set the ADODataSets CursorLocation property to "clUseClient". Then open the Recordset. Then set the ADODatasets Connection to Nil. Do not close the ADODataset. – Arvie

Yes ADO can work in briefcase mode. But the response of DBX over this issue is more natural and predictible. – Opaque