Subject Re: [IBO] Views and table field defaults
Author Tony Masefield
--- In, Helen Borrie <helebor@t...> wrote:
> At 05:15 PM 21/07/2005 +0000, you wrote:
> >Hi All,
> >
> >I have a table that has default values on some of the fields
> >(specifically dates). There are two views splitting the table "in
> >half", one for use by the "originator" and one for
the "responder".
> >Using IBO V 4.5, TIBO strain database, query and transactions (one
> >each of the last two for each view).
> >On inserting into the table via the views, the default values of
> >table fields are not inserted - whether or not these fields are
> >included in the view being inserted. These date fields BTW are
> >intended
> >as "origination date" - the day the record was inserted -
and "last
> >updated" - the date the record was last edited, and are thus not
> >entered by the user per se.
> >The TIBO components are pretty much set up in their 'basic' modes
> >(default values).
> >Am I missing a property setting somewhere in one of the IBO
> >components or is this the default behaviour of Firebird and would
> >have to coded into the "Before Insert" trigger?
> No, this is not an IBO issue. It has to do with the
characteristics of the
> view - including whether it is naturally updatable, or is a non-
> view that is made updatable by way of triggers. That in turn, has
to do
> with the version of server you are using. Without going into
> detail....IB 6 was released with serious bugs in the updating of
> views. That got fixed in Firebird 1, in the sense of making the
> more predictable.
> Then, in Firebird 1.5, it got fixed again to make it so that a
> non-updatable view that was made updatable by triggers would cause
> triggers (including the system triggers created for constraints)
on the
> underlying tables to be ignored. It has been twiddled and tweaked
since Fb
> 1.5, as well.
> If you are using InterBase, I have no idea what, if any, fixes
have been
> done in there for the original bugs; other than the fact that
> harvested a lot of Firebird's bugfixes for IB 6.5.
> The only way you're going to know what is the expected behaviour
for the
> version of server you are using is to track down where your
> version is at. If you are using Firebird, you can track it
through the
> releasenotes - all of them leading up to the release version you
are using.
> On the IBO side, you can bypass all of this cross-version
confusion and
> inconsistency by not activating RequestLive=true on the iboqueries
over the
> views but, instead, writing custom xxxSQL to that performs DML on
the table
> directly. If your views have different fieldnames from the
> tables, then you will have to write executable stored procedures
to achieve
> this.
> Also make certain that your Keylinks for the views are spot-on.
> Another thing to be well aware of - regardless of whether your
queries are
> over views or tables - is that server defaults kick in ONLY on
inserts and
> ONLY if the field in question is not in the INSERT statement. IBO
> complain about missing values for non-nullable fields only if said
> are in the SELECT statement. You can apply FieldsDefaultValues to
> fields to have them applied to inserts. Any values that you want
> by database defaults or triggers should be omitted from the SELECT
> custom DML statements.
> Helen

Firstly, once again thank-you Helen for a comprehensive reply. More
useful information that will be filed for later reference.

To answer your queries, I'm working with FB 1.5/IBO 4.5, it's a
single table with two views and yes, you are absolutely right in
your last paragraph, the date fields with the default values were in
the TIBO query insert SQl. Taking them out solved the problem, the
default values have now 'appeared'.
Had gone through all the IB 6 manuals, plus most of whatever other
documents I had at hand, and had found no reference to this problem.
Didn't check the release notes though.
The question that still remains is why should a simple (i.e. single
table in this instance) updatable view 'cancels' the default values
for a field in the table if that field is included in the view query
insert? Is there a logical explanation why this behaviour occurs?
OK, I know this is not the thread for FB itself but I mention it
here for continuance or would you prefer me to post this question
under FB (I'm still learning 'thread etiquette'!)?