?
Solved

Delphi 7 - Transaction whit devart component

Posted on 2010-11-12
8
Medium Priority
?
699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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 38

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1000 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 101

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

771 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