Board index » delphi » Master-detail with IBQuery

Master-detail with IBQuery

Hi!
I am using IBQuery (3 of them) to accomplish master-detail relationship. I
use DataSource property two connect detail tables to master table. Also, in
SQL I add where clause like:
WHERE
  ID_PARTNER = :ID_PARTNER

ID_PARTNER is PK field in master table and also in detail one.

The display looks ok, but update does not work (I use UpdateSQL, of course).
I would like to know the following: When you insert a new record to a child
table, does master table sends parameter value, or I have to fill that field
by myself (since I don't have DB aware control that is connected to
ID_PARTNER in detail table). I excpected that IBX fills that parameter
automatically. Was I wrong?

--

Mario Blataric
V&M Electronic

 

Re:Master-detail with IBQuery


Quote
Mario Blataric wrote:

> Hi!
> I am using IBQuery (3 of them) to accomplish master-detail relationship. I
> use DataSource property two connect detail tables to master table. Also, in
> SQL I add where clause like:
> WHERE
>   ID_PARTNER = :ID_PARTNER

> ID_PARTNER is PK field in master table and also in detail one.

> The display looks ok, but update does not work (I use UpdateSQL, of course).
> I would like to know the following: When you insert a new record to a child
> table, does master table sends parameter value, or I have to fill that field
> by myself (since I don't have DB aware control that is connected to
> ID_PARTNER in detail table). I excpected that IBX fills that parameter
> automatically. Was I wrong?

You need to fill it yourself.

Quote
> --

> Mario Blataric
> V&M Electronic

--
Jeff Overcash (TeamB)   | Talk about failure
(Please do not email    | To fall is not to fail
 me directly unless     | Failure isn't about falling down
 asked.  Thank You)     | Failure is staying down (Marillion)

Re:Master-detail with IBQuery


Thanks Jeff. Just to mention it, would it be possible to implement that
feature in future versions of IBX?

Re:Master-detail with IBQuery


I am also using this master-detail type and noticed a little problem if
the master record couldn't be found. If you have a detail (in my case
some tables joined together) of which the result set is +100K records if
you omit the where-clause, then querying a master which returns NULL as
link value, causes the detail query to take long before it returns an
empty result set. The reason I think is because of the SQL language. In
de detail query I have something like:

        WHERE OBJECT_ID = :OBJECT_ID

If the master would result in NULL values, the where-clause of the
detail would result in:

        WHERE OBJECT_ID = NULL

which can not be done in SQL and results in the where-clause being
omitted, causing all data to be searched.

So I tought the following was my solution. In the BeforeOpen event of
the detail IBQuery I put the following code:

    if (MyDetailIBQuery.Params[0].IsNull) then
      MyDetailIBQuery.Params[0].AsInteger := -1;

I know that in my database there is no negative key (I only use integers
as keys), so the result is that nothing is found. But (here it comes),
if I query a master record, which does exist and has detail records,
still no detail records are returned.

Debugging this problem revealed that once the parameter is set to -1 in
the event above, it never gets overwritten by the master query. (So it
stays -1, and detail records are never found).

I was able to solve this problem by putting the following code in the
BeforeOpen event:

    if (MyMasterIBQuery.FieldByName('OBJECT_ID').IsNull) then
      MyDetailIBQuery.Params[0].AsInteger := -1
    else
      MyDetailIBQuery.Params[0].AsInteger :=
MyMasterIBQuery.FieldByName('OBJECT_ID').AsInteger;

This does the trick. I was wondering if I overlooked something, causing
my problem. In the case I did not overlook something, maybe this problem
can be addressed in one of the following releases of IBX?

Ivo

Re:Master-detail with IBQuery


Quote
Ivo Lorie wrote:

> If the master would result in NULL values, the where-clause of the
> detail would result in:

>         WHERE OBJECT_ID = NULL

> which can not be done in SQL and results in the where-clause being
> omitted, causing all data to be searched.

        SQL doesn't work this way.  A WHERE clause like the above will return
FALSE, always.  Nothing = NULL, not even NULL itself.  So the above will
return no records.  Try it.

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Craig,

But isn't WHERE OBJECT_ID IS NULL a valid identifying phrase? (IS, not EQUALS)

Nothing equals null, but things can BE null. No?
I've used that many times. Did I just get lucky?

Todd

Quote
Craig Stuntz wrote:
> Ivo Lorie wrote:

> > If the master would result in NULL values, the where-clause of the
> > detail would result in:

> >         WHERE OBJECT_ID = NULL

> > which can not be done in SQL and results in the where-clause being
> > omitted, causing all data to be searched.

>         SQL doesn't work this way.  A WHERE clause like the above will return
> FALSE, always.  Nothing = NULL, not even NULL itself.  So the above will
> return no records.  Try it.

>         -Craig

> --
> Craig Stuntz               Vertex Systems Corporation
> Senior Developer           http://www.vertexsoftware.com

> Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Quote
RayZor{*word*128}wrote:

> Craig,

> But isn't WHERE OBJECT_ID IS NULL a valid identifying phrase? (IS, not EQUALS)

NULL is a state, not a value.  It is the undefined state.  So like Craig said
nothing will = NULL since equality (=) is comparing values.  The IS keyword is
checking the state of the field, not the value of the field.  IS checks if the
field is undefined or defined.

Quote
> Nothing equals null, but things can BE null. No?
> I've used that many times. Did I just get lucky?

> Todd

--
Jeff Overcash (TeamB)   | Talk about failure
(Please do not email    | To fall is not to fail
 me directly unless     | Failure isn't about falling down
 asked.  Thank You)     | Failure is staying down (Marillion)

Re:Master-detail with IBQuery


The issue is not that the child doesn't return rows. It is the fact that
when the master resolves to NULL values, it takes a lot longer for the
detail query to realise that it has no records.

Meanwhile I have done some other tests and the problem exists when
performing a query like:

SELECT *
FROM OBJECTS O
     LEFT OUTER JOIN OBJECT_DETAILS od ON (od.object_id = o.object_id)
WHERE (o.master_object_id = :master_object_id)

MASTER_OBJECT_ID is a foreign key to the same table (OBJECTS) pointing
to OBJECT_ID which is the primary key. The table OBJECT_DETAILS has a
primary key OBJECT_DETAILS_ID and a foreign key OBJECT_ID which also
points to OBJECTS.

If you start this query with master_object_id being NULL, you will find
that it takes a lot longer to return no result then if you would have
entered a non existing value (considering the table OBJECT_DETAILS
contains a lot of rows).

I have done all my tests with EMS QuickDesk 1.7.10.6. Herein you can use
parameters in the SQL Editor, which upon execution will ask you for the
values and give the opportunity to pass NULL (via a checkbox). There I
found another confusing thing; when I changed the parameter
':master_object_id' in ':par_master_object_id', the WHERE-clause was
interpreted as if 'WHERE (o.master_object_id IS NULL)' was put. I will
pas this also on to the makers of QuickDesk.

While I was writing this message, I also had a closer look at my test
program. There I have put a ShowMessage in the BeforeOpen event of the
detail query and found that it is called twice if the master results in
values other then NULL. If the master query returns no rows at all, the
BeforeOpen event of the detail query is called only once. I also must
point out that I am using clientdatasets and providors.

Ivo

Quote
-----Original Message-----
From: Craig Stuntz [mailto:cstuntz@no_spam.vertexsoftware.com]

Posted At: dinsdag 30 januari 2001 15:31
Posted To: interbaseexpress
Conversation: Master-detail with IBQuery
Subject: Re: Master-detail: other question

Ivo Lorie wrote:

> If the master would result in NULL values, the where-clause of the
> detail would result in:

>         WHERE OBJECT_ID = NULL

> which can not be done in SQL and results in the where-clause being
> omitted, causing all data to be searched.

        SQL doesn't work this way.  A WHERE clause like the above will
return
FALSE, always.  Nothing = NULL, not even NULL itself.  So the above will
return no records.  Try it.

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Quote
Ivo Lorie wrote:

> The issue is not that the child doesn't return rows. It is the fact that
> when the master resolves to NULL values, it takes a lot longer for the
> detail query to realise that it has no records.

        What is the table definition, index definitions, and query optimization
PLAN for the query in question?

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Some remarks before you read on:
This query is put in a TIBQuery component of which the datasource
property is filled with a datasource that points to another TIBQuery
component. So the :object_id parameter is filled by the later query.
This master query is known to only return one row.
I have tested this query with QuickDesk and omitting the 'order
by'-clause doesn't change anything.
I have put a line between the different components to make things more
readable.
------------------------------------------------------------------------
----------------------------
The detail query that gives the problem is:

select o.master_object_id,
       o.object_id detail_object_id,
       o.object_kind_id,
       o.object_type_id,
       ot.object_titles_oid,
       ot.object_id title_object_id,
       ot.title_type_id,
       ot.serial_number,
       ot.title,
       oa.object_authors_oid,
       oa.object_id authors_object_id,
       oa.author_id,
       oa.author_type_id,
       a.name
from objects o
     left outer join object_titles ot on (o.object_id = ot.object_id)
     left outer join object_authors oa on ((oa.object_id = o.object_id)
                                           and (oa.author_order = 1))
     left outer join authors a on (a.author_id = oa.author_id)
where (o.master_object_id = :object_id)
order by ot.serial_number

------------------------------------------------------------------------
----------------------------
The plan generated is:

Plan:
PLAN JOIN (JOIN (JOIN (O INDEX (RDB$FOREIGN52),OT INDEX
(RDB$FOREIGN61)),OA INDEX (AUTHOR_ORDER_IDX,RDB$FOREIGN55)),A INDEX
(RDB$PRIMARY3))

Adapted plan:
PLAN JOIN (JOIN (JOIN (O INDEX (RDB$FOREIGN52),OT INDEX
(RDB$FOREIGN61)),OA INDEX (AUTHOR_ORDER_IDX,RDB$FOREIGN55)),A INDEX
(RDB$PRIMARY3))

------------------------------------------------------------------------
----------------------------
The table structure of OBJECTS is:

CREATE TABLE OBJECTS (
    OBJECT_ID           DOM_RECORDID NOT NULL,
    OBJECT_TYPE_ID      DOM_RECORDID NOT NULL,
    OBJECT_KIND_ID      DOM_RECORDID NOT NULL,
    ISN                         DOM_ISN,
    SIGNATURE           DOM_SIGNATURE,
    DESCRIPTION                 DOM_TEXT,
    PUBLISHER_ID                DOM_RECORDID,
    ISSUE_PLACE_ID      DOM_RECORDID,
    MASTER_OBJECT_ID    DOM_RECORDID,
    STRUCTURED_INFO     DOM_TEXT);

/* Primary keys definition */
ALTER TABLE OBJECTS ADD CONSTRAINT PK_OBJECTS PRIMARY KEY (OBJECT_ID);

/* Foreign keys definition */
ALTER TABLE OBJECTS ADD  CONSTRAINT FK_OBJECTS_KIND FOREIGN KEY
(OBJECT_KIND_ID) REFERENCES OBJECT_KIND (OBJECT_KIND_ID) ON UPDATE
CASCADE;
ALTER TABLE OBJECTS ADD  CONSTRAINT FK_OBJECTS_TYPES FOREIGN KEY
(OBJECT_TYPE_ID) REFERENCES OBJECT_TYPES (OBJECT_TYPE_ID) ON UPDATE
CASCADE;
ALTER TABLE OBJECTS ADD  CONSTRAINT FK_OBJECTS_PUBLISHER FOREIGN KEY
(PUBLISHER_ID) REFERENCES PUBLISHERS (PUBLISHER_ID) ON UPDATE CASCADE;
ALTER TABLE OBJECTS ADD  CONSTRAINT FK_OBJECTS_MASTER FOREIGN KEY
(MASTER_OBJECT_ID) REFERENCES OBJECTS (OBJECT_ID);
ALTER TABLE OBJECTS ADD  CONSTRAINT FK_OBJECTS_ISSUE_PLACE FOREIGN KEY
(ISSUE_PLACE_ID) REFERENCES ISSUE_PLACES (ISSUE_PLACE_ID) ON UPDATE
CASCADE;
------------------------------------------------------------------------
----------------------------
The table structure of OBJECT_TITLES is:

CREATE TABLE OBJECT_TITLES (
    OBJECT_TITLES_OID DOM_RECORDID NOT NULL,
    OBJECT_ID DOM_RECORDID NOT NULL,
    TITLE_TYPE_ID DOM_RECORDID NOT NULL,
    SERIAL_NUMBER DOM_SMALLNUMBER NOT NULL,
    TITLE DOM_DESCRIPTION NOT NULL);

/* Primary keys definition */
ALTER TABLE OBJECT_TITLES ADD CONSTRAINT PK_OBJECT_TITLES PRIMARY KEY
(OBJECT_TITLES_OID);

/* Foreign keys definition */
ALTER TABLE OBJECT_TITLES ADD  CONSTRAINT FK_OBJECT_TITLES_OBJECTS
FOREIGN KEY (OBJECT_ID) REFERENCES OBJECTS (OBJECT_ID) ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE OBJECT_TITLES ADD  CONSTRAINT FK_OBJECT_T_REF_244_TITLE_TY
FOREIGN KEY (TITLE_TYPE_ID) REFERENCES TITLE_TYPES (TITLE_TYPE_ID);

/* Indices definition */
CREATE INDEX IDX_OBJECT_TITLES_TITLE ON OBJECT_TITLES (TITLE);
CREATE UNIQUE INDEX OBJECT_TITLES_IDX ON OBJECT_TITLES (SERIAL_NUMBER,
TITLE_TYPE_ID, OBJECT_ID);
CREATE INDEX TITLE_TYPE_OBJECT_IDX ON OBJECT_TITLES (TITLE_TYPE_ID,
OBJECT_ID);

------------------------------------------------------------------------
----------------------------
The table structure of OBJECT_AUTHORS is:

CREATE TABLE OBJECT_AUTHORS (
    OBJECT_AUTHORS_OID DOM_RECORDID NOT NULL,
    OBJECT_ID DOM_RECORDID NOT NULL,
    AUTHOR_ID DOM_RECORDID NOT NULL,
    AUTHOR_TYPE_ID DOM_RECORDID,
    AUTHOR_ORDER DOM_SMALLNUMBER);

/* Primary keys definition */
ALTER TABLE OBJECT_AUTHORS ADD CONSTRAINT PK_OBJECT_AUTHORS PRIMARY KEY
(OBJECT_AUTHORS_OID);

/* Foreign keys definition */
ALTER TABLE OBJECT_AUTHORS ADD  CONSTRAINT FK_OBJECT_AUTHORS FOREIGN KEY
(OBJECT_ID) REFERENCES OBJECTS (OBJECT_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE OBJECT_AUTHORS ADD  CONSTRAINT FK_OBJECT_AUTHORS_AUTHORS
FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS (AUTHOR_ID);
ALTER TABLE OBJECT_AUTHORS ADD  CONSTRAINT FK_OBJECT_A_REF_282_AUTHOR_T
FOREIGN KEY (AUTHOR_TYPE_ID) REFERENCES AUTHOR_TYPES (AUTHOR_TYPE_ID);

/* Indices definition */
CREATE INDEX AUTHOR_ORDER_IDX ON OBJECT_AUTHORS (AUTHOR_ORDER,
AUTHOR_TYPE_ID, OBJECT_ID);

------------------------------------------------------------------------
----------------------------
The table structure of AUTHORS is:

CREATE TABLE AUTHORS (
    AUTHOR_ID DOM_RECORDID NOT NULL,
    NAME DOM_NAME NOT NULL,
    REFERENCE_COUNT DOM_REFERENCE_COUNT NOT NULL,
    USER_NAME DOM_USERNAME NOT NULL,
    CREATION_DATE DOM_CURRENT_DATE NOT NULL);

/* Primary keys definition */
ALTER TABLE AUTHORS ADD CONSTRAINT PK_AUTHORS PRIMARY KEY (AUTHOR_ID);

/* Indices definition */
CREATE UNIQUE INDEX UN_AUTHORS ON AUTHORS (NAME);

Quote
-----Original Message-----
From: Craig Stuntz [mailto:cstuntz@no_spam.vertexsoftware.com]

Posted At: dinsdag 30 januari 2001 19:22
Posted To: interbaseexpress
Conversation: Master-detail with IBQuery
Subject: Re: Master-detail: other question
        What is the table definition, index definitions, and query
optimization
PLAN for the query in question?

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Quote
Craig Stuntz wrote:

>         If that's the case, I think the fix may lie in a SELECTable stored
> proc.

        Sorry, that should read, "...if that's not the case..." (i.e., there is
no difference in speed when the JOINs are omitted).

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Quote
Ivo Lorie wrote:

> The detail query that gives the problem is:

        OK, as one quick test, can you try (temporarily) omitting the LEFT
OUTER JOINs and see if that makes a difference?  Obviously, the table
will open faster -- that's not what I mean.  What I mean is that with
the JOINs omitted do you still see a speed difference between the NULL
and the non-NULL param value?

        If that's the case, I think the fix may lie in a SELECTable stored
proc.

        HTH,

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Many thanks obliged! I thought it had to do with the joins but it was,
as you expected, also the case without the LEFT OUTER JOINs. I tested
the following SELECT-statement with all kinds of values (thus also NULL)
in the parameter and the difference in speed stayed.

        SELECT object_id
        FROM OBJECTS
        WHERE MASTER_OBJECT_ID = :object_id

Question is, is this normal since the field MASTER_OBJECT_ID is indexed
and IB uses it since the plan shows:

        PLAN (OBJECTS INDEX (RDB$FOREIGN52))

and RDB$FOREIGN52 is the index on MASTER_OBJECT_ID. The only thing I can
come up with, is the fact that this field allows NULL values to be
inserted. I did the same SELECT-statement but changed the WHERE-clause
into:

        WHERE OBJECT_TYPE_ID = :parameter

OBJECT_TYPE_ID is also a field with a foreign key relation. Hence it
contains an index. The only two differences I see is that firstly it
doesn't allow NULL-values and secondly it references another table. When
I executed this SELECT-statement and I inserted NULL for the parameter,
the result was shown as fast as if I enter a non existing value.

Knowing all this, I will change my code and use a SELECTable stored
procedure instead of doing tricks in the BeforeOpen event of TIBQuery.

I guess when you wrote to make a SELECTable stored procedure, you meant
something like the following:

create procedure SP_SEL_TEST (
  in_master_object_id integer)
returns (
  out_object_id integer)
as
begin
  /* Procedure Text */
  out_object_id = NULL;
  if (in_master_object_id IS NOT NULL) then
    for select object_id
        from OBJECTS
        where master_object_id = :in_master_object_id
        into :out_object_id
    do suspend;
  else
    suspend;
end;

Ivo

Quote
-----Original Message-----
From: Craig Stuntz [mailto:cstuntz@no_spam.vertexsoftware.com]
Posted At: donderdag 1 februari 2001 15:43
Posted To: interbaseexpress
Conversation: Master-detail with IBQuery
Subject: Re: Master-detail: other question

Craig Stuntz wrote:

>         If that's the case, I think the fix may lie in a SELECTable
stored
> proc.

        Sorry, that should read, "...if that's not the case..." (i.e.,
there is
no difference in speed when the JOINs are omitted).

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:Master-detail with IBQuery


Quote
Ivo Lorie wrote:

> OBJECT_TYPE_ID is also a field with a foreign key relation. Hence it
> contains an index. The only two differences I see is that firstly it
> doesn't allow NULL-values and secondly it references another table. When
> I executed this SELECT-statement and I inserted NULL for the parameter,
> the result was shown as fast as if I enter a non existing value.

        I suspect it's the first difference, but it's unfortunate that IB can't
determine, irregardless of this difference, that SOME_FIELD = NULL can
*never* be true and thus the WHERE clause will never allow any records
to be returned, so there's no point in even looking.

Quote
> Knowing all this, I will change my code and use a SELECTable stored
> procedure instead of doing tricks in the BeforeOpen event of TIBQuery.

> I guess when you wrote to make a SELECTable stored procedure, you meant
> something like the following:

        Yes, that should do it.

        -Craig

--
Craig Stuntz               Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Other Threads