At 08:48 AM 10/03/2003 +0000, you wrote:

>Hi,
>
>Could someone please give a hint for best practise on transaction
>management if you update or insert a record. I use TIB_query for it,
>and my TIB_tranction uses autocommit, itCommitted, and does have a
>timeout of 120 seconds before performing a commit.
>
>The following code (simplified) all perform the correct task but I'm
>not sure about the best code, and why it's better code:
>
>1. doing nothing, just let the timer of IBO do the job
>
>with IB_query1 do
>begin
> sql.clear;
> sql.add('update employees set date = :date where date is null');
> execsql;
>end;
>
>2. perform commit afterwards:
>
>with IB_query1 do
>begin
> sql.clear;
> sql.add('update employees set date = :date where date is null');
> execsql;
>end;
>try IB_transaction1.commit
>except IB_transaction1.rollback;showmessage('oops');end;
>
>3. start separate transaction each time:
>
>IB_transaction1.starttransaction;
>try
> with IB_query1 do
> begin
> sql.clear;
> sql.add('update employees set date = :date where date is null';
> execsql;
> end;
>finally
> try IB_transaction1.commit except
> IB_transaction1.rollback;showmessage('oops');end;
>end;

First, IB_Query is a wasteful way to do this. Use IB_DSQL or IB_Cursor for
non-select (DML) queries.

Second, in these procedures, you are not applying any *value* to the :DATE
parameter, so these updates will either fail or do something weird.

Third, unless your DML query is a "one-off", ad hoc type of query, you are
wasting a lot of resources by changing the SQL property every time you run it.

As far as deciding when to commit the transaction, your choices will depend
very much on whether you want to keep things uncommitted (and allow the
possibility of subsequent rollback, in case something else fails) or to
cleanly commit the single operation. I can't tell from your examples since
they all represent "one-off" or "first-time" SQL and the statement is
performing a positioned update. Generally, you would want this to be
committed as soon as it completes.

In the case of a searched update (i.e. you have a WHERE clause that
searches for a specific Employee record), the situation would probably be
different. Your update might be constrained to a single record, or just a
few. You might want to keep the transaction uncommitted until you have
finished feeding in fresh Employee parameter values, and commit the whole
upon completion.

Helen