Master/Detail Relationship and inserting new records
lazarus / 16-Databases

----------------------------------------------------------------------
talorigomat · 2014-10-13 22:27

I'm using firebird 2.5.2 and Lazarus 1.2.4.  I have a form that has
two datasets in a master detail relationship.  The guidance dataset is
the master and the assignment dataset is the detail.  The object is to
have the user add a new guidance and the departments it is assigned to
and then click a button to save everything.  When the save button is
clicked I get a new id for the guidance, update the id field in the
guidance dataset and then attempt to update the guidance id in the
assignment dataset (along with generating an assignment id for each
department the guidance is assigned to).  The problem is that as soon
as the guidance id is set in the guidance dataset then all the entries
in the assignment dataset disappears.  What is the best way to
accomplish what I'm trying to do?  Below is the code I'm using. 

Code: [Select]procedure TfrmGuidance.SaveChanges;
begin
  try
    if trnGuidance.Active then
    begin
      if qryGuidance.State = dsInsert then
      // If a new guidance inserted
      begin
        qryNewId.SQL.Add(SetupGenerator('Gdl'));
        qryNewId.Open;   // gets an id for the guidance from the
firebird generator
        qryGuidance.FieldByName('GdlId').Value :=
qryNewId.FieldByName('GEN_ID').AsLargeInt;
        qryNewId.Close;
      end;

      // Make changes to Assignments
      qryNewId.SQL.Add(SetupGenerator('Asgn'));
      qryAssign.First;
      while not qryAssign.EOF do
      begin
        if qryAssign.FieldByName('Assid').Value = 0 then
        begin
          qryAssign.Edit;
          // *** get new assignment ID
          qryAssign.FieldByName('GdlId').Value:=
qryGuidance.FieldByName('GdlId').Value;
          qryNewId.Open;
          qryAssign.FieldByName('AssId').Value :=
qryNewId.FieldByName('GEN_ID').AsLargeInt;
          qryNewId.Close;
        end;
        qryAssign.Next;
      end;

      if qryguidance.State in [dsEdit, dsInsert] then
      begin
        qryGuidance.ApplyUpdates; //Pass user-generated changes back
to database...
//        trnGuidance.Commit; //... and commit them using the
transaction.
      end;
      qryAssign.Edit;
      qryAssign.ApplyUpdates;
      trnGuidance.Commit;
      qryGuidance.Active := True;
      qryAssign.Active := True;
      trnGuidance.Active := True;
      trnAssign.Active:= True;
    end;
  except
  on E: EDatabaseError do
    begin
      MessageDlg('Error', 'A database error has occurred. Technical
error message: ' +
        E.Message, mtError, [mbOK], 0);
    end;
  end;

end;

----------------------------------------------------------------------
mangakissa · 2014-10-14 08:37

I try to understand what you mean and can't figure out what you want
to do with your code.
What I do with master/detail.
- Get a unique number from the generator of the master dataset
- Insert this with that number
- save the unique number on your form.
- when saving the detail dataset take the number from the master as a
reference
- refresh the detail dataset for getting the generated key number

You don't have to generate a unique number for detail dataset, because
there's no reference to another dataset.

----------------------------------------------------------------------
rvk · 2014-10-14 09:00

Quote from: mangakissa on October 14, 2014, 10:37:34 amYou don't have
to generate a unique number for detail dataset, because there's no
reference to another dataset.
Uuuh? It's always handy to have a unique identifier in every table.
How are you going to edit that detail table if it doesn't have a
unique number? You could use the other fields in your update-statement
but that could get you in trouble if you inserted a detail record with
the same values as another. So, always create a unique number for your
records, regardless if it's a detail dataset.

Quote from: talorigomat on October 14, 2014, 12:27:57 amThe object is
to have the user add a new guidance and the departments it is assigned
to and then click a button to save everything.  When the save button
is clicked I get a new id for the guidance, update the id field in the
guidance dataset and then attempt to update the guidance id in the
assignment dataset (along with generating an assignment id for each
department the guidance is assigned to).
Am I missing a qryGuidance.Post?
Shouldn't this be done before qryGuidance.ApplyUpdates?

Second in your loop through qryAssign you do an qryAssign.Edit and
change the values but don't do a qryAssign.Post before going to the
next record.

So try to do a .Post after you changed the records (for both
qryGuidance and qryAssign) otherwise the fields are not really changed
in the database.

----------------------------------------------------------------------
mangakissa · 2014-10-15 07:48

QuoteUuuh? It's always handy to have a unique identifier in every
table. How are you going to edit that detail table if it doesn't have
a unique number?QuoteThe unique number will be processed by the
generator internally. That's why a refresh is needed.

----------------------------------------------------------------------
rvk · 2014-10-15 08:18

Quote from: mangakissa on October 15, 2014, 09:48:08 amThe unique
number will be processed by the generator internally. That's why a
refresh is needed.
(You quoting goes a bit wrong )

What do you mean by "The unique number will be processed by the
generator internally." ??
Please note that a generator does absolutely nothing on its own.
Only if you create a trigger which uses the generator to create a
unique id it will be used internally.
But I didn't read anything in the original post about using a trigger,
so that's a big assumption.

----------------------------------------------------------------------
talorigomat · 2014-10-16 01:41

Quote from: rvk on October 14, 2014, 11:00:37 amAm I missing a
qryGuidance.Post?
Shouldn't this be done before qryGuidance.ApplyUpdates?

Second in your loop through qryAssign you do an qryAssign.Edit and
change the values but don't do a qryAssign.Post before going to the
next record.

So try to do a .Post after you changed the records (for both
qryGuidance and qryAssign) otherwise the fields are not really changed
in the database.

According to the "How to change data in a table?" section of the
SqlDBHowto Wiki (http://wiki.freepascal.org/SqlDBHowto) the .Post only
validates what has been entered and the the .AppylUpdates method
actually writes the data back to the database.

The problem occurs as soon as I change the value in query with the
following statement:
qryGuidance.FieldByName('GdlId').Value :=
qryNewId.FieldByName('GEN_ID').AsLargeInt;

The records in the assignment dataset disappears before I call
.ApplyUpdates (or a .Post).  I presume that as the master-detail
relationship is based on the GdlId columns of both datasets then as
soon as I change the  GdlId value of of the guidance dataset it causes
what has been entered in the assignment dataset to disappear as they
no longer share the same gdlId value

mangakissa, I believe the code is doing what you stated. It works fine
if the user enters a new guidance saves it and then enters one or more
assignment records then save.

----------------------------------------------------------------------
mangakissa · 2014-10-16 07:01

Quote from: rvk on October 15, 2014, 10:18:50 am(You quoting goes a
bit wrong )That's strange.
QuoteWhat do you mean by "The unique number will be processed by the
generator internally." ??
Please note that a generator does absolutely nothing on its own.
Only if you create a trigger which uses the generator to create a
unique id it will be used internally.
But I didn't read anything in the original post about using a trigger,
so that's a big assumption.
I assume there's a trigger created for processing an unique number.
But your right. Talorigomat doesn't talk about it.
As for the detail. If you create several records in it for the same
master_id at once, its better to use a trigger for generating id's and
then refresh the table afterwards.

----------------------------------------------------------------------
rvk · 2014-10-16 08:53

Quote from: talorigomat on October 16, 2014, 03:41:00 amAccording to
the "How to change data in a table?" section of the SqlDBHowto Wiki
(http://wiki.freepascal.org/SqlDBHowto) the .Post only validates what
has been entered and the the .AppylUpdates method actually writes the
data back to the database.I can't find that on the page the page you
mention. It says "Use 'Post' to validate the new data, if the data is
valid then the edit mode is left". But that doesn't mean its the only
thing Post does. You always need to call .Post (or .Cancel) after your
.Edit. Maybe it's different for your database but it's best to follow
the guidelines for all databases. See here:
http://wiki.freepascal.org/Databases#Post_and_Cancel
QuoteCalling the dataset post method saves the values (edit) or record
(insert). In some dataset descendants, they will be written to the
database immediately, while in others they will be stored in a list of
updates until a further call is made to save all changes to the
database. Finally, even when they are written to the database, you may
still have to call a "commit" method to make the database write them
permanently. All of this also varies considerably with the dataset
descendant, so look up the details for the one you are using.(Edit: I
just looked in the source of ApplyUpdates and notice it does a .Post
if the dataset is still in dsEdit/dsInsert mode and the record has
changed. So it does a silent .Post itself. Although it does this, I
still feel it's good practice to do this yourself before doing an
ApplyUpdates. Maybe it's because I'm used to working with Delphi)

Quote from: talorigomat on October 16, 2014, 03:41:00 amThe records in
the assignment dataset disappears before I call .ApplyUpdates (or a
.Post).  I presume that as the master-detail relationship is based on
the GdlId columns of both datasets then as soon as I change the  GdlId
value of of the guidance dataset it causes what has been entered in
the assignment dataset to disappear as they no longer share the same
gdlId value
Your presumption is correct. You have a SQL-statement in your
qryAssign which follows the value given in qryGuidance.GdlId. Once you
change that all your records in qryAssign will be refreshed and your
changes in it are gone.

I don't think you can add one qryGuidance and multiple qryAssign's at
the same time and keep your master-detail grids intact. You can do it
if you disable the coupling of the master and details and loop over
qryAssign to set the GdlId for the records where it is 0. But I don't
think it is a stable solution.

You already noticed that adding a qryGuidence and saving it, and after
that, adding qryAssign's, it works correctly. I think you need to
enforce that in your design. You can set qryAssign.GdlId to
qryGuidence.GdlId directly after the qryAssign.Insert's. Always make
sure only the master-table or the detail-table can be in Edit/Insert
mode. Not both at the same time.

After that there are two ways of assigning GdlId to your qryGuidence.
You can get the value yourself (like you already did but then directly
after qryGuidence.Insert) or let the trigger (if you have one) do it's
work and do a refresh after the .ApplyUpdates (like mangakissa
suggests in his first post). But adding multiple qryAssign's and
leaving its GdlId on null (or 0) and after that traversing all the
null's doesn't seem like a good idea. Best is to keep to one active
qryAssign-record and assign it's GdlId directly after the .Insert. and
doing an ApplyUpdates before you're doing another .Insert.