Editing/adding new rows to DETAIL
lazarus / 16-Databases

----------------------------------------------------------------------
paulkp · 2014-03-03 14:06

I've got master/detail working (SQLite) nicely with 2x DBGrids.
I can add records to the MASTER using DBGrid1 AND run some standard
commit code:

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Edit;
  SQLQuery1.Upda]"]>Blockedde:=UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;
end;                   

BUT I can't add records to the DETAIL using DBGrid2 AND similar code .
.
For a start there is no SQLTransaction2.
And of course we also need to tell the update that the master primary
field value in the detail = what it currently is set to in DBGrid1
(since we're not displaying that field in DBGrid2) . .

Not sure of the best way to do this and it struck me this must be
fairly common.

----------------------------------------------------------------------
BigChimp · 2014-03-03 16:28

Have you seen
http://wiki.lazarus.freepascal.org/MasterDetail
?

----------------------------------------------------------------------
paulkp · 2014-03-03 21:29

Thanks, that helped but . .

As suggested, I added this code to add the MASTER foreign key to the
DETAIL record (the rest of the change is added by DBGrid):

Code: [Select]procedure TForm1.SQLQuery2AfterInsert(DataSet:
TDataSet);
begin
  DataSet.FieldByName('storyID').AsInteger :=
SQLQuery1.FieldByName('storyID').AsInteger;
end;       

but I'm still trying to get the DETAIL update code working (by analogy
to the MASTER update code):

Code: [Select]procedure TForm1.Button3Click(Sender: TObject);
begin
  SQLQuery2.Edit;
  SQLQuery2.Upda]"]>Blockedde:=UpWhereChanged;
  SQLQuery2.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery2.Close;
  SQLQuery2.Open;
end;                
without luck so far . .

----------------------------------------------------------------------
paulkp · 2014-03-03 21:41

Quote from: BigChimp on March 03, 2014, 05:28:25 pmHave you seen
http://wiki.lazarus.freepascal.org/MasterDetail
?

Also, strangely, that example does not discuss any 'commit' code . .
(I have custom commit buttons running committ code). That example
seems to suggest it is unecessary but I have navigator components and
they do not commit correctly.

----------------------------------------------------------------------
paulkp · 2014-03-04 02:52

Actually although that code crashes it DOES WORK!
When the app is restarted it is evident that it has added the detail
record (entered in DBGrid2) WITH the correct foreign key!

In crashing it says:

SQLQuery1 (the master query): Field not found: 'storyID'
(and storyID is the foreign key).

Of course that field is in both tables . .

What's going on?

----------------------------------------------------------------------
paulkp · 2014-03-04 03:23

Folks, I solved it!

The DETAIL commit code needs to be:

Code: [Select]procedure TForm1.Button3Click(Sender: TObject);
begin
  SQLQuery2.Edit;
  SQLQuery2.Upda]"]>Blockedde:=UpWhereChanged;
  SQLQuery2.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;
  SQLQuery2.Close;
  SQLQuery2.Open;
end;
The point is we need to close and re-open SQLQuery1 (Master) AS WELL
as the Detail query I was already closing and re-opening.

As far as I can tell THESE commit code-blocks (detail above, master in
my first post) is what needs to be added to have a TRUE MINIMAL
master-detail that is ACTUALLY useable (ie you can add records via
DBGrids to EITHER master or detail without typing in any keys).

If anyone disagrees, let me know. I reckon it would be nice if the
grids and non-visible DB components allowed this behaviour with no
coding IMO through appropriate settings. And why is this full example
nowhere on the web for Delphi or Lazarus? Have I missed something? Why
no discussion of commit code (except in one case for the master, and
nowhere for the detail)?

Isn't this the bread-and-butter of RDBs? It may be obvious to experts
but certainly not to a newbie and even us newbies want to start with a
working master-detail.

----------------------------------------------------------------------
taazz · 2014-03-04 03:41

that's probably because both sqlQuery1 and SQLquery2 use the same
transaction. Try to add a second SQLtransanction and link with
sqlQuery2 and see if that works with out reopening the sqlquery1.
If my serves me right using SQLtransaction1.commit will automatically
close all the linked queries as well so a simple sqlQuery1.Open should
do the trick that is why the commitretaining was introduced.
As for the bread and butter of RDBs is the Relationship enforcement
and it does its job wonderfully.

----------------------------------------------------------------------
paulkp · 2014-03-04 04:29

Thanks, I'll try that.

I just presumed it had to be the same SQLTransaction because that's
how the sample code worked.

I agree with you re bread-and-butter of RDBs, but surely master-detail
is the most common example of a relationship . .

Why doesn't the http://wiki.lazarus.freepascal.org/MasterDetail demo
(as good as it is, thanks!) cover commit code? It made me think I was
doing something wrong . .

----------------------------------------------------------------------
taazz · 2014-03-04 04:44

Quote from: paulkp on March 04, 2014, 05:29:18 amI agree with you re
bread-and-butter of RDBs, but surely master-detail is the most common
example of a relationship . . I said "relationship enforcement" not
relationship, I can have relationships using xml files and try to
enforce them in my client application its not the same though it still
depends on code on the client to do the job and can be easily avoided
by using something else to edit the xml files.

Quote from: paulkp on March 04, 2014, 05:29:18 amWhy doesn't the
http://wiki.lazarus.freepascal.org/MasterDetail demo (as good as it
is, thanks!) cover commit code? It made me think I was doing something
wrong . .
There to many variables to cover in a single article, for example
there are at least 2 different methods of master detail editing. 1)
insert a master record commit and then insert the details (eg,
supplier or customer and its phone numbers) 2) insert the master and
details in one go/transaction  (eg receipt and its products) where the
master can not exist with out any details. As you can see the commit
code depends on the problem.

----------------------------------------------------------------------
paulkp · 2014-03-04 04:48

Fair enough.

For what it's worth, I think my simple demo represents the minimal 2x
DBGrid-based master-detail that let's you add any record you want via
the grids without having to manually enter keys.

----------------------------------------------------------------------
BigChimp · 2014-03-04 08:06

PaulKP, I agree that having a full worked demo is important.

I'd be happy if somebody would extend the master/detail wiki page to
include using dbnavigator etc.... actually it may then fit into the
SQLdb tutorial0...4 (see e.g.
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2).

Thanks,
BigChimp