RAD Studio Ease way to How Map Field Types from Database to another Type in RAD Studio FireDAC - sample BCD to Currency type

[emailx45]

Ease way to How Map Field Types from Database to another Type in RAD Studio FireDAC - sample BCD to Currency type
by Emailx45

Спойлер: Content

[SHOWTOGROUPS=4,20,22]

• Scenary:
  • MSWindows 10 Enterprise 1909 updated
  • RAD Studio 10.3.3 RIO Arch
  • Interbase v2020 64bits
• 
  
• This samples is for show how do you can map a field from your table and use it how another type (respecting some rules of equivalence) in your app in Delphi, for example!

/* Table: MYORACLETB, Owner: SYSDBA */
CREATE TABLE MYORACLETB
(
        ID    INTEGER NOT NULL,
        MYNUMERIC2X0    NUMERIC(2, 0),
        MYNUMERIC4X0    NUMERIC(4, 0),
        MYNUMERIC8X0    NUMERIC(8, 0),
        MYNUMERIC18X0    NUMERIC(18, 0),
        MYNUMERIC20X4    NUMERIC(18, 4)
);

/* Meta data descriptions.  This syntax requires InterBase 2020 or higher.  Some tables require ODS18 and higher */

Спойлер: Source code on Delphi

unit uFormMain;

interface

uses
  Winapi.Windows,
  Winapi.Messages,
  System.SysUtils,
  System.Variants,
  System.Classes,
  Vcl.Graphics,
  Vcl.Controls,
  Vcl.Forms,
  Vcl.Dialogs,
  FireDAC.Stan.Intf,
  FireDAC.Stan.Option,
  FireDAC.Stan.Error,
  FireDAC.UI.Intf,
  FireDAC.Phys.Intf,
  FireDAC.Stan.Def,
  FireDAC.Stan.Pool,
  FireDAC.Stan.Async,
  FireDAC.Phys,
  FireDAC.VCLUI.Wait,
  Data.DB,
  FireDAC.Comp.Client,
  Vcl.StdCtrls,
  FireDAC.Stan.Param,
  FireDAC.DatS,
  FireDAC.DApt.Intf,
  FireDAC.DApt,
  FireDAC.Comp.DataSet,
  FireDAC.Phys.IBDef,
  FireDAC.Comp.UI,
  FireDAC.Phys.IBBase,
  FireDAC.Phys.IB;

type
  TForm1 = class(TForm)
    FDConnection1: TFDConnection;
    btnFDConnADD_MapRules: TButton;
    FDQuery1: TFDQuery;
    btnFDQueryVERIFY_FielsTypes: TButton;
    Memo1: TMemo;
    FDPhysIBDriverLink1: TFDPhysIBDriverLink;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDQuery1ID: TIntegerField;
    FDQuery1MYNUMERIC2X0: TSmallintField;
    FDQuery1MYNUMERIC4X0: TSmallintField;
    FDQuery1MYNUMERIC8X0: TIntegerField;
    FDQuery1MYNUMERIC18X0: TLargeintField;
    btnHowMany_FDQueryMapRules: TButton;
    btnClear_FDConnMapRules: TButton;
    procedure btnFDConnADD_MapRulesClick(Sender: TObject);
    procedure btnFDQueryVERIFY_FielsTypesClick(Sender: TObject);
    procedure btnHowMany_FDQueryMapRulesClick(Sender: TObject);
    procedure btnClear_FDConnMapRulesClick(Sender: TObject);
  private
    procedure prcHowManyMapRules;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses
  System.TypInfo;

procedure prcMemoLog(lText: string);
begin
  if not(lText.Trim = '') then
    Form1.Memo1.Lines.Add(lText);
end;

function fncMyNewRule(lNewRule: TFDMapRule; lPrecMax, lPrecMin, lScaleMax, lScaleMin: Integer; lSourceDataType, lTargetDataType: TFDDataType): TFDMapRule;
begin
  result := nil;
  // if necessary use try-except-block for security
  try
    lNewRule.PrecMax        := lPrecMax;
    lNewRule.PrecMin        := lPrecMin;
    lNewRule.ScaleMax       := lScaleMax;
    lNewRule.ScaleMin       := lScaleMin;
    lNewRule.SourceDataType := lSourceDataType;
    lNewRule.TargetDataType := lTargetDataType;
    //
  finally
    result := lNewRule;
  end;
end;

procedure TForm1.btnClear_FDConnMapRulesClick(Sender: TObject);
begin
  FDConnection1.Close;
  //
  FDConnection1.FormatOptions.MapRules.Clear;
  //
  Memo1.Lines.Clear;
  //
  prcHowManyMapRules;
end;

procedure TForm1.btnFDConnADD_MapRulesClick(Sender: TObject);
var
  lMyMapRule: TFDMapRule;
begin
  btnClear_FDConnMapRules.Click;
  //
  FDConnection1.FormatOptions.AssignedValues := [fvMapRules];
  FDConnection1.FormatOptions.OwnMapRules    := true;
  //
  // Oracle NUMERIC(2,0) to integers type in Delphi (BYTE)
  lMyMapRule := fncMyNewRule(FDConnection1.FormatOptions.MapRules.Add, { }
    2, 0, 0, 0, dtBCD, dtSByte);
  // Oracle NUMERIC(4,0) to integers type in Delphi (Int16)
  lMyMapRule := fncMyNewRule(FDConnection1.FormatOptions.MapRules.Add, { }
    4, 0, 0, 0, dtBCD, dtInt16);
  // Oracle NUMERIC(8,0) to integers type in Delphi (Int32)
  lMyMapRule := fncMyNewRule(FDConnection1.FormatOptions.MapRules.Add, { }
    8, 0, 0, 0, dtBCD, dtInt32);
  // Oracle NUMERIC(18,0) to float type in Delphi (Currency)
  lMyMapRule := fncMyNewRule(FDConnection1.FormatOptions.MapRules.Add, { }
    18, 0, 0, 0, dtBCD, dtCurrency);                                   // will be a "ftLargeint";
  //
  // Oracle NUMERIC(18,4) to float type in Delphi (Currency)
  lMyMapRule := fncMyNewRule(FDConnection1.FormatOptions.MapRules.Add, { }
    18, 0, 4, 0, dtBCD, dtCurrency);                                   // will be a "ftCurrency"
  //
  Memo1.Lines.Clear;
  //
  prcHowManyMapRules;
end;

procedure TForm1.btnFDQueryVERIFY_FielsTypesClick(Sender: TObject);
var
  i: Integer;
begin
  Memo1.Lines.Clear;
  //
  if not FDQuery1.Active then
    FDQuery1.Open();
  //
  for i := 0 to Pred(FDQuery1.FieldDefs.Count) do
  begin
    prcMemoLog(                                                                         { }
      Format('%.2d, %s, %s, %s, %s, %s', [                                              { }
          FDQuery1.FieldDefs.Items[i].FieldNo,                                          { }
          FDQuery1.FieldDefs.Items[i].Name,                                             { }
          GetEnumName(TypeInfo(TFieldType), Ord(FDQuery1.FieldDefs.Items[i].DataType)), { }
          FDQuery1.FieldDefs.Items[i].FieldClass.ClassName,                             { }
          FDQuery1.FieldDefs.Items[i].Size.ToString,                                    { }
          FDQuery1.FieldDefs.Items[i].Precision.ToString                                { }
        ])                                                                              { }
      );
  end;
  //
  prcMemoLog('...');
  prcMemoLog('***** Pay attention on 2 last "Field definition: dtBCD to dtCurrency"... *****');
  //
  FDQuery1.Close;
end;

procedure TForm1.btnHowMany_FDQueryMapRulesClick(Sender: TObject);
var
  i: Integer;
begin
  Memo1.Lines.Clear;
  //
  prcHowManyMapRules;
end;

procedure TForm1.prcHowManyMapRules;
var
  i: Integer;
begin
  if (FDQuery1.FormatOptions.MapRules.Count > 0) then
  begin
    for i := 0 to Pred(FDQuery1.FormatOptions.MapRules.Count) do
    begin
      prcMemoLog(                                                                                       { }
        Format('%.2d, %s, %s, %d, %d, %d, %d, %d, %d, %s, %s, %s, %s', [                                { }
            FDQuery1.FormatOptions.MapRules[i].ID,                                                      { }
            FDQuery1.FormatOptions.MapRules[i].DisplayName,                                             { }
            FDQuery1.FormatOptions.MapRules[i].GetNamePath,                                             { }
            FDQuery1.FormatOptions.MapRules[i].PrecMax,                                                 { }
            FDQuery1.FormatOptions.MapRules[i].PrecMin,                                                 { }
            FDQuery1.FormatOptions.MapRules[i].ScaleMax,                                                { }
            FDQuery1.FormatOptions.MapRules[i].ScaleMin,                                                { }
            FDQuery1.FormatOptions.MapRules[i].SizeMax,                                                 { }
            FDQuery1.FormatOptions.MapRules[i].SizeMin,                                                 { }
            FDQuery1.FormatOptions.MapRules[i].TypeMask,                                                { }
            FDQuery1.FormatOptions.MapRules[i].NameMask,                                                { }
            GetEnumName(TypeInfo(TFDDataType), Ord(FDQuery1.FormatOptions.MapRules[i].SourceDataType)), { }
            GetEnumName(TypeInfo(TFDDataType), Ord(FDQuery1.FormatOptions.MapRules[i].TargetDataType))  { }
          ])                                                                                            { }
        );
    end;
    //
    prcMemoLog('...');
    prcMemoLog('***** Pay attention on 2 last "Rule": ftBCD = TBCDField type *****');
  end
  else
    prcMemoLog(Format('FDConnection / FDQuery Rules: %d', [FDQuery1.FormatOptions.MapRules.Count]));
end;

end.

How I did the test:
1) I dont have Oracle installed... then, using a Interbase Database for test
1.1) Was created a table with this structure:
/* Table: MYORACLETB, Owner: SYSDBA */
CREATE TABLE MYORACLETB
(
ID INTEGER NOT NULL,
MYNUMERIC2X0 NUMERIC(2, 0),
MYNUMERIC4X0 NUMERIC(4, 0),
MYNUMERIC8X0 NUMERIC(8, 0),
MYNUMERIC18X0 NUMERIC(18, 0),
MYNUMERIC20X4 NUMERIC(18, 4)
);

2) Now, I can test my code in Delphi 10.3.3 Arch

3) Click button "btnFDQueryVERIFY_FielsTypes" to see the table fields as "captured" from Database

4) Now, click button "btnFDConnADD_MapRules" to add new "rules" on FDConnection and used for all datasets connected to him

5) Now, Click button "btnFDQueryVERIFY_FielsTypes" again to see new definitions using the new rules for the fields type

[/SHOWTOGROUPS]