Writing Expressions (FireDAC)

Go Up to Working with DataSets (FireDAC)

FireDAC offers powerful client expressions engine, used for filtering, indexing, and calculated fields.

General

FireDAC supports expression syntax compatible with:

• BDE data access components
• TClientDataset
• Oracle 8 (not for 100%)
• ODBC escape functions

To add Oracle-like, ODBC-like, and regular expression functions, you need to include the FireDAC.Stan.ExprFuncs unit into any application uses clause. Otherwise you can get an error, such as "Column with name [NVL] is not found". Here is a list of registered functions:

• Character Macro Functions
• Numeric Macro Functions
• Date and Time Macro Functions
• System Macro Functions
• Convert Macro Function

The expression engine supports FireDAC escape sequences. Also, the dataset fields may be referenced in an expression, as the identifiers. For example:

FDQuery1.Filter := 'upper(name) like ''bill%''';
FDQuery1.Filtered := True;

Or:

FDQuery1.Filter := '(upper(rtrim(name)) like ''bill%'') or (upper(rtrim(name)) like ''john%'')';
FDQuery1.Filtered := True;

If you need to create your own function and register it with FireDAC, see the FireDAC.Stan.ExprFuncs.pas unit for details.

Using Expression for Custom Purposes

An application can use the FireDAC expression evaluator to perform text formula calculations. There are two basic options:

• Use the TFDDataSet.CreateExpression method to perform calculations on the dataset fields. For example:

var
  oEval: IFDStanExpressionEvaluator;
...
oEval := FDMemTable1.CreateExpresison('(sal + comm) * tax / 100');
Label1.Caption := oEval.Evaluate;
...
FDMemTable1.Next;
oEval.DataSource.Position := FDMemTable1.GetRow;
Label1.Caption := oEval.Evaluate;
...
oEval := nil;
FDMemTable1.Close;

• Use the API to extend the evaluator by custom data source. For example:

uses
  FireDAC.Stan.Intf, FireDAC.Stan.Factory, FireDAC.Stan.Expr, FireDAC.Stan.ExprFuncs;

type
  TMyVariable = class (TInterfacedObject, IFDStanExpressionDataSource)
  private
    FName: String;
    FpValue: PDouble;
  protected
    function GetVarIndex(const AName: String): Integer;
    function GetVarType(AIndex: Integer): TFDDataType;
    function GetVarScope(AIndex: Integer): TFDExpressionScopeKind;
    function GetVarData(AIndex: Integer): Variant;
    ...........
  public
    constructor Create(AName: String; var AValue: Double);
  end;

{ TMyVariable }

constructor TMyVariable.Create(AName: String; var AValue: Double);
begin
  inherited Create;
  FName := AName;
  FpValue := @AValue;
end;

function TMyVariable.GetVarIndex(const AName: String): Integer;
begin
  if CompareText(AName, FName) = 0 then
    Result := 0
  else
    Result := -1;
end;

function TMyVariable.GetVarType(AIndex: Integer): TFDDataType;
begin
  if AIndex = 0 then
    Result := dtDouble
  else
    Result := dtUnknown;
end;

function TMyVariable.GetVarScope(AIndex: Integer): TFDExpressionScopeKind;
begin
  if AIndex = 0 then
    Result := ckConst
  else
    Result := ckUnknown;
end;

function TMyVariable.GetVarData(AIndex: Integer): Variant;
begin
  if AIndex = 0 then
    Result := FpValue^
  else
    Result := Null;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  d: Double;
  oDS: IFDStanExpressionDataSource;
  oParser: IFDStanExpressionParser;
  oEval: IFDStanExpressionEvaluator;
begin
  oDS := TMyVariable.Create('x', d);

  FDCreateInterface(IFDStanExpressionParser, oParser);
  oEval := oParser.Prepare(oDS, '(2*x)+cos(sin(90)+10)', [], [poDefaultExpr], '');

  d := 1.2;
  ShowMessage(oEval.Evaluate);

  d := 3.4;
  ShowMessage(oEval.Evaluate);
end;

Samples

• FireDAC TFDQuery Filter sample
• FireDAC TFDQuery Aggregates sample
• FireDAC\Samples\DatS Layer\CalcColumnWithFuncs