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

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

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

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 .

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

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

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

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

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

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