Handling Errors (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Connections (FireDAC)


This topic describes how to handle database errors with FireDAC.

General

The EFDDBEngineException class is the base class for all DBMS exceptions. A single exception object is a collection of database errors, accessible through the EFDDBEngineException.Errors[] property and represented by the TFDDBError class.

FireDAC combines "personalization" and unification of the EFDDBEngineException exception and of the TFDDBError error classes. "Personalization" means that a driver can have its own exception and error classes, which contain information specific to the DBMS:

DBMS Exception class Error class
Advantage Database FireDAC.Phys.ADSWrapper.EADSNativeException FireDAC.Stan.Error.TFDDBError
DataSnap server FireDAC.Phys.TDBXBase.ETDBXNativeException FireDAC.Stan.Error.TFDDBError
dbExpress v4 FireDAC.Phys.TDBXBase.ETDBXNativeException FireDAC.Stan.Error.TFDDBError
Firebird FireDAC.Phys.IBWrapper.EIBNativeException FireDAC.Phys.IBWrapper.TFDIBError
IBM DB2 FireDAC.Phys.DB2.EDB2NativeException FireDAC.Phys.ODBCWrapper.TFDODBCNativeError
Informix FireDAC.Phys.Infx.EInfxNativeException FireDAC.Phys.Infx.TFDInfxError
InterBase FireDAC.Phys.IBWrapper.EIBNativeException FireDAC.Phys.IBWrapper.TFDIBError
Microsoft Access FireDAC.Phys.MSAcc.EMSAccessNativeException FireDAC.Phys.ODBCWrapper.TFDODBCNativeError
Microsoft SQL Server FireDAC.Phys.MSSQL.EMSSQLNativeException FireDAC.Phys.MSSQL.TFDMSSQLError
MySQL FireDAC.Phys.MySQLWrapper.EMySQLNativeException FireDAC.Phys.MySQLWrapper.TFDMySQLError
ODBC FireDAC.Phys.ODBCWrapper.EODBCNativeException FireDAC.Phys.ODBCWrapper.TFDODBCNativeError
Oracle FireDAC.Phys.OracleWrapper.EOCINativeException FireDAC.Phys.OracleWrapper.TOCIError
PostgreSQL FireDAC.Phys.PGWrapper.EPgNativeException FireDAC.Phys.PGWrapper.TFDPgError
SQLite FireDAC.Phys.SQLiteWrapper.ESQLiteNativeException FireDAC.Stan.Error.TFDDBError
Sybase SQL Anywhere FireDAC.Phys.ASAWrapper.EASANativeException FireDAC.Phys.ODBCWrapper.TFDODBCNativeError
Teradata Database FireDAC.Phys.TData.ETDataNativeException FireDAC.Phys.ODBCWrapper.TFDODBCNativeError

TFDDBError has an ErrorCode property, which is the native DBMS error code.

"Unification" means that all driver exception classes are inherited from the EFDDBEngineException - a single base class that contains driver independent information. Its Kind property is a DBMS independent error code. For example, the code for handling a unique key violation may be as follows:

try
  FDQuery1.ExecSQL('insert into MyTab(code, name) values (:code, :name)', [100, 'Berlin']);
except
  on E: EFDDBEngineException do begin
    if E.Kind = ekUKViolated then
      ShowMessage('Please enter unique value !');
    raise;
  end;
end;

Error Information

The error information is mainly present by the EFDDBEngineException properties:

  • Errors -- the collection of the TFDDBError objects.
  • ErrorCount -- the number of errors in the Errors collection.
  • Kind -- the DBMS independent error kind.
  • Message -- the actual error message.

And by the TFDDBError properties:

  • ErrorCode -- the DBMS vendor specific error code.
  • Kind -- the DBMS independent error kind.
  • Message -- the error message.

To simplify the application debugging or to make exception logging more informative, the EFDDBEngineException provides the SQL and Params properties.

Also, depending on the error area and the DBMS ability to provide the advanced error information, the following TFDDBError properties are useful:

  • When a SQL parsing error is involved, then CommandTextOffset returns the offset in the SQL command text.
  • When a constraint violation, a DB object alteration failure, or some other errors are involved, then the ObjName property returns a database object name.
  • When an Array DML error is involved, then RowIndex returns the array row index, to which the error belongs.

Handling Exceptions

Exceptions can be processed in one of the following ways:

  • using the try/except/end construction. This is a standard Delphi way to handle exceptions. For example:
FDConnection1.StartsTransaction;
try
  FDQuery1.ExecSQL;
  FDConnection1.Commit;
except
  on E: EFDDBEngineException do begin
    FDConnection1.Rollback;
    // do something here
    raise;
  end;
end;
procedure TForm1.FDConnection1Error(ASender: TObject; const AInitiator: IFDStanObject;
  var AException: Exception);
var
  oExc: EFDDBEngineException;
begin
  if AException is EFDDBEngineException then begin
    oExc := EFDDBEngineException(AException);
    if oExc.Kind = ekRecordLocked then
      oExc.Message := 'Please, try the operation later. At moment, the record is busy'
    else if (oExc.Kind = ekUKViolated) and SameText(oExc[0].ObjName, 'UniqueKey_Orders') then
      oExc.Message := 'Please, provide the unique order information. It seems, your order was already put';
  end;
end;

FDConnection1.OnError := FDConnection1Error;

Using End User Error Dialog

With the help of the TFDGUIxErrorDialog component, an end user can be notified about errors returned by the database:

FireDACErrorPicture.png

To use the dialog, just drop the component somewhere on a form. The dialog hooks the TApplication.OnException event handler and pops up the dialog, when there is an unhandled FireDAC exception. The "Query" page allows you to see the SQL command text produced by the exception. Pressing Ctrl+C in the dialog puts the complete exception information into clipboard.

See Also