Recovering Connection (FireDAC)

Go Up to Working with Connections (FireDAC)

Describes how to work in an unstable environment. FireDAC allows an application to recover from a connection failure.

General

A database application can work within an unstable environment, where the network may be physically disconnected or the DBMS server may be restarted. The application needs to seamlessly recover from such interruptions and continue to communicate with the DBMS.

The FireDAC automatic connection recovery allows you to detect when a connection has been lost and to properly respond and recover from this situation. The loss is discovered only when a certain DB action is performed, such as Open, ExecSQL, or Ping. Then the DBMS driver raises EFDDBEngineException with Kind = ekServerGone.

The simplest way to verify a connection status and/or keep the connection alive is to call the TFDCustomConnection.Ping method. The Ping method can be used even when a connection is closed, to see when a connection can be established.

Known limitations:

DBMS	Description
Advantage Database	Not supported for a local free connection.
dbExpress bridge driver	FireDAC may fail to detect ekServerGone. Ping method is not supported.
Informix	Required to set DirectExecute to True.
Microsoft Access	Not supported.
MySQL	To minimize call delays when the network connection is lost, consider adjusting the ReadTimeout and WriteTimeout connection definition parameters.
ODBC bridge driver	FireDAC may fail to detect ekServerGone. The Ping method is not supported.
SQLite	Not supported.

Controlling Connection Recovery

To enable automatic connection recovery, set ResourceOptions.AutoReconnect to True. Optionally, use the TFDCustomConnection event handlers:

• OnRecover - to respond to the connection lost event and provide the next action to FireDAC.
• OnLosted - fires when a connection was lost and not recovered.
• OnRestored - fires when a connection was lost and recovered.

The response from the TFDCustomConnection.OnRecover event handler may be to re-establish the connection, go into the offline mode, or simply close the connection. When OnRecover is not specified, FireDAC tries to re-establish the connection. For example:

procedure TForm1.FDConnection1Recover(ASender: TObject;
  const AInitiator: IFDStanObject; AException: Exception;
  var AAction: TFDPhysConnectionRecoverAction);
var
  iRes: Integer;
begin
  iRes := MessageDlg('Connection is lost. Offline - yes, Retry - ok, Fail - Cancel',
    mtConfirmation, [mbYes, mbOK, mbCancel], 0);
  case iRes of
  mrYes:    AAction := faOfflineAbort;
  mrOk:     AAction := faRetry;
  mrCancel: AAction := faFail;
  end;
  Log('Connection is recovering');
end;

We strongly recommend that you do not close the FireDAC manager from within OnRecover, OnLost, and OnRestored event handlers, because this can lead to unexpected problems.

Preparing the Application

When a connection is recovered, the following states are lost:

• active transactions are rolled back and the execution is continued from the failed statement.
• result sets that have not been fetched yet are trimmed and TFDDataSet.SourceEOF is set to True.
• database session states are lost, including Oracle package states and session variables.
• registered database alerts are unregistered.

To prepare your application to work in an unstable environment and minimize failure affects, set the following option values:

Set this option	To the following value
FetchOptions.Mode	fmAll
FetchOptions.RowsetSize	200 - 300
FetchOptions.AutoClose	True (the default)
TxOptions.AutoCommit	True
ResourceOptions.AutoReconnect	True

The application can use the Offline connection mode and CachedUpdates.

See Also

• Establishing Connection
• Offlining Connection