Subject Re: [IBO] updatable views and 3.6Cc
Author Helen Borrie
At 08:34 AM 09-02-01 -0800, you wrote:
>Hi Jason,
>I actually had a chance to dosome coding. Its good to be back!
>Last night I ran into an apparent issue with updatabale views and
>My view has a single surrogate key integer field and the requisite triggers
>to make it updatable (this is verified to be true).
>When I set a TIBOQuery to select * from myview and put in keyLinks and
>keyrelation the query is not editable. However if I remove these entries
>and just do a keylinks autodefine, then all is well. I much prefer to take
>control of these properties myself.
>has this been reported?

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 the
point of view of the IBOQuery, which knows only about the view, not the
underlying tables).

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.

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).

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 occurs.



All for Open and Open for All
InterBase Developer Initiative ยท