Insert Query not Applied in MySQL table

[EnterYourNameHere]

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]

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

[EnterYourNameHere]

I 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]

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]

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.