Subject Re: [IBO] Trouble with inserting row in TIBOTable when used as a detail dataset
Author Helen Borrie
At 01:16 AM 30-10-02 +0000, you wrote:
>I have just completed the basic migration of some bigger project to
>Now I am running into some serious compatibility trouble concerning
>such a basic master-detail-functionality of TIBOTable that I fear I
>am simply missing some important point.
>Just look at this trivial standard-constellation:
>1. Table dtMaster with primary key "INTROWID"
>2. Table dtDetail with primary key "INTROWID" and foreign
>key "MASTER" referencing dtMaster.INTROWID
>(MasterSource = dsMaster;
>Now in BDE-world, inserting a row in dtDetail would automatically
>assign the value of dtMaster.INTROWID to the field dtDetail.MASTER.
>But with TIBOTable, the value of dtMaster.INTROWID is assigned
>to the field dtDetail.INTROWID instead - dtDetail.MASTER is left
>unassigned .

This is only true for ISAM databases (Paradox, Access, etc.) so I'm
guessing that you didn't test it with IB. It doesn't apply to InterBase,
for which parameterised queries are used (yes, even on TTables) to
implement master-detail relationships.

The "default" way for the BDE to determine a detail-to-master relationship
where the detail is a query (which is always the case with a client/server
database) is to match fieldnames. That is, it will form the relationship
on any pair of field names that match by both name and data type.

You cannot rely on IndexFieldNames to enforce the relationship, since
Interbase forms its own index with a name such as RDB$FOREIGN99 on the
column or columns that form the referring structure. Even if you have a
user-defined index on MASTER, InterBase can't be relied on to associate it
with the foreign key relationship. (You should *NOT* place an index on a
foreign key that duplicates this automatically created index, btw...)

Although you say that your example is "trivial", you have in fact made it
non-trivial by confusing the master-detail relationship by having both
INTROWID and MASTER in the detail table, with Detail.INTROWID not being the
foreign key column.

To fix, nil out the IndexFieldNames property and either:

Change the Masterfields property of the Detail Table to
MASTER (alternatively trying the syntax
DetailTable.MASTER=MasterTable.INTROWID, substituting in the real database
table names, of course)


Take charge of the detail query yourself by placing it into a TIBOQuery and
providing a parameterised SQL statement, e.g.

MyQuery.SQL.Add ('select <fieldlist> from DetailTable');
MyQuery.SQL.Add('WHERE MASTER = :master')

In the second case..
-- a <fieldlist> is preferred, although you can use SELECT *
-- there is no MasterSource property - use the Datasource property instead
-- set the query's ParamCheck property to True in order to have the master
table's primary key feed through to the detail query's :master parameter.

I'd recommend going the second way, since you are soon going to discover
how detrimental a Table component is in client/server work.