Lazarus

Programming => Databases => Topic started by: BigChimp on December 17, 2012, 11:52:52 am

Title: Master/detail tutorial: additions/corrections welcome. Zeos?!?
Post by: BigChimp on December 17, 2012, 11:52:52 am

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 (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

Title: Re: Master/detail tutorial: additions/corrections welcome. Zeos?!?
Post by: goodname on December 17, 2012, 02:59:56 pm

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('CUST_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.

Title: Re: Master/detail tutorial: additions/corrections welcome. Zeos?!?
Post by: BigChimp on December 17, 2012, 03:22:30 pm

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

Title: Re: Master/detail tutorial: additions/corrections welcome. Zeos?!?
Post by: EgonHugeist on January 05, 2013, 12:34:46 pm

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_doUpdateMasterFirst;
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