Newb problem with Insert into a SQLite database
lazarus / 16-Databases

----------------------------------------------------------------------
Bart · 2013-05-30 17:52

Hi,

I am novice to database programming.
Therefore I was delighted to see that my favorite PC magazine started
a course "Programming in Lazarus" and choose an example with a SQLite
database example.

The program connects to the database without problem and shows me the
(one and only) record in the table called "Adressen" in several
TDBEdit's and a TDBGrid.

Problems is that the code to insert a new record does not seem to
work.

Code: [Select]procedure TfrmAdressen.AddBtnClick(Sender: TObject);
var
  SQL: String;
begin
  SQL := 'INSERT INTO Adressen (Naam) VALUES (' + QuotedStr('') + ')';
  DataModule1.sqlAdressen.Active := False;
  DataModule1.sqlAdressen.SQL.Text := SQL;
  DataModule1.sqlAdressen.ExecSQL;

  SQL := 'SELECT * FROM Adressen';
  DataModule1.sqlAdressen.SQL.Text := SQL;
  DataModule1.sqlAdressen.Active := True;
  DataModule1.sqlAdressen.Last;
  AdresGrid.Refresh;
end;

When this code is executed a new row is added to the DBGrid and all
DBEdits turn "blank" (as expected). When I type "foo" in the DBEdit
that is connected to the field "Naam" then the DBGid gets updated
(when I leave the DBEdit).
When I then execute that code again, all that I typed before gets
lost: in the DBGrid the row that held "foo" now turns blank, and if I
click on the DBGrid where "foo" used to be, all DBEdits are blank
again.

When I close the application, the database is unaltered.
Only the first record (which I created in SQLite Personal) remains
there.

Could anyone please look at my simple (and small) program to see where
it goes all wrong?

I attach the sample program and the db.
(You need the SQLite3.dll to build and run the program)
You must click on the button "Open Adressen"in the programs main form
to open the form that is supposed to edit the database.
In that form click on the SpeedButton with caption "+" to invoke the
code that tries to add a record.

Bart

----------------------------------------------------------------------
JanRoza · 2013-05-30 21:08

Bart,

This is caused because the frmAdressen form is closed without
committing the data changes to the adressen table. Add a formclose
procedure to your frmAdressen form and give it the following content:

Code: [Select]procedure TfrmAdressen.FormClose(Sender: TObject; var
CloseAction: TCloseAction);
begin
  // Maak wijzigingen definitief in de database
  DataModule1.sqlAdressen.ApplyUpdates;
  DataModule1.DbConnection.Transaction.CommitRetaining;
end; 
Now the data is always committed to the SQLite database the moment the
form is closed.

----------------------------------------------------------------------
Bart · 2013-05-31 06:43

I will try that.

However, this does not explain the behaviour I describe whilst adding
records.
As soon as I add a new one, it seems to "forget" about the previous
added records.

Bart

----------------------------------------------------------------------
BigChimp · 2013-05-31 07:51

The code doesn't seem to have any transaction handling; try this:
Code: [Select]procedure TfrmAdressen.AddBtnClick(Sender: TObject);
var
  SQL: String;
begin
  // yegh, quoted string. Why not parameterized queries? See
  //sqldb tutorial 3
  // also, it helps to actually provide a name:
  //SQL := 'INSERT INTO Adressen (Naam) VALUES (' + QuotedStr('') +
')';
  SQL := 'INSERT INTO Adressen (Naam) VALUES (' +
QuotedStr(NameEdit.Text) + ')';
  DataModule1.sqlAdressen.Active := False;
  DataModule1.sqlAdressen.SQL.Text := SQL;
  DataModule1.sqlAdressen.ExecSQL;
  //add next line to get the database to store the records
  DataModule1.DbConnection.Transaction.CommitRetaining;

  SQL := 'SELECT * FROM Adressen';
  DataModule1.sqlAdressen.SQL.Text := SQL;
  DataModule1.sqlAdressen.Active := True;
  DataModule1.sqlAdressen.Last; //why do they go to last??? is that
necessary? maybe to get recordcount?
  DataModule1.sqlAdressen.First; //BigChimp: stubborn me likes first
;) Don't know if needed
  AdresGrid.Refresh;
... don't know if it helps, not much of a GUI guy anyway.

The delete button is still broken - probably needs a similar fix

----------------------------------------------------------------------
JanRoza · 2013-05-31 10:18

I solved it by giving every DBEdit an OnExit procedure with the
following two lines: 

Code: [Select] 
DataModule1.sqlAdressen.ApplyUpdates;
DataModule1.DbConnection.Transaction.CommitRetaining;

This way the DBGrid gets updated as well directly after entering any
data in a new record.

The line Code: [Select]DataModule1.sqlAdressen.Last; is put in to make
sure the grid is positioned on the newly added record. The grid is
unsorted and otherwise after reactivating the sqlAdressen table the
grid would be positioned on the first record.