Jump to content
Serge_G

SQLite, adding a function

Recommended Posts

Hi,

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

 

I wrote a FDSQLiteFunction (well more than one)

image.png.7b8011fa775a515f3c9cc02c5ee22094.png

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;

image.png.2abf4b0ab66c2ddc47a486d54c8f62e2.png

 

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

Edited by Serge_G

Share this post


Link to post
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 by Guest

Share this post


Link to post

 

 

48 minutes ago, emailx45 said:

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

Well, there was a mistake in the database :classic_blush: , 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

 

Share this post


Link to post
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
)

 

image.thumb.png.5c3913aa0d60224a0d7fdab30009ee69.png

 

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 by Guest

Share this post


Link to post

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

image.png.50b11012dfb184252e0c1ae75d2f1ca1.png

Share this post


Link to post

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

  • Thanks 1

Share this post


Link to post

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

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

Share this post


Link to post
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.

 

image.png.13009ad3b3edd8353cf4aad1f9b02a3a.png

 

hug

Edited by Guest

Share this post


Link to post
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.

Share this post


Link to post
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.

 

image.png.13009ad3b3edd8353cf4aad1f9b02a3a.png

 

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 by Serge_G
  • Like 1

Share this post


Link to post
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 by Guest

Share this post


Link to post
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?!

😄

 

Share this post


Link to post
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

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×