Firebird perform automatic reset on Generator

Question

Environment : Linux server running Firebirdcs 2.5 Superclassic ; desktop application Delphi 7 Dbexpress; hundred of machines consuming.

Dear friends,

In the database there is an order table where part of the PK this table is incremented by a Generator called by the application "SELECT GEN_ID..." before the recording of insert.

I observed that in the call to Generator, whether it has been made Commit, Rollback or nothing the Generator always increases.

The problem is I need to get this generator has an increment limit and reached that limit he restarts counting.

Like the Generator there is no event that I could implement Trigger, I thought about:

Create a Stored Procedure; the application shall consult that Stored Procedure within a controlled transaction context ( Readcommitted ) that would perform the SELECT GEN_ID and the value returned being the limit through a AUTONOMOUS TRANSACTION another select to reset generator.

There would be a competition problem Stored Procedure and that would be the best way?

Thank you in advance to those who can help me.

Answer 1

If you want to limit it to a specific value, as in the case of 999999, use...

SELECT MOD(GEN_ID(COD_PEDIDO, 1),999999) AS ID FROM RDB$DATABASE

So you don’t have to deal with anything in the application and you don’t have to worry about resetting the Quence/Enerator, and you don’t have to deal with it in the process.

Answer 2

Generator doesn’t always increase +1 when searching for it, see the examples:

Here will be incremented +1 to Generator

SELECT GEN_ID(GENERATOR, 1) AS ID FROM RDB$DATABASE

Already this way below, only the current value of Generator is returned

SELECT GEN_ID(GENERATOR, 0) AS ID FROM RDB$DATABASE

I believe you can create a Rigger in your own order table and do the desired validation and restart the count of it.

Example - EDITED

I’m not sure how you do in your application, but I’ll give you another example and you try to apply, is another example using Rigger (I don’t know much about procedures)...

Note: I tested with Firedac and read in some places that Dbexpress also works with returning

first - Create Trigger by applying the validations you use in the application:

CREATE TRIGGER PEDIDO_BI0 FOR PEDIDO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if ((SELECT GEN_ID(COD_PEDIDO, 0) AS ID FROM RDB$DATABASE) = 1000) then
    EXECUTE STATEMENT('SET GENERATOR COD_PEDIDO TO 0');

    new.PE_CODIGO = 'ABC' || (SELECT GEN_ID(COD_PEDIDO, 1) AS ID FROM RDB$DATABASE);
end

2nd - You said you use the order PK to form the order items PK, so try this way:

procedure MetodoInsertPedido;
begin
  {Vai inserir o pedido, sem informar a PK, pois a trigger irá tratá-la e após a inserção o RETURNING da query irá retornar a PK gerada na trigger}
  qryInsert.SQL.CommandText := 'insert into pedido(data, valor) values (current_date, 10.50) returning id'; 
  qryInsert.Open; {Sim use o open}

  {Aqui você pega o valor da PK que a trigger gerou}
  IdPedido = qryInsert.FieldByName('id').AsString;

  [...insert itens do pedido...]
end;

Understanding

1º - The Trigger will be fired before happen the insertion of the order in the table and then you can treat your PK directly on Trigger, passing to it the values you need

2º - When using the returning and run the Insert using Open() of the query things happened like this:
• Runs script from Insert
• Fires Trigger, which generates the id and already arrow it to the record
• After execution the id for the query is returned

You commented something about access competition, with this example I did I believe you will only have the competition that already exists at the time of insertion of requests.