Subject Re: [IBO] Master-Detail dillemas with TIBO*
Author Helen Borrie
At 01:00 AM 10/03/2005 +0000, you wrote:

>Thanks a lot for the very educative reply (as usual!)
> > >So if I need to use a JOIN in the
> > >future, then I should use TIBOQuery and lose the Master-Detail
> > features.
> >
> > Why? TIBOQuery inherits the TQuery implementation of master-detail.
> > Thus, like TQuery, it doesn't expose explicit MasterFields linking.
> > This means that the linking columns in the two output sets must be
> > of exactly the same type and have the same field names in the
> > database; and the Details set must be designed so that the
> > column[s] that link[s] to the Deal set's KeyLinks (for the
> > masterlinking) are parameters.
>I'm a bit lost here. Can you point me to an example, or a place in the
>docs that explains this?
> > Or perhaps you were unaware that you link your Details set to your
> > Deal set through the Datasource property of the Details set?
>I'll read IBO help again, but I'll appreciate a simple example.

UMMMMM - it's not in the IBO help, since it is behaviour that is inherited
from Delphi's TQuery. That's the help you should look at -- albeit
Borland's helpfile on this issue is minimal. However, you should get more
useful stuff from the actual Delphi manual (book or PDF, whatever you got
with your Delphi edition). If you don't have these manuals then you're
just gonna have to *believe* it when I tell you that the detail's
Datasource property provides the set-to-set datalinking for a
TQuery/TIBOQuery detail/master relationship.

The native IBO TIB_Query exposes MasterLinks *and* MasterParamLinks, so it
is not crippled by the "same-name" restriction of the TDataset family.

> > >2. Should I use cached updates? Before posting a deal to the DB I
> > must check that the data is valid, eg. that the balance of all
> >
> > That sounds like an occasion when cached updates could be
> > used.....However, since all posting is done at the level of the
> > current row, you can always intervene in the BeforePost event of
> > either (or both) dataset[s] to do this kind validation. The key
> > thing here, where you have inter-row dependencies, is NOT to use
> > an AutoCommit transaction.
>I understand that I need to take control of the transaction, so that I
>only commit once all the data is coherent. I don't understand how a
>BeforePost can help me do validation that needs to check rows that the
>user hasn't input yet.

Nor I. FWIW, I haven't a clue what you're referring to here.

Do you actually understand that Post writes to the database? Fb/IB -
unlike Paradox, Access, etc., has what's known as "multi-generational
architecture". That means your new row versions have two lives in the

1) when you post, you new row gets written to the database (if it's allowed
to) but the database state doesn't change. If your Post succeeds, then you
have a lock on the version of the row that *other* transactions can see.

2) when you successfully commit the transaction that contains your new,
updated or deleted row, the database state changes and your version becomes
the version that all transactions will see from then on.


if you roll back the transaction that contains your changes, a) your
inserts and deletes will simply disappear and b) your posted record
versions become garbage which, all being equal, will get removed next time
a sweep or GC operation is done.

> > IMO, relying on client code to do this is not a good design
> > practice, since it totally binds the integrity of your data to the
> > application....and, if you have another application (maybe even in
> > another language, which can't share your classes) you have to
> > repeat the same validation.
>I understand and even anticipated this shortcoming. But I don't really
>have an alternative. I can't do all this server-side, becuause I need
>to do validation and data fixing that depends on application parameters.

Isn't that like saying you can't start a bee farm because you don't know
how to make the bees go into the hive?

>I don't want to waste too much of your time with detailed
>explanations, but just some examples: I need to calculate a special
>date field that depends on user input and a non-trivial logic, and
>store the result in the detail row. Then I have to verify that all
>those calculated dates in all detail rows fall within a certain range.
>I need to calculate (and store) a tax amount field which depends on
>many application variables and uses a very twisted logic (tax laws...)

Well, the complexity of the logic doesn't stop it being calculated and
validated on the server side...where did you get the idea that it did?

But - whatever - if you are taking inputs from the user and doing stuff to
them, in order to calculate a value that is to be stored in the database,
then take the inputs via non-db-aware fields, do the calculation and pass
the result to the Insert or Update method via FieldByName or
ParamByName. Repeat-repeat-repeat, you cannot bind a client-side
calculated field directly to a database field.

> > >4. When I try to input a new detail row I get an error box that
> > >compains "missing value for MASTER_ID". I guess this is because
> >
> > You don't have the linking set up.
> > ...
> > The hidden implicit linking will take care of writing the
> > master_id to the detail set when you call Insert on the detail set.
>Ok, I think I'm starting to figure it out.
> > >5. There are additional "missing value" messages for other fields.
> > >Those are calculated fields, which can be calculated only after the
> > >user inputs all data (ie. user click OK). How can I tell
> > >TIBODataset to "be patient" with those fields until I actually
> > >try to post?
> >
> > This needs to be explained more clearly. A calculated field on the
> > client side doesn't exist in the database. It sounds as though you
> > need some help constructing the detail query so you can get your
> > head around the binding between the output set and the underlying
> > table.
>Those calculated fields that I mention are all "real" fields, ie.
>columns in the table.

They can't be BOTH real fields AND calculated fields. "That does not compute."

>As I explained above, I need to calculate them
>client-side becuase the calculation isn't trivial. So during user
>input those fields/columns don't have any values, because I fill them
>only AFTER the user signals that input is done. However the user can't
>input anything because the dataset complains on the missing values.

Calculated fields (AND COMPUTED BY columns output from the database AND
derived columns output from the database) are read-only. Period. Even
though the GUI control allows the user type things into the field in the
control, there is no way to bind these GUI fields directly to any
underlying dataset column or parameter - hence the errors. Your code must
calculate the value it wants in these dataset fields/parameters and use a
method to assign the values.

>I may have found a solution. It seems that the dataset sets the
>"Required" property for the TFields that correspond to each of those
>columns. So I need to set "Required=FALSE" to prevent the dataset from
> validating until posting time, at which point I'll take care of
>stuffing the fields with the proper values.

That's one way to get past a particular dataset exception. But it still
won't allow you to bind the TField to the underlying database column.

> > The methods that have to check the whole set will be pretty ugly,
> > because they will have to iterate through the rows of the detail
> > set....maintain a structure (TStrings usually) to hold the linkages
> > between the detail set's Keylinks and the sequence number.
>Can't I just iterate the rows in the dataset? Once the user is done
>editing and before posting all the data, I can do eg.:
>WHILE Ok AND (NOT DetailQuery.EOF) DO
> Ok:=VerifyDetailRow;
> CalcDetailFields;
> TotalCheck:=TotalCheck+DetailQuery['amount'];
> { Etc. }
> DetailQuery.Next;
> END;
>IF VerifyOk THEN
> ApplyUpdates
> MessageDlg('Houston we have a problem');
> > If the logic permits, call the set-level methods after the
> > row-level validations are done.
>I think I can do them both at the same time as shown in the example
>above, ie. for each row do the validation and update the calculated
>fields, and keep counters for set-level data.
>At the end of the iteration I'll test the set-level counters, and
>either proceed to post or warn the user to fix the input.

Whatever you need, do.

> > >7. I want to let the user move detail rows relative to each other.
> >
> > Then you certainly want to cache the detail set. I think you will
> > have to search for a third-party grid or tree control to get the
> > UI you want, since the VCL's TDBGrid doesn't have the capability
> > you want, AFAIR.
>I'm not concerned about the UI part. I can add up/down arrow buttons
>to move the current row, etc. Let's suppose I have the GUI in place -
> how can I move a row in a (cached) dataset? I can't find any method
>to change the order of rows, or to move a row from one "place" in the
>dataset to another.
>Helen - thanks again for the great help. I hope you still have some
>more patience for me, as I now know that there's more I should know...

I do stress the importance of understanding the OO nature of Delphi. The
TDataset-compatible data access components inherit from the VCL ancestor
everything that is specifically not overridden or hidden by the TIBO*
class. Additionally, because the TIBO* components are wrapped by a
TIB_Query, there are "new" properties and methods available to them, that
are not present in the ancestor classes. Things like KeyLinks,
KeyRelation, et al., for which you can study the help for the corresponding
native TIB_* class.

The IBO help is built using a totally Delphi-aware help-builder
(Time2Help). Whenever you see a published member (property, method or
event) of a TDataset component that has no help text in the IBO help, you
will know that the member is inherited from TDataset. You can help
yourself to find where in the VCL help to look, by inspecting the
"Hierarchy" (a green speedbutton in the help toolbar of the component's
main page).

What this all means is that, if you're using the TDataset-compatible
components, you need to get your head around how the ancestors work.