Solved

Delphi 7 - Transaction whit devart component

Posted on 2010-11-12
8
675 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:danz67
8 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34120364
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.
0
 

Author Comment

by:danz67
ID: 34121615
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?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 34129762
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

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
ID: 34132056


Dont start transaction initially
Make all your changes in memory datasets that reflect your actual tables in the db
When you are ready to save your invoice,

start the transaction
  Create the invoice
  Reduce stock amount
  Commit transaction
if exception occures
  roll back transaction  //all data you changed is rolled back so you dont have incosistency
  raise the error
end
0
 

Author Comment

by:danz67
ID: 34134297
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

0
 

Author Comment

by:danz67
ID: 34134361
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???

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34700453
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now