At 12:08 AM 5/04/2003 +0000, you wrote:

>I have a situation where each transaction has to perform various
>operations, such as: update a client's balance, update a cashier's
>till, insert a journal entry for each item (1 or more) in the
>transaction. My proposed solution using IBO Objects is the creation
>of a script with the corresponding update or insert statements. My
>logical sequence of execution would be:
> try
> TIB_Script.IB_Connection := TIB_Connection;
> TIB_Script.IB_Transaction :=TIB_Transaction;
> if not TIB_Transaction.TransactionIsActive
> then TIB_Transaction.StartTransaction;
> TIB_Script.Execute;
> TIB_Transaction.Commit;
> except
> TIB_Transaction.RollBack;
> end;
>
>- my assumption for using a script is to reduce the communication
>load, rather than doing a transaction for each update/insert
>statement. Does this sounds reasonable?

No. Using a script for this type of self-contained operation doesn't make
sense, when you could do the whole thing via a parameterised stored procedure.

>- I am considering to have generic update/insert stored procedures,
>which will be called instead of sending an update/insert SQL
>statement. Is it reasonable to assume that this is faster than the
>update/insert statement?

Yes. If you have a task which updates multiple tables, inserts rows
somewhere, logs something, etc., then SP is the only sensible way to go.

>
>- should I add commit statements within the script (possibly as the
>last statement in order to avoid a "half" committed script), or
>leave the commit to Delphi through the IBO component as it is the
>logical sequence above?

No way. Don't go the script route. Enclose the whole task in a single
transaction via the SP as you suggested, so that, if anything goes wrong in
the course of the work, everything can be rolled back to ensure that the
database state is not changed by a failed operation.

>- how can I avoid deadlock conflicts using the IBO components under
>this scenario? I will have several clients performing transactions.

Deadlock sounds ugly, but in fact it is your best friend in a tight
multi-user environment. If you have several clients performing
transactions involving the same records, you *need* deadlocks, so that your
application can detect a conflict situation, resolve things, and keep the
work flow moving.

>
>Can anyone tell me if my assumptions are reasonable or are there
>better approaches/suggestions?

Think "stored procedure" and you will be right on-track.

Helen