Sqlite under FireDAC [11]-About testing for batch-submitted SQL commands

You can quickly complete the form design by pasting the following code directly onto a blank form:
Object Dbgrid1:tdbgrid left = 0 Top = 0 Width = 265 Height = 338 Align = Alleft DataSource = DataSource1 TabOrder = 0 Titlefont.charset = Default_charset Titlefont.color = Clwindowtext Titlefont.height = -11 TitleFont.Name = ' Tahoma ' Titlefont.style = []endobject Button1:tbutton left = 280 Top = Width = Height = Caption = ' Button1 ' Tab Order = 1 OnClick = Button1clickendobject Button2:tbutton left = 280 Top = Width = Height = + Caption = ' but Ton2 ' TabOrder = 2 OnClick = Button2clickendobject Button3:tbutton left = 280 Top = 104 Width = at Height = Cap tion = ' Button3 ' taborder = 3 OnClick = Button3clickendobject Button4:tbutton left = 280 Top = 144 Width = Heigh t = Caption = ' Button4 ' taborder = 4 OnClick = Button4clickendobject Fdconnection1:tfdconnection left = the Top = 48endobject Fdphyssqlitedriverlink1:tfdphyssqlitedriverlink left = 167 Top = 48endobject fdguixwaitcursor1:tfdguixwait Cursor Provider = ' FormS ' left = 164 top = 120endobject fdquery1:tfdquery Connection = FDConnection1 left = 192endobject DATASOURC E1:tdatasource DataSet = FDQuery1 left = Top = 120end
Code:

 {set up}
procedure TForm1.FormCreate (Sender: TObject);
const
  strTable = ‘CREATE TABLE MyTable (Id integer PRIMARY KEY AUTOINCREMENT, Name string (10), Age integer)’; // Id (increment), Name, Age
begin
  FDConnection1.DriverName: = ‘SQLite’;
  FDQuery1.ExecSQL (strTable);
  FDQuery1.Open (‘SELECT * FROM MyTable’);
end;

{Insert one by one}
procedure TForm1.Button1Click (Sender: TObject);
const
  strInsert = ‘INSERT INTO MyTable (Name, Age) VALUES (: name,: age)’;
begin
// FDQuery1.FetchOptions.AutoClose: = True; // default
  FDQuery1.ExecSQL (strInsert, [‘A’, 1]);
  FDQuery1.ExecSQL (strInsert, [‘B’, 2]);
  FDQuery1.ExecSQL (strInsert, [‘C’, 3]);
  FDQuery1.ExecSQL (strInsert, [‘D’, 4]);

  FDQuery1.Open (‘SELECT * FROM MyTable’);
end;

{Separated with; one row insert}
procedure TForm1.Button2Click (Sender: TObject);
const
  strInsert = ‘INSERT INTO MyTable (Name, Age) VALUES ("% s ",% d) ';
var
  LStr: string;
begin
  LStr: = ‘‘;
  LStr: = LStr + Format (strInsert, [‘AA‘, 11]) + ‘;’;
  LStr: = LStr + Format (strInsert, [‘BB‘, 22]) + ‘;’;
  LStr: = LStr + Format (strInsert, [‘CC‘, 33]) + ‘;’;
  LStr: = LStr + Format (strInsert, [‘DD‘, 44]) + ‘;’;
  LStr: = LStr + ‘SELECT * FROM MyTable’;

  FDQuery1.ExecSQL (LStr);
  FDQuery1.Open ();
end;

{Extract and execute all commands using NextRecordSet method}
procedure TForm1.Button3Click (Sender: TObject);
const
  strInsert = ‘INSERT INTO MyTable (Name, Age) VALUES ("% s ",% d); ';
begin
  FDQuery1.FetchOptions.AutoClose: = False; // Supposedly this must be set, but it can also be set without testing
  FDQuery1.SQL.Clear;
  FDQuery1.SQL.Add (Format (strInsert, [‘AAA‘, 111]));
  FDQuery1.SQL.Add (Format (strInsert, [‘BBB‘, 222]));
  FDQuery1.SQL.Add (Format (strInsert, [‘CCC’, 333]));
  FDQuery1.SQL.Add (Format (strInsert, [‘DDD’, 444]));

  FDQuery1.SQL.Add (‘SELECT * FROM MyTable’);

  FDQuery1.Execute ();
  FDQuery1.NextRecordSet;
end;

{Using DML array parameters}
procedure TForm1.Button4Click (Sender: TObject);
const
  strInsert = ‘INSERT INTO MyTable (Name, Age) VALUES (: name,: age);’;
begin
  FDQuery1.FetchOptions.AutoClose: = False; //

  FDQuery1.SQL.Text: = strInsert;
  FDQuery1.Params.ArraySize: = 4; // Ready to execute the above statement 4 times

  {Parameters set 4 times separately}
  FDQuery1.Params [0] .AsStrings [0]: = ‘AAAA’;
  FDQuery1.Params [1] .AsIntegers [0]: = 1111;

  FDQuery1.Params [0] .AsStrings [1]: = ‘BBBB’;
  FDQuery1.Params [1] .AsIntegers [1]: = 2222;

  FDQuery1.Params [0] .AsStrings [2]: = ‘CCCC’;
  FDQuery1.Params [1] .AsIntegers [2]: = 3333;

  FDQuery1.Params [0] .AsStrings [3]: = ‘DDDD’;
  FDQuery1.Params [1] .AsIntegers [3]: = 4444;

  FDQuery1.Execute (4, 0); // Execute 4 times from 1

  FDQuery1.SQL.Add (‘SELECT * FROM MyTable’);
  FDQuery1.NextRecordSet;
end; 

Test:
In addition, you can also use the FireDAC extended SQL script (Tfdscript), which can also invoke SQL instructions directly in the file.