Subject Re: [IBO] updatable views and 3.6Cc
Author Rob Schuff
----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <>
Sent: Friday, February 09, 2001 3:56 PM
Subject: Re: [IBO] updatable views and 3.6Cc

> At a guess, using KeyRelation on a dataset that is not joined is the
> problem here. Effectively, a view (even one which involves joins in its
> definition) is not a joined dataset as there is only one relation (from
> point of view of the IBOQuery, which knows only about the view, not the
> underlying tables).

Yes but I don't think it should cause a problem. it should just be

> Setting your own Keylinks should work from the point of view of navigation
> in the buffer and relocating after Refresh; but they are not meaningful
> from the point of view of filling parameters for updates and inserts. The
> Keylinks should refer to columns in the view, not the underlying table.

it did refer to a column (same name) in the view.

> When you say your view has "a single surrogate integer key field" I assume
> you mean you are somehow generating a unique number into the view to
> enforce uniqueness, since views don't have explicit keys (although they do
> have the internal RDB$DB_KEY, which is unique, and which you can define as
> your keylinks in IBO using the DB_KEY identifier. This has traps, though,
> since DB_Keys have the potential to change once a commit occurs).

In the view I'm joining two tables that have a 1-1 relationship (people and
users). Of course the view itself doesn't have a PK, but inherits one fron
the underlying table. There is no reason to rely on db_key here.

> You will make the dataset updatable if you place explicit updateSQL
> statements in the EditSQL, InsertSQL and DeleteSQL properties - use the
> statements that worked with your trigger tests, using parameters. It is
> not even necessary to set RequestLive to true in this case. IBO will pick
> up the appropriate values from the Fields[] array to plug into the
> updateSQL. Your updateSQL should not include the primary key columns of
> the underlying tables unless you are fetching these values into the
> parameters yourself with the Gen_ID() function before the Post event

I ended up just setting the keylink, and leaving the keyrelation property
blank **and** left keylinks autodefine set to true. left all update insert
delete code in the triggers. seems to work fine, but I'm still not clear on
why setting the keyrelation should keep this scenario from working.



> hth
> Helen
> All for Open and Open for All
> InterBase Developer Initiative ยท
> _______________________________________________________