Insert Query not Applied in MySQL table
lazarus / 16-Databases

----------------------------------------------------------------------
EnterYourNameHere · 2014-12-23 15:03

Hi EveryOne

I'm relatively new to Lazarus but an old Delphi addept. I came across
a problem i can't tackle. The Following code makes a temporarely
change in a DBGRid but not in the underlying table.

Code: [Select]With DataModule1.SQLQuery1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('INSERT INTO OETabel (OETabelId, OETabelNaam,
OETabelParent) VALUES (NULL, "Test", "Test")');
      ExecSQL;
      SQL.Clear;
      SQL.Add('SELECT * FROM OETabel');
      ExecSQL;
      Open;
    end;
The Query itself works fine in MySQL workbench and it shows that the
autoinc. id field is incremented too. But the table (OETabel) is not
affected. Like there is missing a Post command in old Delphi.

Any help would be much appreciated

----------------------------------------------------------------------
kpeters58 · 2014-12-23 16:35

I do not see any Commit statement - so that would most likely be your
problem

----------------------------------------------------------------------
EnterYourNameHere · 2014-12-23 20:55

Quote from: kpeters58 on December 23, 2014, 05:35:33 pmI do not see
any Commit statement - so that would most likely be your problem

Thanks a lot

This works

Code: [Select]  With DataModule1.SQLQuery1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('INSERT INTO OETabel (OETabelId, OETabelNaam,
OETabelParent) VALUES (NULL, "Test", "Test")');
      ExecSQL;
    end;
  DataModule1.SQLTransaction1.Active := True;
  DataModule1.SQLTransaction1.Commit;
  With DataModule1.SQLQuery1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM OETabel');
      ExecSQL;
      Open;
      ApplyUpdates;
     end; 
Is there any way to simplify this. It looks redundant to close and
clear twice. If its lean then i like it too.

----------------------------------------------------------------------
kpeters58 · 2014-12-24 01:47

Sure - here you go....(Caveat: Air code - untested)

Code: [Select]  var
    q: TSQLQuery;
  //...
  q := DataModule1.SQLQuery1;
  q.Close;
  q.SQL.Text := 'INSERT INTO OETabel (OETabelId, OETabelNaam,
OETabelParent) VALUES (NULL, "Test", "Test")';
  q.ExecSQL;
  q.ApplyUpdates(0);
  TSQLTransaction(q.Transaction).CommitRetaining;
  q.Close;
  q.SQL.Text := 'SELECT * FROM OETabel';
  q.Open;
  // done

----------------------------------------------------------------------
EnterYourNameHere · 2014-12-24 21:07

Tnx, Works like a charm except for ApplyUpdates gave an error "can't
perform this task on a inactive dataset" Moving that line to the end
of the code solved the problem.