Avatar of danz67
danz67Flag for Italy asked on

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.
DelphiMySQL Server

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
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
danz67

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?
Geert G

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
danz67

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)




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;

Open in new window

ASKER
danz67

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???

Mike McCracken

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.