Devart Forums

Code: Select all

  LiteQuery1.DataTypeMap.AddFieldNameRule('Fct(ID)', ftFloat);

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils, Variants, DB, DBAccess, LiteAccess, LiteDataTypeMap;

type
  TMyClass = class
  private
    FLiteConnection: TLiteConnection;
    FLiteQuery: TLiteQuery;
    FLiteUserFunction: TLiteUserFunction;
  public
    constructor Create;
    destructor Destroy; override;

    procedure Open(const SQL: string);
    procedure ClearRule;
    procedure SetRule(const FiledName: String; DestType: TFieldType); overload;
    procedure SetRule(FiledType: SmallInt; DestType: TFieldType); overload;
    procedure onExecute(Sender: TObject; Params: TDAParams; var ResultValue: Variant);
  end;

{ TMyClass }

constructor TMyClass.Create;
begin
  FLiteConnection := TLiteConnection.Create(nil);
  FLiteConnection.Database := ':memory:';
  FLiteConnection.ExecSQL('create table Tbl (ID int)');
  FLiteConnection.ExecSQL('insert into Tbl values (1),(2)');

  FLiteQuery := TLiteQuery.Create(nil);
  FLiteQuery.Connection := FLiteConnection;

  FLiteUserFunction := TLiteUserFunction.Create(nil);
  FLiteUserFunction.Connection := FLiteConnection;
  FLiteUserFunction.FunctionName := 'Fct';
  FLiteUserFunction.Params.CreateParam(ftFloat, 'p1', ptInput);
  FLiteUserFunction.OnExecute := onExecute;



end;

destructor TMyClass.Destroy;
begin
  FLiteUserFunction.Free;
  FLiteQuery.Free;
  FLiteConnection.Free;

  inherited;
end;

procedure TMyClass.Open(const SQL: string);
var
  i: integer;
begin
  FLiteQuery.Close;
  FLiteQuery.SQL.Text := SQL;
  FLiteQuery.Open;

  while not FLiteQuery.Eof do begin
    for i := 0 to FLiteQuery.FieldCount - 1 do begin
      if FLiteQuery.Fields[i].IsNull then
        Write('Null' + #9)
      else
        Write(FloatToStr(FLiteQuery.Fields[i].AsFloat) + #9);
    end;
    Write(#13#10);
    FLiteQuery.Next
  end;
end;

procedure TMyClass.ClearRule;
begin
  FLiteQuery.DataTypeMap.Clear;
end;

procedure TMyClass.SetRule(const FiledName: String; DestType: TFieldType);
begin
  FLiteQuery.DataTypeMap.AddFieldNameRule(FiledName, DestType);
end;

procedure TMyClass.SetRule(FiledType: SmallInt; DestType: TFieldType);
begin
  FLiteQuery.DataTypeMap.AddDBTypeRule(FiledType, DestType);
end;

procedure TMyClass.onExecute(Sender: TObject; Params: TDAParams;
  var ResultValue: Variant);
begin
  if Params.Items[0].AsInteger = 1 then
    ResultValue := Null
  else
    ResultValue := 2.5;
end;

var
  MyClass: TMyClass;
begin
  MyClass := TMyClass.Create;
  try
    MyClass.Open('SELECT ID/3 FROM Tbl');
    //returns
    //  0
    //  0
    //Use FieldName  Rule
    writeln;
    MyClass.SetRule('FctField', ftFloat);
    MyClass.Open('select ID, Fct(ID) as FctField from Tbl order by ID');
    //returns
    //  1   Null
    //  2   2.5
    writeln;
    MyClass.Open('select ID, Fct(ID)  as FctField from Tbl order by ID desc');
    //  2   2.5
    //  1   Null

    //Use DBType Rule
    writeln;
    MyClass.ClearRule;
    MyClass.SetRule(liteNull, ftFloat);
    MyClass.Open('select ID, Fct(ID) as FctField from Tbl order by ID');
    //returns
    //  1   Null
    //  2   2.5
    writeln;
    MyClass.Open('select ID, Fct(ID)  as FctField from Tbl order by ID desc');
    //  2   2.5
    //  1   Null

    //Use DBType Rule both fields
    writeln;
    MyClass.ClearRule;
    MyClass.SetRule(liteNull, ftFloat);
    MyClass.Open('select ID,Fct(ID),Fct(ID+0) from Tbl');
    //returns
    //  1   Null    Null
    //  2   2.5    2.5
  finally
    MyClass.Free;
    readln;
  end;
end.