Recent

Author Topic: Master/detail tutorial: additions/corrections welcome. Zeos?!?  (Read 6756 times)

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Master/detail tutorial: additions/corrections welcome. Zeos?!?
« 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

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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Master/detail tutorial: additions/corrections welcome. Zeos?!?
« Reply #1 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.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Master/detail tutorial: additions/corrections welcome. Zeos?!?
« Reply #2 on: December 17, 2012, 03:22:30 pm »
@goodname - good: please update the page as you see fit ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

EgonHugeist

  • Jr. Member
  • **
  • Posts: 71
Re: Master/detail tutorial: additions/corrections welcome. Zeos?!?
« Reply #3 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


 

TinyPortal © 2005-2018