Master/detail tutorial: additions/corrections welcome. Zeos?!?
lazarus / 16-Databases

----------------------------------------------------------------------
BigChimp · 2012-12-17 10:52

Hi guys,

With the help from Michael Van Canneyt on the mailing list I just
wrote an article/short tutorial on how to set up master/detail
relations in Lazarus/FPC sqldb:
http://wiki.freepascal.org/MasterDetail

As usual, please correct/add if necessary.

Having a separate section with instructions on how to do this in Zeos
- or a link to their documentation - may be nice!

Thanks,
BigChimp

----------------------------------------------------------------------
goodname · 2012-12-17 13:59

Master Detail queries are very useful. Just a couple of small things.

First I suggest using DataSet instead of the query name in the after
insert event. This removes a complier suggestion about using the
dataset argument.
Code: [Select]qrySales.FieldByName('CUST_NO').AsInteger:=qryCustomers.
FieldByName('CUST_NO').AsInteger;
DataSet.FieldByName('CUST_NO').AsInteger:=qryCustomers.FieldByName('CU
ST_NO').AsInteger;

The other is that if no record is selected in the master query then
the detail query will usually return nothing as the detail where
clause uses SALES.CUST_NO:=null. If you want the detail to show all
results when the master has nothing selected use the filter/filtered
properties. Filter/filtered is likely a separate tutorial. Have used
filter in place of Master/Detail with DBLookupComboBoxes where a
detail box is filtered based on selection of a master box.

----------------------------------------------------------------------
BigChimp · 2012-12-17 14:22

@goodname - good: please update the page as you see fit

----------------------------------------------------------------------
EgonHugeist · 2013-01-05 11:34

Hi BigChimp,

i've seen your topic so i wanna support you a little bit. Like you
know i'm not the best in english but code-lines are international. The
attached code is from our Zeos-test-suites for Master-Detail
relations. I've attached this one because it shows an additional
option Zeos have: Update the Master-Table before the Datail-Table is
updated. This helps if you've some constraints between the Master and
Detail table. The additional oprtion you can find in the
TZDataSets(TZTable, TZReadOnlyQuery, TZQuery).Options =
[doUpdateMasterFirst]:

Code: [Select]{**
  Runs a test for in extendet clientdatset rules
  All detail-queries should be updated in a single transaction.
  But now the MasterTable should be updated first for an valid
ForegnKey.
  Then all DetailTables should have been updated.
  Very tricky and has to deal with MetaData informations.
}
procedure TZTestMasterDetailCase.TestClientDatasetWithForeignKey_doUpd
ateMasterFirst;
var
  SQLMonitor: TZSQLMonitor;
  CommitCount, I: Integer;

begin
  if SkipTest then Exit;

  SQLMonitor := TZSQLMonitor.Create(nil);
  SQLMonitor.Active := True;
  MasterQuery.SQL.Text := 'SELECT * FROM department ORDER BY dep_id';
  MasterQuery.Options := MasterQuery.Options + [doDontSortOnPost];
  MasterQuery.Open;

  CheckStringFieldType(MasterQuery.FieldByName('dep_name').DataType,
Connection.DbcConnection.GetConSettings);
  CheckStringFieldType(MasterQuery.FieldByName('dep_shname').DataType,
Connection.DbcConnection.GetConSettings);
 
CheckStringFieldType(MasterQuery.FieldByName('dep_address').DataType,
Connection.DbcConnection.GetConSettings);

  DetailQuery.SQL.Text := 'SELECT * FROM people';
  DetailQuery.MasterSource := MasterDataSource;
  DetailQuery.MasterFields := 'dep_id';
  DetailQuery.LinkedFields := 'p_dep_id';
  DetailQuery.Options := DetailQuery.Options + [doUpdateMasterFirst,
doDontSortOnPost];
  DetailQuery.Open;
  CommitCount := 0;
  try
    MasterQuery.Append;
    MasterQuery.FieldByName('dep_id').AsInteger := TestRowID;
    MasterQuery.FieldByName('dep_name').AsString :=
GetDBTestString('öäüüäö', Connection.DbcConnection.GetConSettings);
    MasterQuery.FieldByName('dep_shname').AsString := 'abc';
    MasterQuery.FieldByName('dep_address').AsString :=
GetDBTestString('A adress of öäüüäö',
Connection.DbcConnection.GetConSettings);

    CheckEquals(True, (MasterQuery.State = dsInsert), 'MasterQuery
Insert-State');

    DetailQuery.Append;
    DetailQuery.FieldByName('p_id').AsInteger := TestRowID;
    DetailQuery.FieldByName('p_dep_id').AsInteger := TestRowID;

    DetailQuery.FieldByName('p_begin_work').AsDateTime := now;
    DetailQuery.FieldByName('p_end_work').AsDateTime := now;
    DetailQuery.FieldByName('p_picture').AsString := '';
    DetailQuery.FieldByName('p_resume').AsString := '';
    DetailQuery.FieldByName('p_redundant').AsInteger := 5;
    CheckEquals(True, (DetailQuery.State = dsInsert), 'MasterQuery
Insert-State');

    MasterQuery.Post;

    CheckEquals(True, (MasterQuery.State = dsBrowse), 'MasterQuery
Browse-State');
    CheckEquals(True, (DetailQuery.State = dsBrowse), 'DetailQuery
Browse-State');

    for i := 0 to SQLMonitor.TraceCount -1 do
      if SQLMonitor.TraceList[i].Category = lcTransaction then
        if Pos('COMMIT', UpperCase(SQLMonitor.TraceList[i].Message)) >
0 then
          Inc(CommitCount);
    //fix it CheckEquals(1, CommitCount, 'CommitCount');
  finally
    MasterQuery.SQL.Text := 'delete from people where p_id =
'+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    MasterQuery.SQL.Text := 'delete from department where dep_id =
'+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    SQLMonitor.Free;
  end;
end;

Also does this test show you the correct way to link the Mast and
Detail relations between the components.

I hope it helps you a little bit.

Cheers, Michael