FireDAC
ContentsIndexHome
PreviousUpNext
Handling Errors

Describes how to handle database errors with FireDAC.

Group
Links
General

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

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

DBMS 
Exception class 
Error class 
Advantage Database 
uADPhysADSWrapper.EADSNativeException 
Sybase SQL Anywhere 
uADPhysASAWrapper.EASANativeException 
uADPhysODBCWrapper.TADODBCNativeError 
DataSnap server 
IBM DB2 
uADPhysODBCWrapper.TADODBCNativeError 
Firebird / Interbase 
uADPhysIBWrapper.EIBNativeException 
uADPhysIBWrapper.TADIBError 
Microsoft Access 
uADPhysODBCWrapper.TADODBCNativeError 
Microsoft SQL Server 
MySQL 
uADPhysMySQLWrapper.MySQLNativeException 
uADPhysMySQLWrapper.TADMySQLError 
ODBC 
uADPhysODBCWrapper.EODBCNativeException 
uADPhysODBCWrapper.TADODBCNativeError 
Oracle 
uADPhysOracleWrapper.EOCINativeException 
uADPhysOracleWrapper.TOCIError 
PostgreSQL 
uADPhysPGWrapper.EPgNativeException 
uADPhysPGWrapper.TADPgError 
SQLite 
uADPhysSQLiteWrapper.SQLiteNativeException 
dbExpress v1-3 
uADPhysDBExp.EDBXNativeException 
dbExpress v4 

 

And TADDBError has ErrorCode property - the native DBMS error code. 

"Unification" means that all driver exception classes are inherited from the EADDBEngineException - a single base class that contains driver independent information. And the Kind property - a DBMS independent error code. For example, code for handling a unique key violation may look like this: 

 

try
  ADQuery1.ExecSQL('insert into MyTab(code, name) values (:code, :name)', [100, 'Berlin']);
except
  on E: EADDBEngineException 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 EADDBEngineException properties:

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

And by the TADDBError properties:

  • ErrorCode - 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 EADDBEngineException provides the SQL and Params properties. That is the SQL command with parameter values leaded to the exception. 

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

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

 

Handling exceptions

The exceptions may be processed using one of the ways:

  • using try / except / end construction. This is a standard Delphi way to handle exceptions. For example:

 

ADConnection1.StartsTransaction;
try
  ADQuery1.ExecSQL;
  ADConnection1.Commit;
except
  on E: EADDBEngineException do begin
    ADConnection1.Rollback;
    // do something here
    raise;
  end;
end;

 

  • setting TADQuery.OnError event handler;
  • setting TADConnection.OnError event handler. These ways are good for exception logging or exception "adjusting". For example:

 

procedure TForm1.ADConnection1Error(ASender: TObject; const AInitiator: IADStanObject;
  var AException: Exception);
var
  oExc: EADDBEngineException;
begin
  if AException is EADDBEngineException then begin
    oExc := EADDBEngineException(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;

ADConnection1.OnError := ADConnection1Error;

 

  • setting TADQuery.OnExecuteError event handler for handling Array DML specific errors;
  • setting TADQuery.OnUpdateError event handler for handling updates posting errors;
  • setting TADConnection.OnLosted, OnRestored, OnRecover event handlers for handling connection lost errors.

 

Using end user error dialog

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

 

 

To use the dialog, just drop the component somewhere on a form. The dialog will hook TApplication.OnException event handler. And will popup the dialog, when there is an unhandled FireDAC exception. The "Query" page allows to see the SQL command text produced the exception. Pressing Ctrl+C in dialog will put complete exception information into clipboard.

What do you think about this topic? Send feedback!