Subject Re: [IBO] Lock Conflict Problem
Author Helen Borrie
At 08:38 AM 23/06/2005 +0300, you wrote:
> > However, why try to store these averages at all? You can create a
> > read-only view that contains the calculated value as a COMPUTED BY
> > field. If nobody is updating that average, then everybody can read that
> > view and see the latest average in his own transaction context.
> >
> > Helen
> >
>Thank you Helen. I dig your book and try to find some examples about the
>computed columns. But those examples are not the ones that I need.
>Let's say there are more than 1 000 000 records in table B and I created a
>view like this
>" Select cola,colb,colc, (select sum(cola) , sum(colb) from tableB ) as
>colsuma,colsumb from
>tableA " will it work fast ?
>is this syntax correct ?

No. If you work out the data you actually want, I'll try to help with the
syntax; but what you are asking here isn't logical without some
correlation between tableA and tableB. Also, as I understood, it wasn't
sums you wanted to represent, but averages....

>if not can you give me an example ?

Certainly, given a practicable problem description.

>I tried another way ; put an IBSql component on the form and use it to get
>the averages ( Master detail related) . It is working very fast but this
>time I can't see these results on the same grid ( in one line )

I can't make sense of this, either. Do you mean "an IB_DSQL
component?" This component is not a dataset, i.e. not usable for
SELECTs. If you use an IB_DSQL for executing an executable stored
procedure, you can read any return values in the Fields[] array after

>Also I have another question about autocommit on transactions. If the
>autocommit is false and also the server autocommit is false then
>how will be the server's behavior about the triggers when added a new
>record to the table. From my tests the trigger fires whatever the
>autocommit is. If I prepare a transaction with autocommit to false I am
>expecting the triggers to fire after I commit the transaction. Am I thinking
>wrong ?

Yes, you seem unclear about how database state changes work. Triggers
execute upon the POSTING of a state-changing operation. POSTING such an
operation (if successful) causes all triggers to fire in the specified
sequence. Once the POST is complete, all triggers have fired.

"Post" happens at statement level.

The posted changes are seen only by the transaction that requested the
post. No other transaction will see those changes until (or unless) the
transaction is committed; and, even then, only other transactions that are
in ReadCommitted isolation will see that committed work. Commit makes
permanent every operation that has been posted since the transaction began.

Autocommit is a client-side thing, not something the server does. It
causes a form of commit known as "commit with retain" to follow immediately
upon each post. The Commit with Retain (implemented in Delphi by the
CommitRetaining method) causes a new transaction to start after the commit,
without clearing the resources of the preceding transaction. It was put
there by Borland to make transactional database operations appear like
Paradox (which isn't transactional). It has its benefits but should be
used conservatively.

ServerAutocommit is something different again, with a specific use when
creating metadata, and should be avoided for interactive applications -
keep it set false.