Sqlite under FireDAC [8]-Custom functions

Sqlite itself does not have this function, FireDAC through tfdsqlitefunction added this function; Although similar effects can be achieved through some SQL statements or through views, the functions are more flexible.

This example first built a score table, and then through two tfdsqlitefunction to achieve the "total score" and "average" calculation.
You can copy the contents of the following text box and paste it directly onto the form to quickly complete the form design:
Object Dbgrid1:tdbgrid left = 8 Top = Width = 321 Height = DataSource = DataSource1 TabOrder = 0 titlefont.c Harset = Default_charset Titlefont.color = Clwindowtext Titlefont.height = -11 titlefont.name = ' Tahoma ' TitleFont.Sty Le = []endobject Button1:tbutton left = 382 Top = Width = Height = + Caption = ' Button1 ' taborder = 1 oncli ck = Button1clickendobject Button2:tbutton left = 382 Top = 129 Width = Height = Caption = ' Button2 ' TabOrder = 2 OnClick = Button2clickendobject Fdconnection1:tfdconnection left = Top = 24endobject FDPhysSQLiteDriverLink1: Tfdphyssqlitedriverlink left = 143 Top = 24endobject fdguixwaitcursor1:tfdguixwaitcursor Provider = ' Forms ' left = 26 0 top = 24endobject fdquery1:tfdquery Connection = FDConnection1 left = 344 Top = 24endobject DATASOURCE1:TDATASOURC E DataSet = FDQuery1 left = 420 Top = 24endobject fdsqlitefunction1:tfdsqlitefunction Driverlink = fdphyssqlitedriver Link1 Active = True FuNctionname = ' MyFun1 ' argumentscount = 3 OnCalculate = Fdsqlitefunction1calculate left = Top = 200endobject fdsqlit Efunction2:tfdsqlitefunction Driverlink = FDPhysSQLiteDriverLink1 Active = True functionname = ' MyFun2 ' Argumentscoun t = 3 OnCalculate = Fdsqlitefunction2calculate left = Top = 200end
Code:

UnitUnit1;InterfaceusesWinapi.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.Stan.ExprFuncs, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, Firedac.dats, FireDAC.DApt.Intf, Firedac.dapt, Vcl.grids, Vcl.dbgrids, Data.db, FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Comp.UI, FireDAC.Phys.SQLite, Vcl.stdctrls, FireDAC.Phys.SQLiteWrapper;typeTForm1 =class(Tform)    Fdconnection1:tfdconnection;    Fdphyssqlitedriverlink1:tfdphyssqlitedriverlink;    Fdguixwaitcursor1:tfdguixwaitcursor;    Fdquery1:tfdquery;    Datasource1:tdatasource;    Dbgrid1:tdbgrid;    Button1:tbutton;    Button2:tbutton;    Fdsqlitefunction1:tfdsqlitefunction; Fdsqlitefunction2:tfdsqlitefunction;procedureFormcreate (Sender:tobject);procedureButton1Click (Sender:tobject);procedureButton2click (Sender:tobject);procedureFdsqlitefunction1calculate (afunc:tsqlitefunctioninstance; ainputs:tsqliteinputs; Aoutput:tsqliteoutput;varAuserdata:tobject);procedureFdsqlitefunction2calculate (afunc:tsqlitefunctioninstance; ainputs:tsqliteinputs; Aoutput:tsqliteoutput;varAuserdata:tobject);Private{Private declarations} Public{Public declarations}End;varForm1:tform1;Implementation{$R *.DFM}procedureTform1.formcreate (Sender:tobject);Conststrtable = ' CREATE TABLE MyTable (name string (10), language integer, Math Integer, English integer) '; Build a Student score tablebegin{Create a score table and insert test data}FDCONNECTION1.PARAMS.ADD (' Driverid=sqlite ');  Fdconnection1.execsql (strtable); Fdquery1.execsql (' INSERT into MyTable (name, language, Math, English) VALUES (: 1,: 2,: 3,: 4) ', [' Zhang San ', 66, 77, 88]); Fdquery1.execsql (' INSERT into MyTable (name, language, Math, English) VALUES (: 1,: 2,: 3,: 4) ', [' John Doe ', 77, 88, 99]); Fdquery1.open (' SELECT * from MyTable '); {Set parameters to two tfdsqlitefunction respectively}Fdsqlitefunction1.driverlink: = FDPhysSQLiteDriverLink1; Fdsqlitefunction1.functionname: = ' MyFun1 '; Name of functionFdsqlitefunction1.argumentscount: = 3; The number of arguments to the functionFdsqlitefunction1.oncalculate: = fdsqlitefunction1calculate; Establishing OnCalculate events at design time is more convenientFdsqlitefunction1.active: = True;  Fdsqlitefunction2.driverlink: = FDPhysSQLiteDriverLink1; Fdsqlitefunction2.functionname: = ' MyFun2 '; Fdsqlitefunction2.argumentscount: = 3; Fdsqlitefunction2.oncalculate: = fdsqlitefunction2calculate; Establishing OnCalculate events at design time is more convenientFdsqlitefunction2.active: = True;End; {Call MyFun1}procedureTform1.button1click (Sender:tobject);beginFdquery1.open (' SELECT name, MyFun1 (Chinese, maths, English) as total score from MyTable ');End; {Call MyFun2}procedureTform1.button2click (Sender:tobject);beginFdquery1.open (' SELECT name, MyFun2 (Chinese, maths, English) as average from MyTable ');End; {Definition of function MyFun1: Total score}procedureTform1.fdsqlitefunction1calculate (afunc:tsqlitefunctioninstance; ainputs:tsqliteinputs; Aoutput:tsqliteoutput;varAuserdata:tobject);beginAoutput.asinteger: = Ainputs[0]. Asinteger + ainputs[1]. Asinteger + ainputs[2]. Asinteger;End; {Definition of function MyFun2: Average score}procedureTform1.fdsqlitefunction2calculate (afunc:tsqlitefunctioninstance; ainputs:tsqliteinputs; Aoutput:tsqliteoutput;varAuserdata:tobject);beginAoutput.asfloat: = (ainputs[0]. Asinteger + ainputs[1]. Asinteger + ainputs[2]. Asinteger)/3;End;End.

: