Subject Re: [IBO] Re: ibsql - grid/insert problem
Author TeamIBO
> Bingo, that's correct, Geoff -- no defined PK causes the problem.

> I've also discovered that the following trigger (before insert) can
> cause the problem...

> new.F_PK = gen_id(gen_shared, 1);

This item you can fix (and should fix in all your triggers) by

if new.F_PK IS NULL then
new.F_PK = gen_id(gen_shared, 1);

In IBO setup the dataset GeneratorLinks property as

or the connection GeneratorLinks property as

You need to remember that triggers will fire whenever there is an
insert or an edit or whatever the trigger is for. As such you need to
make provision for the possibility of other circumstances, for example
importing records with existing F_PK values.

IBO needs some form of unique identifier access to a table for
interactive use. It does not actually have to be a primary key but
this is usual and recommended. If you dont have a primary key then
you MUST supply KeyLinks for the dataset specifying how to identify
the records. (If there is a defined primary key then IBO can usually
manage to detect automatically - for simple selects.)

Why is it so?...

IBO generally NEEDS to know the identity of a record BEFORE an insert
is actually posted - and that is the reason for setting up the
GeneratorLinks properties. On insert, when preparing the new record,
IBO will read the next generator value for the given field as
specified in the generator links.

In this way IBO knows the identity of the record before it is posted,
and so can read the record back individually using that identity. The
server triggers must NOT change the PK value if it is given in the
insert, or the information that IBO obtained will no longer be valid
and the record cannot be read back individually.

When IBO posts a record it uses the KeyLinks to refresh the record
from the dataset - and so display changes applied at the server. There
are several instances where IBO **MUST** be able to do this,

* If there are BLOB fields on the table then IBO absolutely has to
be able to refresh the record back into the query - because the
server assigned BLOB_ID actually changes when a blob is written at
the server (both for new blobs AND when changing existing blobs). So
if the record is not refreshed the blob_id in the record buffer will
be invalid and cause exceptions if you try and use the record buffer
directly. IBO will detect if blobs exist in the record and perform a
refresh automatically (on TIB_Query and other buffered datasets, but
NOT on TIB_Cursor).

* If you turn on various options in the BufferSynchoFlags. These
options are often necessary, so that you can see trigger initiated
side effects after posting an insert or edit.

There are also some other instances relating to be able to
retrieve/refresh specific records.

Without the above mechanisms IBO would have to refresh the entire
dataset after every change in order to be able to see the server side
changes on the one record you just inserted. This has obvious
performance implications but its worse than that, with no way to
identify the record (in advance) it could not even reliably put you
back on the record you just posted (how could it know which record
that was?).

As implied above, this mostly relates to bufferred datasets,
TIB_Query, TIBOQuery etc. Things are different with unbuffered
datasets (TIB_Cursor) which is setup for scanning/inserting
performance and not for user interactive use - but thats a different
story and this email is long enough.


Geoff Worboys - TeamIBO
Telesis Computing