Subject Re: [IBO] Re: TIBOTable.Insert then Edit
Author Helen Borrie
At 03:53 PM 6/12/2005 +0000, you wrote:
>I haven't seen a response to this. Let me ask a simpler question.
>Is it possible to succesfully carry out the sequence Insert, Post,
>Edit (same record), Post with a TIBOTable?

Depends on what you mean by "success". Can you do it? yes. Should you do
it? No. You can win the battle, but you will lose the war.

More comments inline.....

>Kevin Donn
>--- In, "kgdonn" <kd.ibo@M...> wrote:
> >
> > It seems either I or IBO is missing something fundamentally
> > important. I'm doing an Insert, Post, Edit, & Post on a
> > and I get "Record was not located to update". What gives?
> >
> > Here's the code:
> >
> > procedure TForm1.InsEditBtnClick(Sender: TObject);
> > begin
> > with TIBOTable.Create(nil) do try
> > TableName:='LEA';
> > IB_Connection:=IBODatabase1;
> > Open;
> > Insert;
> > Fields[0].AsInteger:=5000;
> > Post;
> > Edit;
> > Fields[1].AsString:='Donn';
> > Post
> > finally Free end
> > end;

Unless you changed from the default, ibodatabase's transaction is in
Autocommit. The first Post both posts and commits the insert. The default
CommitAction of ibotable is caInvalidateCursor, which basically means the
cursor is "nowhere" after a commit. Hence, the second Post has no Keylink
from which to populate the WHERE clause it creates for the UPDATE statement.

Can you do it? yes. Study the help for TIB_CommitAction to see which
behaviour you want after the Commit - in theory, caRefreshKeys should be
enough, since there is no data in the inserted row except for the primary
key (which, one hopes, you have set as the Keylink).

However, when the keys are refreshed, the buffer pointer won't be on the
row you inserted, since it's at the very end of the table. You could try
creating a bookmark for the row before you call post and then calling
GoToBookmark afterwards, before you call Edit.

Alternatively, setting BufferSynchroFlags := [bsAfterInsert] as part of
your Create sequence might be enough to do the trick.

Now some uninvited comments.

Doing this sort of stuff in a table component is costly. You'll notice
that native IBO doesn't even have a table component. This is by
design. With Fb/IB there is no physical structure on disk that is operated
on directly, as there is with Paradox, Access, etc. (which the VCL's TTable
was designed for). This model is therefore hopeless for a client/server
DMBS If the table is bigger than about 200 rows. The traffic on the wire
for keeping a table component's buffers in synch with database state is
horrendous, even if you weren't using Autocommit and forcing either the
whole table (caRefresh) or a set consisting of the PK of every row in the
table (caRefreshKeys) to pass across the wire every time the user clicks
the Save button.

From an application design POV, it doesn't make sense, either. Why would
you want to create a row, commit it and then immediately update it? Why
not apply the non-key data directly in the Insert statement? The problem
here is that you create a row with no data. It now exists and the only way
to "undo" it will be to delete it. So, supposing that the data you apply
in the Edit throws an exception, you have this empty row sitting in the
database with no context. This is essentially the effect of the
application designer forcing tasks to be non-atomic.

And then, if you decide to take control of the transaction yourself and
take the care needed to make user tasks atomic, you bump into the problem
of proliferating record versions on the server. Inserting and then
updating (or, perhaps, more pertinently, deleting) the same row inside the
same transaction is (currently) allowed. In the multi-generational
architecture (MGA), multiple operations on the same row within the same
transaction create multiple versions of the that row, which can potentially
aggravate problems associated with garbage collection. It is so contrary to
recommended practice that it has been made illegal in Firebird 2.