firedac SQLite, adding a function

[Serge_G 87]

Hi,

I am surely doing something wrong there, but I can't understand where.

 

I wrote a FDSQLiteFunction (well more than one)

All of these return me widestrings, I was expecting  Currency, Date, Currency. 

Note, first one came from help http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.SQLite_Sample

procedure TDM.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
// from sample  
AOutput.AsCurrency := AInputs[0].AsCurrency * AInputs[1].AsInteger;
end;

procedure TDM.FDSQLiteFunctionAAAAMMJJ2DateCalculate(
  AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs;
  AOutput: TSQLiteOutput; var AUserData: TObject);
var vSDate : String;
    vDate : TDate;
begin
vsDate:=AInputs[0].AsString;
vDate:=EncodeDate(StrToInt(Copy(vsdate,1,4)),
                  StrToInt(Copy(vsdate,5,2)),
                  StrToInt(Copy(vsdate,7,2)));
AOutput.AsDate:=vDate;
end;

procedure TDM.FDSQLiteFunctionMontantCalculate(
  AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs;
  AOutput: TSQLiteOutput; var AUserData: TObject);
var vMontant : Currency;
    IMontant : String;
begin
 IMontant:=AInputs[0].AsString;
 Vmontant:=StrToFloat(IMontant);
 if Ainputs[1].AsString='-'
    then vMontant:=Vmontant * -1;
 AOutput.AsCurrency:=vMontant;
end;

 

Don't understand why! Ok, it's at design time, but I think that matters.

Edited February 19, 2021 by Serge_G

[Guest]

5 hours ago, Serge_G said:

TDM.FDSQLiteFunctionAAAAMMJJ2DateCalculate

my opinion, using: solve one problem!!!

lOupPutDate := StrToDateTimeDEF('datex', <<date valid in case of error>>);

*******

 

5 hours ago, Serge_G said:

TDM.FDSQLiteFunctionMontantCalculate

why convert the Float in Str, and Str in Float again?

 

why not:

AInputs[0].AsDouble or equivalent directly, in "+" or "-" verification?

if ( x < 0) {negative} then {or AInput[1].Contains('-') }

AOutPut.AsDouble := := -AInputs[0].AsDouble

else

AOutPut.AsDouble := := AInputs[0].AsDouble;

 

of course, using "EXCEPT block" (two cases) for any error and one result default for it!

 

no needs any "var" use.

 

NOTE: if the value is negative, then (-(-...))= + 

.... and so...

 

you see?

Edited February 19, 2021 by Guest

[Serge_G 87]

 

 

48 minutes ago, emailx45 said:

why convert the Float in Str, and Str in Float again?

Well, there was a mistake in the database , column  aInput[0] is a string in French format so with " ," as decimal point

aInput[1] if a char '+' or '-'. Don't fire at the pianist, that's not my database!

 

 In the same way, the format of the date

vDate:=EncodeDate(StrToInt(Copy(vsdate,1,4)),
                  StrToInt(Copy(vsdate,5,2)),
                  StrToInt(Copy(vsdate,7,2)));

I am in doubt because of base 0,1 string and, yes, I forgot the StrToDateTimeDef

but problem remains.

I exposed my problem with the first fuction

procedure TDM.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
// from sample  
AOutput.AsCurrency := AInputs[0].AsCurrency * AInputs[1].AsInteger;
end;

And this one, see second picture, give me a widestring not a currency field

 

[Guest]

54 minutes ago, Serge_G said:

but problem remains.

maybe my solution help you now:

 

my SQLite DB

CREATE TABLE [myTableSQLite] (
[ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[VALUE1] FLOAT DEFAULT '''0''' NOT NULL,
[VALUE2] FLOAT DEFAULT '''0''' NOT NULL,
[VALUE_Type_Char] VARCHAR(1)  NOT NULL
)

 

 

my FORM in Text ... (just interessant part...)

   object FDConnection1: TFDConnection
    Params.Strings = (
      
        'Database=D:\RADRIOTests\VCL_SQLite_Functions_Test\mySQLiteDB.s3d' +
        'b'
      'StringFormat=Unicode'
      'DateTimeFormat=DateTime'
      'DriverID=SQLite')
    Connected = False
    LoginPrompt = False
    Transaction = FDTransaction1
    UpdateTransaction = FDTransaction1
    Left = 568
    Top = 16
  end
  object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink
    Left = 704
    Top = 16
  end
  object FDGUIxWaitCursor1: TFDGUIxWaitCursor
    Provider = 'Forms'
    ScreenCursor = gcrHourGlass
    Left = 832
    Top = 16
  end
  object DataSource1: TDataSource
    DataSet = FDQuery1
    Left = 424
    Top = 256
  end
  object FDQuery1: TFDQuery
    BeforeEdit = FDQuery1BeforeEdit
    BeforePost = FDQuery1BeforePost
    AfterPost = FDQuery1AfterPost
    Connection = FDConnection1
    SQL.Strings = (
      
        'SELECT *, myMultFunc(VALUE1, VALUE2, VALUE_Type_Char)as ResultMu' +
        'lt,'
      'myStringToDateFunc("19/02/2021") as ResultDate'
      'FROM myTableSQLite')
    Left = 560
    Top = 80
    object FDQuery1ID: TFDAutoIncField
      FieldName = 'ID'
      Origin = 'ID'
      ProviderFlags = [pfInWhere, pfInKey]
      ReadOnly = True
    end
    object FDQuery1VALUE1: TFloatField
      FieldName = 'VALUE1'
      Origin = 'VALUE1'
      Required = True
    end
    object FDQuery1VALUE2: TFloatField
      FieldName = 'VALUE2'
      Origin = 'VALUE2'
      Required = True
    end
    object FDQuery1VALUE_Type_Char: TWideStringField
      FieldName = 'VALUE_Type_Char'
      Origin = 'VALUE_Type_Char'
      Required = True
      Size = 1
    end
    object FDQuery1ResultMult: TWideStringField
      AutoGenerateValue = arDefault
      FieldName = 'ResultMult'
      Origin = 'ResultMult'
      ProviderFlags = []
      ReadOnly = True
      Size = 32767
    end
    object FDQuery1ResultDate: TWideStringField
      AutoGenerateValue = arDefault
      FieldName = 'ResultDate'
      Origin = 'ResultDate'
      ProviderFlags = []
      ReadOnly = True
      Size = 32767
    end
  end
  object FDTransaction1: TFDTransaction
    Connection = FDConnection1
    Left = 464
    Top = 16
  end
  object myFDSQLiteMultFunc: TFDSQLiteFunction
    DriverLink = FDPhysSQLiteDriverLink1
    FunctionName = 'myMultFunc'
    ArgumentsCount = 3
    OnCalculate = myFDSQLiteMultFuncCalculate
    Left = 664
    Top = 72
  end
  object myFDSQLiteStrintToDate: TFDSQLiteFunction
    DriverLink = FDPhysSQLiteDriverLink1
    FunctionName = 'myStringToDateFunc'
    ArgumentsCount = 1
    OnCalculate = myFDSQLiteStrintToDateCalculate
    Left = 808
    Top = 80
  end

 

type
  TForm1 = class(TForm)
    FDConnection1: TFDConnection;
    FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    DataSource1: TDataSource;
    FDQuery1: TFDQuery;
    FDTransaction1: TFDTransaction;
    myFDSQLiteMultFunc: TFDSQLiteFunction;
    DBGrid1: TDBGrid;
    Button1: TButton;
    DBNavigator1: TDBNavigator;
    myFDSQLiteStrintToDate: TFDSQLiteFunction;
    FDQuery1ID: TFDAutoIncField;
    FDQuery1VALUE1: TFloatField;
    FDQuery1VALUE2: TFloatField;
    FDQuery1VALUE_Type_Char: TWideStringField;
    FDQuery1ResultMult: TWideStringField;
    FDQuery1ResultDate: TWideStringField;
    procedure myFDSQLiteMultFuncCalculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FDQuery1AfterPost(DataSet: TDataSet);
    procedure FDQuery1BeforeEdit(DataSet: TDataSet);
    procedure FDQuery1BeforePost(DataSet: TDataSet);
    procedure myFDSQLiteStrintToDateCalculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses
  System.DateUtils;

var
  iFDQryRecno: Integer = 0;
  bFDQryState: TDataSetState;

procedure TForm1.Button1Click(Sender: TObject);
begin
  if FDConnection1.Connected then
  begin
    FDConnection1.Close;
    myFDSQLiteMultFunc.Active     := false;
    myFDSQLiteStrintToDate.Active := false;
  end else begin
    FDConnection1.Open;
    myFDSQLiteMultFunc.Active     := true;
    myFDSQLiteStrintToDate.Active := true;
    FDQuery1.Open;
  end;
  //
end;

procedure TForm1.FDQuery1AfterPost(DataSet: TDataSet);
begin
  FDQuery1.Disconnect(false);
  FDQuery1.Open(); { }
  //
  case bFDQryState of
    TDataSetState.dsInsert:
      FDQuery1.Last;
    TDataSetState.dsEdit:
      FDQuery1.RecNo := iFDQryRecno;
  end;

end;

procedure TForm1.FDQuery1BeforeEdit(DataSet: TDataSet);
begin
  iFDQryRecno := FDQuery1.RecNo;
end;

procedure TForm1.FDQuery1BeforePost(DataSet: TDataSet);
begin
  bFDQryState := FDQuery1.State;
end;

procedure TForm1.myFDSQLiteMultFuncCalculate( { }
  AFunc: TSQLiteFunctionInstance;             { }
  AInputs: TSQLiteInputs;                     { }
  AOutput: TSQLiteOutput;                     { }
  var AUserData: TObject);
begin
  try
    if (AInputs[2].AsString = '-') then
      AOutput.AsCurrency := AInputs[0].AsCurrency * -AInputs[1].AsCurrency { or using: ABS( input 1) for avoid "-(-(..))" }
    else
      AOutput.AsCurrency := AInputs[0].AsCurrency * AInputs[1].AsCurrency;
  except
    AOutput.AsCurrency := 0;
  end;
end;

procedure TForm1.myFDSQLiteStrintToDateCalculate( { }
  AFunc: TSQLiteFunctionInstance;                 { }
  AInputs: TSQLiteInputs;                         { }
  AOutput: TSQLiteOutput;                         { }
  var AUserData: TObject);
begin
  try
    AOutput.AsString := DateToStr(StrToDateDef(AInputs[0].AsString, now));
  except
    AOutput.AsString := DateToStr(now); { some valid value }
  end;
end;

 

NOTE: better dont use "reserverd words" like "Result" as your fieldnames, or, similar!

 

hug

Edited February 19, 2021 by Guest

[Serge_G 87]

Well, I was thinking it was a sort of VCL vs FMX thing but no.

If I look at your grid I see that resultmult column is as string (align left)

Like me have a widestringfield

    object FDQuery1MNT: TWideStringField
      AutoGenerateValue = arDefault
      FieldName = 'MNT'
      Origin = 'MNT'
      ProviderFlags = []
      ReadOnly = True
      Size = 32767
    end

And this is my problem, if you use Livebindings a real one at design time!

 

A note for this function

// Select id,montant,sens,
// testmnt(sens,montant) as MNT, --  function1
// asIntvalue(sens) as IntSens, -- function2   
// montant*asIntvalue(sens) MNTBIS from ecritures

procedure TForm11.FDSQLiteFunction2Calculate(AFunc: TSQLiteFunctionInstance;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
try
if AInputs[0].AsString='+'
  then AOutput.AsInteger:=1
  else AOutput.AsInteger:=-1;
except
  Aoutput.AsInteger:=0;
end;
end;

if you declare fields at design time you have a widestringfield, no fields declared it is a largeint value and MNTBIS a currency value

[Dmitry Arefiev 101]

SQLite is really typeless database. When you use AOutput.AsCurrency, then FireDAC maps this to SQLITE_TEXT (to keep precision), and AOutput.AsFloat to SQLITE_FLOAT. To "remap" an expression in SELECT list to a Delphi specific type you should use special construction in SELECT list:

<expression> AS "<alias>::<type>"

More about that:
http://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

[Serge_G 87]

Thanks, Dmitry, I was not aware of this special construction to remap  a column.

This does the trick 🙇‍♂️  and brighten my day 

[Guest]

1 hour ago, Serge_G said:

I was not aware of this special construction to remap  a column.

another thing, DONT FORGET OF "RE-MAP" by FireDAC options!

there, you can do it too!

Include the definition have an "inherit" "VERTICAL", or be, definining on FDManager -> FDConnection -> FDQuery ... and so!

Then, you can re-map a Field on FDConnection, and all FDQuerys/FDDatasets will inherit it

try this.

 

 

hug

Edited February 20, 2021 by Guest

[Guest]

I Like YOU B U T I D O NO t Hv🤦‍♂️Te server-code from the client?! Or are you building a server-side plugin?CAPS why?++++

WHY ALL CAPS++

It is not reDABLE.

[Serge_G 87]

3 hours ago, emailx45 said:

another thing, DONT FORGET OF "RE-MAP" by FireDAC options!

there, you can do it too!

Include the definition have an "inherit" "VERTICAL", or be, definining on FDManager -> FDConnection -> FDQuery ... and so!

Then, you can re-map a Field on FDConnection, and all FDQuerys/FDDatasets will inherit it 

try this.

 

 

hug

I try this before Dmitry response, but does not work at design time for declaring fields (keep in mind : Livebindings)

Thanks anyway.

 

Is there anyway to flag discussion as solved on this forum?

Edited February 20, 2021 by Serge_G

[Attila Kovacs 629]

@Dany Marmur By the way, do you know why SQL Queries are written in capital letters? 😉

[Guest]

2 hours ago, Serge_G said:

I try this before Dmitry response, but does not work at design time for declaring fields (keep in mind : Livebindings)

doesnt works because the "FD-FUNCTIONS" ARE RESOLVED IN RUN-TIME LIKE "CALCULATE FIELDS", OF COURSE, MORE FLEXIBLE - if not reADABLE!

 

To make custom functions ACCESSIBLE AT DESIG-TIME, CREATE A CUSTOM-DESIGN-TIME PACKAGE WITH A DATAMODULE, DROP THE COMPONENTS ON THIS MODULE, AND SET UP PROPERLY.

• Create the module in the module unit initialization section and destroy it in the finalization section.
• After this, install your package into the Object Pascal IDE.

 

NOTE: in time, this works in LiveBinding tech! same that is not explicitly revelated in your initial post!

 

hug and GOOD READS!

 

Edited February 20, 2021 by Guest

[Guest]

@Attila Kovacs, no, i moved over to lower-case some five years ago. More readable, methinks.

[Attila Kovacs 629]

@Dany Marmur Because the server works faster if you are shouting 😛

[Fr0sT.Brutal 900]

On 2/21/2021 at 11:27 AM, Attila Kovacs said:

@Dany Marmur Because the server works faster if you are shouting 😛

Hmm don't you know that uppercase letters occupy more memory causing overall slowdown?!

😄

 

[Guest]

17 minutes ago, Fr0sT.Brutal said:

Hmm don't you know that uppercase letters occupy more memory causing overall slowdown?!

it's true!

imagine how many pixels is necessary to  form it on screen.

 

hug