Collapse All
AnyDAC
ContentsIndexHome
PreviousUpNext
Writing Expressions
Example  Working with DataSets  Send Feedback

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

Group

Working with DataSets

Links
Example, Working with DataSets
Description
General

AnyDAC 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 should include uADStanExprFuncs unit into any application uses clause. Otherwise you can get error, like "Column with name [NVL] is not found". The registered functions are:

Expression engine does not support ODBC escape {fn xxx} syntax for calling of functions. But practically the full set of the escape functions is supported. To call a function just call it in the expression string, as you do in Delphi. 

The dataset fields may be referenced in an expression, as the identifiers. For example: 

 

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

 

Or: 

 

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

 

If you need to create your own function and register it with AnyDAC, see uADStanExprFuncs.pas unit for details. 

Note, expression engine supports AnyDAC escape sequences. 

 

Using expression for custom purposes

An application may use AnyDAC expression evaluator to perform text formula calculations. There are two basic options:

  • use TADDataSet.CreateExpression method to perform calculations on the dataset fields. For example:

 

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

 

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

 

uses
  uADStanIntf, uADStanFactory, uADStanExpr, uADStanExprFuncs;

type
  TMyVariable = class (TInterfacedObject, IADStanExpressionDataSource)
  private
    FName: String;
    FpValue: PDouble;
  protected
    function GetVarIndex(const AName: String): Integer;
    function GetVarType(AIndex: Integer): TADDataType;
    function GetVarScope(AIndex: Integer): TADExpressionScopeKind;
    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): TADDataType;
begin
  if AIndex = 0 then
    Result := dtDouble
  else
    Result := dtUnknown;
end;

function TMyVariable.GetVarScope(AIndex: Integer): TADExpressionScopeKind;
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: IADStanExpressionDataSource;
  oParser: IADStanExpressionParser;
  oEval: IADStanExpressionEvaluator;
begin
  oDS := TMyVariable.Create('x', d);

  ADCreateInterface(IADStanExpressionParser, 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;
Example

See AnyDAC demos for more details:

  • AnyDAC\Samples\Comp Layer\TADQuery\Filter;
  • AnyDAC\Samples\DatS Layer\CalcColumnWithFuncs.
What do you think about this topic? Send feedback!