Sqlite (eight) custom function under Delphi FIREDAC

Sqlite itself does not have this function, FIREDAC through the tfdsqlitefunction added to the 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 score" 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 = 321 Height = DataSource = DataSource1 TabOrder = 0 titl Efont.charset = Default_charset Titlefont.color = Clwindowtext Titlefont.height = -11 titlefont.name = ' Tahoma ' Ti Tlefont.style = [] End object Button1:tbutton left = 382 top = Width = Height = Caption = ' Button1 ' TabOrder = 1 OnClick = Button1Click End object Button2:tbutton left = 382 top = 129 Width = Height = Ca ption = ' Button2 ' TabOrder = 2 OnClick = Button2click End object Fdconnection1:tfdconnection left = top = E nd object Fdphyssqlitedriverlink1:tfdphyssqlitedriverlink left = 143 top =-End Object Fdguixwaitcursor1:tfdguixwa Itcursor Provider = ' Forms ' left = the top = "End object Fdquery1:tfdquery Connection = FDConnection1 left = 344 top = The End object Datasource1:tdatasource DataSet = FDQuery1 left = 420 top = The End object Fdsqlitefunctio N1:tfdsqlitefunctionDriverlink = FDPhysSQLiteDriverLink1 Active = True functionname = ' MyFun1 ' argumentscount = 3 OnCalculate = Fdsqli Tefunction1calculate left = + End object Fdsqlitefunction2:tfdsqlitefunction driverlink = Fdphyssqlitedr IverLink1 Active = True functionname = ' MyFun2 ' argumentscount = 3 OnCalculate = Fdsqlitefunction2calculate Left = 152 top =

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 sheetbegin{Create a score sheet and insert test data}
  FDCONNECTION1.PARAMS.ADD (' Driverid=sqlite ');
  Fdconnection1.execsql (strtable);
  Fdquery1.execsql (' INSERT into MyTable (name, language, maths, English) VALUES (: 1,: 2,: 3,: 4) ', [' John ', 66, 77, 88]);
  Fdquery1.execsql (' INSERT into MyTable (name, language, maths, English) VALUES (: 1,: 2,: 3,: 4) ', [' Dick ', 77, 88, 99]);

  Fdquery1.open (' SELECT * from MyTable ');
  {Set parameters to two tfdsqlitefunction respectively}
  Fdsqlitefunction1.driverlink: = FDPhysSQLiteDriverLink1; Fdsqlitefunction1.functionname: = ' MyFun1 '; Fdsqlitefunction1.argumentscount of function Name: = 3; Number of parameters of function//fdsqlitefunction1.oncalculate: = Fdsqlitefunction1calculate;

  It is more convenient to establish oncalculate events at design time fdsqlitefunction1.active: = True;
  Fdsqlitefunction2.driverlink: = FDPhysSQLiteDriverLink1;
  Fdsqlitefunction2.functionname: = ' MyFun2 ';
  Fdsqlitefunction2.argumentscount: = 3; Fdsqlitefunction2.oncalculate: = fdsqlitefunction2calculate; It is more convenient to establish oncalculate events at design time fdsqlitefunction2.active: = True; End; {Calling MyFun1}procedureTform1.button1click (Sender:tobject);beginFdquery1.open (' SELECT name, MyFun1 (Chinese, maths, English) as total score from MyTable '); End; {Calling MyFun2}procedureTform1.button2click (Sender:tobject);beginFdquery1.open (' SELECT name, MyFun2 (Chinese, maths, English) as average points 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; {function MyFun2 definition: Average divide}procedureTform1.fdsqlitefunction2calculate (afunc:tsqlitefunctioninstance; ainputs:tsqliteinputs; Aoutput:tsqliteoutput;varAuserdata:tobject);beginAoutput.asfloat: = (ainputs[0]. Asinteger + ainputs[1]. Asinteger + ainputs[2]. Asinteger)/3; End; End.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/Programming/Delphi/