Delphi 7 - Transaction whit devart component
hi,
I created an application that must work in a local network.
Consider the creation of a invoice, when I create, a transaction begins with the component of TMyConnection Devart-CoreLab, from the ARTICOLI table confirm the selected row be reported in the invoice, if in another computer do the same thing, until the first computer I do not close the transaction, the second computer can not do anything.
I created an application that must work in a local network.
Consider the creation of a invoice, when I create, a transaction begins with the component of TMyConnection Devart-CoreLab, from the ARTICOLI table confirm the selected row be reported in the invoice, if in another computer do the same thing, until the first computer I do not close the transaction, the second computer can not do anything.
Ephraim Wangoya
Don't leave your transactions open. Any updates handled in a transaction should be committed immediately otherwise all other users attempting to update the table are locked out.
ASKER
I know, I leave the transaction open because when I do an invoice and select a record from the ARTICOLI table, the stock must decrease, then if I decide to cancel the bill should not decrease the stock. If I close the transaction soon as you say, the stock is reduced and then decide to cancel the invoice do I do?
oracle is by default read-committed
so until your transaction is finished nobody else will see the pending stock changes
you could solve it with an additional table to add the pending stock changes in a autonomuous transaction
> user A
start tran 1
create invoice A1
Add stock article A100 to invoice for 200 amount
>> in auto tran, add record to pending_stock_change table for this article
if user B already created a new invoice too and you want to warn or warn anybody of the pending stock changes
you need to setup a system to look for any changes in the pending_stock_change table
you could show the pending changes to the other users like this
you need to use threading for this to work adequatly
so until your transaction is finished nobody else will see the pending stock changes
you could solve it with an additional table to add the pending stock changes in a autonomuous transaction
> user A
start tran 1
create invoice A1
Add stock article A100 to invoice for 200 amount
>> in auto tran, add record to pending_stock_change table for this article
if user B already created a new invoice too and you want to warn or warn anybody of the pending stock changes
you need to setup a system to look for any changes in the pending_stock_change table
you could show the pending changes to the other users like this
you need to use threading for this to work adequatly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mmm ...
Now i run a stored procedure for recuce stock, I think the best solution is to create a stored procedure to cancel the reduce stock, if I cancel the invoice.
I created the procedure works on each record concerned enough to change ... instead of decreasing the quantity, needs to be increased, the only problem is that I should do this on all rows in the invoice via mysql and I do not know how.
I enclose the procedure I use for the drain (to change)
Now i run a stored procedure for recuce stock, I think the best solution is to create a stored procedure to cancel the reduce stock, if I cancel the invoice.
I created the procedure works on each record concerned enough to change ... instead of decreasing the quantity, needs to be increased, the only problem is that I should do this on all rows in the invoice via mysql and I do not know how.
I enclose the procedure I use for the drain (to change)
CREATE DEFINER = 'root'@'localhost' PROCEDURE `SCARICA_DISPONIBILITA`(
IN idarticolo INTEGER(11),
IN idmagazzino INTEGER(11),
IN quantita DECIMAL(15,3)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE `giacenze` SET `giacenze`.`disponibilita` = `giacenze`.`disponibilita` - quantita
WHERE `giacenze`.`idarticolo` = idarticolo AND `giacenze`.`idmagazzino` = idmagazzino;
END;
ASKER
I think we should create a loop inside the procedure that scans all the records contained in the bill (table dettfattura)
...
''in delphi i use this WHILE NOT dettfattura.EOF do
update table....
dettfattura.NEXT''
in mysql what???
...
''in delphi i use this WHILE NOT dettfattura.EOF do
update table....
dettfattura.NEXT''
in mysql what???
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.