At 07:43 AM 25/08/2006, you wrote:

>I have an application that is using firebird 1.5.3 and connecting
>using BDE components. I am slowly converting a section at a time to
>IBO Components. one part of the APP has several users selecting items
>from inventory and I am updating the qty by Selecting the current qty
>in a select statement using a live query, setting to Edit state
>decreasing the value then posting the record. I seem to be having
>problems with the qty not getting updated correctly.
>
>does someone have an air tight way to make sure the qty is decreased
>and increased correctly as items are added to or cancelled off of an
>order.

Make it a high priority to understand how transactions work in Fb/IB!!

The prime problem here is that your quantity updating mechanism
assumes that there is only one user. As soon as two or more
transactions hit the same inventory record simultaneously, you have
the potential for your inventory accounting to become rapidly
meaningless, especially if the transactions are in tiReadCommitted
isolation. The bad news is that the same bug exists in your legacy
code if this is a conversion. I hope you have some log that is
maintained between stocktakes to keep a backtrace on
quantity-affecting transactions.

In a multi-user environment, updating stock quantity is a global
task. Architecturally, you have to remove the ability for your
application to update the inventory quantity and replace this
client-side operation with After triggers on your transaction detail
record. You must be conscientious about transaction configuration,
since you must avoid conditions where multiple users can overwrite
one another's changes.

Dynamic inventory data (such as Quantity and Next Delivery, for
example) should be stored separately from static data (such as
colour, size, barcode, etc.), in one and only one place, and it is in
this place where the detail triggers will try to
operate. (Simplistically one Stock Quantity table, although
real-life granularity requirements might necessitate a multi-layer
structure).

No client should ever attempt to alter stock quantity directly. The
triggers need to be written so that they test conditions and throw
custom exceptions when tests fail. Your only concern on the client
side should be to intercept exceptions (custom ones and conflicts)
and handle them appropriately.

Helen