Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 21 total
Thread Open Row exclusively
Tue, Sep 22 2015 6:24 AMPermanent Link

Pasquale

Web Pos srl

how do I open a table row in a database mssql exclusively ?

my problem is that I have a table as follows :

users : varchar(20)
plafond : money

any time a user can access this table and subtract money from record plafond .

Obviously, each subtraction operation of the money must be made exclusively before it is possible to make another
Tue, Sep 22 2015 11:36 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Pasquale,

<< how do I open a table row in a database mssql exclusively ? >>

That is handled by MS SQL Server during any updates.

What exactly is your concern here ?  That the money balance will go below zero ?  If so, then that is something to be enforced at the database level via MS SQL Server with a check constraint.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Sep 22 2015 11:54 AMPermanent Link

Pasquale

Web Pos srl

My problem is that a user must be able to update money only when another user he has finished update the record money




Tim Young [Elevate Software] wrote:

Pasquale,

<< how do I open a table row in a database mssql exclusively ? >>

That is handled by MS SQL Server during any updates.

What exactly is your concern here ?  That the money balance will go below zero ?  If so, then that is something to be enforced at the database level via MS SQL Server with a check constraint.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Sep 22 2015 12:28 PMPermanent Link

Pasquale

Web Pos srl

This code write

procedure TFormGestionePlafond.myCommitError(Sender: TObject; const ErrorMsg: String);
Begin                                                              
 Database.AfterCommit   := nil;
 Database.OnCommitError := nil;
 PosPlafond.Cancel;
 Errore := True;
 
 MessageDlg(ErrorMsg,'Informazione',mtError,[mbOk],mbOk,nil,True);
End;

procedure TFormGestionePlafond.DetrazionePlafondClick(Sender: TObject);
begin
 database.OnCommitError := myCommitError;
 Errore := False;

 PosPlafond.Params.Clear;
 PosPlafond.Params.Add('IDMerchant='+IDMerchant);
 DataBase.LoadRows(PosPlafond);
end;

procedure TFormGestionePlafond.PosPlafondAfterLoad(Sender: TObject);
begin
 IF (PosPlafond.RowCount = 1) And (PosPlafond.columns['Stato'].AsString = 'Completato') Then
 Begin
   IF Not DataBase.InTransaction Then
   Begin
     DataBase.StartTransaction;
     Try
       PosPlafond.Update;
       PosPlafond.columns['Stato'].AsString  := 'Prenotato';
       PosPlafond.Save;
       
       IF DataBase.InTransaction Then
           Database.Commit
          Else
           MessageDlg('Errore nessuna TRANSAZIONE in corso!','Informazione',mtError,[mbOk],mbOk,nil,True);
     Except
       PosPlafond.Close;
       Database.Rollback;                                                              
       raise;
       //MessageDlg('Errore transazione su Prenotazione Plafond!','Informazione',mtError,[mbOk],mbOk,nil,True);
     End;
   End
   Else
    MessageDlg('Altra transazione in corso!','Informazione',mtError,[mbOk],mbOk,nil,True);
 End
 Else
 Begin
   EsitoTransazione := 'Plafond Bloccato';
   MessageDlg('Plafond giÓ prenotato in un altra transazione!','Informazione',mtError,[mbOk],mbOk,nil,True);
   Close;
 End;  

end;

procedure TFormGestionePlafond.PosPlafondRowChanged(Sender: TObject; Column: TDataColumn);
begin             
IF Not Errore Then
Begin
 IF (column <> nil) Then
    IF column.AsString = 'Prenotato' Then
    Begin

      IF PosPlafond.columns['Stato'].AsString = 'Prenotato' Then
      Begin

        IF PosPlafond.columns['Plafond'].AsFloat - PrezzoMerchant >=0 Then
        Begin

            DataBase.StartTransaction;
            Try
              PosPlafond.Update;
              PosPlafond.columns['Plafond'].AsFloat     := PosPlafond.columns['Plafond'].AsFloat - PrezzoMerchant;
              PosPlafond.columns['Stato'].AsString      := 'Completato';
              PosPlafond.Save;

              IF DataBase.InTransaction Then
                Database.Commit
              Else
              Begin
                MessageDlg('Errore nessuna TRANSAZIONE in corso!','Informazione',mtError,[mbOk],mbOk,nil,True);
                Exit;
              End;

              EsitoTransazione := 'Plafond Detratto';
            Except
              Database.Rollback;
              raise;
              //MessageDlg('Errore transazione su aggiornamento Plafond!','Informazione',mtError,[mbOk],mbOk,nil,True);
            End;
        End
        Else
        Begin
          EsitoTransazione := 'Plafond Insufficiente';
          MessageDlg('Plafond insufficiente!','Informazione',mtError,[mbOk],mbOk,nil,True);

          DataBase.StartTransaction;
          Try
            PosPlafond.Update;
            PosPlafond.columns['Stato'].AsString  := 'Completato';
            PosPlafond.Save;

            Database.Commit;
          Except
           Database.Rollback;
           raise;
           //MessageDlg('Errore su transazione Plafond insufficiente!','Informazione',mtError,[mbOk],mbOk,nil,True);
          End;
        End;
      
        Close;
      End;

    End;
End
Else
 MessageDlg('Errore su transazione Plafond !','Informazione',mtError,[mbOk],mbOk,nil,True);
end;


The problem is : If two users attempt to do simultaneously operation generates a commit error .
Tue, Sep 22 2015 1:12 PMPermanent Link

Walter Matte

Tactical Business Corporation

Pasquale wrote:

None of the database activity happens in the browsers.  So the EWB code only runs in the browser.  When you do the Database.Commit - the request gets sent back to the Server and the Server does the database work.

So are you using the EWB Server or PHP as the backend to do the MSSQL work?  That is where you need to do the MSSQL.StartTran - MSQL.EndTran effort that deals with the database.


And are you sure you would want EXCLUSE access?  so that the database is limited to one connection?



The EWB code is magic, it manages all the data and sends a JSON package to the server with the request to Add, Edit or Delete from the database.  So look at the server side to deal with you MSSQL needs.

Walter
Tue, Sep 22 2015 5:05 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Pasquale wrote:

<< My problem is that a user must be able to update money only when another user he has finished update the record money >>

The short answer is: no, you can't do what you're asking in an EWB client application.

The only way to handle this would be to write an EWB Web Server module that implements a custom request and handles this in MS SQL Server.  And describing how you would so is beyond the scope of a support forum post.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Sep 22 2015 5:06 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Pasquale,

<< The problem is : If two users attempt to do simultaneously operation generates a commit error . >>

What is the exact error message that you're seeing ?

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Sep 23 2015 8:58 AMPermanent Link

Pasquale

Web Pos srl

how do I catch and handle this error ?

This error I create it by doing two tasks simultaneously .

I think the problem is this : when a user makes an update while the other still not done then the second is the inconsistent database and then goes wrong commit



Attachments: error.png
Wed, Sep 23 2015 10:20 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Pasquale


What you need to do is implement your own specialised locking. Add an extra column to the table (say InUse BOOLEAN) and test that before you allow anyone to start making changes. If its false then set to true and proceed, if true then give the user a message telling them to try again in a few minutes. As part of the posting money to the table reset the flag to false.

Roy Lambert
Wed, Sep 23 2015 11:31 AMPermanent Link

Pasquale

Web Pos srl

if you look at the code written in previous posts you realize that I have already done this but the situation does not change in the sense that when two operations on the table are the same according to one of the two is in error committed and locks




Roy Lambert wrote:

Pasquale


What you need to do is implement your own specialised locking. Add an extra column to the table (say InUse BOOLEAN) and test that before you allow anyone to start making changes. If its false then set to true and proceed, if true then give the user a message telling them to try again in a few minutes. As part of the posting money to the table reset the flag to false.

Roy Lambert
Page 1 of 3Next Page
Jump to Page:  1 2 3
Image