mORMot Open Source

leus

Member

Registered: 2012-09-05

Posts: 79

"Reserving" a single record, updating a value, and returning it

I have a program that emits sales receipts. Correlative numbers for the receipt are assigned beforehand by the Tax office, and need to be distributed between branch offices and points of sales (terminals).

I found out that the most convenient way is, once a correlative series has been received from the Tax office, is to "preload" all receipts, so I have the correlative numbers already stored in the database. This way I can "move" series of numbers between branches and not worry about internet connectivity.

The problem is that the created receipt is a signed XML document, and it *needs* the correlative number beforehand.

So, to emit a single receipt, a terminal (client, POS) must be able to reserve the next available correlative number, then create the XML document, then update the record, and all of this in an atomic operation. This is my current design:

• Read the first record that has the Branch and Terminal fields as null (unused)

• Update the record with the appropriate values for Branch and Terminal (thus marking it as "used")

• Create the receipt data (this need the Correlative number obtained above)

• Update the record with the generated receipt XML

Right now, I'm doing the following for the first part (obtain the reserved receipt). While this seems to work, I wonder if there is a way to update a single record in an atomic way.

function GetReservedReceipt(Branch, Terminal: RawUTF8): Int64;
var
  Receipt: TReceipt;
  Id: TID;
begin
  Result := -1;
  if ServerDB.TransactionBegin(TReceipt) then
  begin
    try
      Receipt := TReceipt.Create;
      try
        if ServerDB.Retrieve
          ('Available is NULL and Branch is NULL and Terminal is NULL',
          Receipt) then
        begin
          Receipt.Available := false;
          Receipt.TrackId := 0;
          Receipt.Branch := Branch;
          Receipt.Terminal := Terminal;
          if ServerDB.Update(Receipt) then
          begin
            Result := Receipt.AssignedCorrelativeNumber;
          end;
        end;
        ServerDB.Commit();
      finally
        Receipt.Free;
      end;
    except
      ServerDB.RollBack();
    end;
  end;
end;

So I guess my question is, is there a way to update a single record based on a criteria that may match multiple records?

Last edited by leus (2020-12-15 19:02:41)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: "Reserving" a single record, updating a value, and returning it

Use ServerDB.WriteLock/WriteUnLock instead of a transaction: on server side, it will be faster and safer.