Subject Re: [IBO] master/detail help
Author Helen Borrie (TeamIBO)
At 01:58 AM 25-07-02 +0000, you wrote:
>Hi there,
>I am trying to import data into 2 tables (master/detail).
>I'm using TIBCursor's for this.
>I have a generator on the master table, and I'm trying to link them
>up so that when I insert a record into the detail, the relevant link
>is made, but I can't get this to work.
>I have created a source to the master, and ref this through the
>details Mastersource property, then I set up the details MasterKeys
>to reflect the relevant field.
>When I do a prepare on the detail query I get an error about a WHERE
>My actual query is an INSERT query, and I have no where clause in
>the query, so I'm assuming this has to do with the master link

Master-detail linking links two datasets. An Insert query is not a
dataset. MasterLinking (amongst other things) tries to find matching
records in the detail set by passing the masterlinking key value to its SQL
as a WHERE clause. It expects a dataset (a SELECT statement), not a DML
instruction (like INSERT, UPDATE or DELETE).

Apart from that, if you are inserting into two linked tables in one
transaction, the value of the generated key won't be known, for use as a
foreign key value for the detail rows, unless you explicitly arrange to get
hold of it before the detail inserts begin. Look at the Gen_ID() function
(of the dataset or connection components, either will do). With this you
can get the gen value and feed it into both the master and the detail
inserts. Make sure that, if you have a trigger on the master table to use
the generator to populate the PK, that you ensure it will only fire when
the insert SQL doesn't supply the PK value.

if (new.PK is null) then
new.PK = Gen_ID(MyGen, 1);

Your description is very confused. You said you are importing
records. How come you are trying to do this through a master-detail
structure anyhow?

>Can someone explain whats happening here? Or how to setup a
>master/detail in code?

It's just the same as doing it visually in the IDE, except that you assign
values to the essential properties using Pascal statements instead of
having Delphi stream it to a DFM file.

First, I think you (or we) need a clearer description of what you want this
thing to do. When you bring this back to the list, please provide the SQL
statements you are supplying to your components.

As a further comment on all this confusion, you wouldn't normally
use TIB_Cursors to hold master-detail sets if you are using MasterSource
and MasterLinks, since these don't return scrollable datasets...

Grab the TI sheet on Master/Detail from the TechInfo page.

>NB, these queries are in source, not using the components.

I think what you mean to say is that you are creating the components in
run-time, rather than in design-time. Is there any special reason why? It
seems like a very awkward way to approach things, especially when you are
just starting out.

Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at