Lazarus

Programming => Databases => Topic started by: madref on April 01, 2015, 12:04:55 pm

Title: [SOLVED] Database won't update
Post by: madref on April 01, 2015, 12:04:55 pm

i have managed to create a database using SQLite. I can fill the database and change data.
All the changes are visibly seen, but the changed aren't permanent. Because when i close my application and thus the database none of the changes are saved.


How to do that?


This is the code i use for making the change in a subform.

Code: [Select]

procedure TForm_RefereeInformation.Button_Referee_OKClick(Sender: TObject);
var lkq: TSQLQuery;
    maxid: integer;
begin
  lkq := TSQLQuery.Create(self);
  lkq.DataBase:=Form_RefereeMain.SQLite3Connection_RefereeDB;


  if Referee_ID <> 0 then   // een bestaande wedstrijd wordt/is aangepast
    begin
      lkq.SQL.Clear;
      lkq.SQL.Text := 'SELECT * FROM tbl_Scheidsrechters ' +
                      'WHERE Scheids_ID=' + IntToStr(Referee_ID) + ';';
      lkq.Active:=True;
      lkq.Edit;
      lkq.FieldByName('Scheids_ID').AsInteger := Referee_ID;
    end
  else begin      // een nieuwe scheidsrechter aanmaken
    // Bepaal de laatse Scheids_ID
    lkq.SQL.Clear;
    lkq.SQL.Text := 'SELECT MAX(Scheids_ID) AS MaxID FROM tbl_Scheidsrechters;';
    lkq.Open;
    maxid := strtointdef(lkq.Fields[0].AsString,0);
    lkq.Close;
    lkq.SQL.Clear;
    lkq.SQL.Text := 'SELECT * FROM tbl_Scheidsrechters;';
    lkq.Open;
    lkq.Insert;
    lkq.FieldByName('Scheids_ID').AsInteger := maxid+1;
  end;  // if Referee_ID
  if lkq.Active = False then lkq.Active:=True;
  lkq.FieldByName('Voornaam').AsString := Edit_Voornaam.Text;
  lkq.FieldByName('Tussenvoegsel').AsString := Edit_Tussen.Text;
  lkq.FieldByName('Achternaam').AsString := Edit_Achternaam.Text;
  lkq.FieldByName('Adres').AsString := Edit_Adres.Text;
  lkq.FieldByName('Postcode').AsString := Edit_Postcode.Text;
  lkq.FieldByName('Woonplaats').AsString := Edit_Plaats.Text;
  lkq.FieldByName('Telefoon').AsString := Edit_Telefoon.Text;
  lkq.FieldByName('Mobiel').AsString := Edit_Mobiel.Text;
  if Edit_Geboortedatum.Text = '' then
    lkq.FieldByName('Geboortedatum').AsString := ''
  else
    lkq.FieldByName('Geboortedatum').AsDateTime := StrToDate (Edit_Geboortedatum.Text);
  lkq.FieldByName('Email').AsString := Edit_Email.Text;


  //Sla het op in de tabel
  lkq.Post;
  lkq.ApplyUpdates;
  // Query uit het geheugen halen.
  lkq.Active := False;
  lkq.Free;
  Form_RefereeMain.SQLQuery_GridReferees.Refresh;
  Close;
end;     // Button_Referee_OKClick


And this is the code i use to close the main form.

Code: [Select]


procedure TForm_RefereeMain.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery_GridLicenties.Close;
  SQLQuery_GridReferees.Close;
  SQLTransaction_RefereeDB.Active := False;
  SQLite3Connection_RefereeDB.Connected := False;
end;     // FormClose




Title: Re: Database won't update
Post by: rvk on April 01, 2015, 12:08:23 pm

From the other topic:

Quote from: madref on March 31, 2015, 08:33:38 pm

Quote

SQLTransaction1.CommitRetaining;

Don't need this :)

So you do need the commitRetaining-line  :)

Code: [Select]

SQLTransaction1.CommitRetaining;

When you don't commit a transaction it will be rolled back automatically at the end of your program. So you do need to manually issue a commit (or set the transaction to autocommit).

Title: Re: Database won't update
Post by: DonAlfredo on April 01, 2015, 12:24:55 pm

Please forgive me to add a sidenote to this discussion, but have you seen the mORMot-framework ?

The author and I have worked very hard to make mORMot fully FPC compatible.
It is now in use on Windows, Linux (i386 and ARM), OSX and Android.

It will abstract all your storage work. No low level SQL needed.
Datasets will easy your GUI.
RTTI used to ease implementation.
Switch database backend in one line of code.
Rest server/access in one line of code.

In your case (look at samples 1 and 2). Just define:

Code: [Select]

TSQLReferee=class(TSQLRecord)
protected
  fVoornaam:RawUTF8
  ......
published
  Voornaam:RawUTF8 read fVoornaam write fVoornaam;
  ......
end

and off you go !
Just my 2 cents.

http://synopse.info/fossil/wiki?name=SQLite3+Framework (http://synopse.info/fossil/wiki?name=SQLite3+Framework)

Title: Re: Database won't update
Post by: madref on April 01, 2015, 01:16:40 pm

Yep it seems i do ;)

And now it works!!