Handling Errors (FireDAC)
Go Up to Working with Connections (FireDAC)
This topic describes how to handle database errors with FireDAC.
Contents
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:
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, 'Tokyo']);
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;
- setting the TFDQuery.OnError event handler.
- setting the TFDConnection.OnError event handler. These are good ways to handle exception logging or exception "adjusting". For example:
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;
- setting the TFDQuery.OnExecuteError event handler for handling Array DML specific errors.
- setting the TFDQuery.OnUpdateError event handler for handling updates posting errors.
- setting the TFDConnection.OnLost, OnRestored, OnRecover event handlers for handling connection lost errors.
Using End User Error Dialog
With the help of the TFDGUIxErrorDialog component, an end user can be notified about errors returned by the database:
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.