Jump to content
Alberto Fornés

Firebird transactions with Firedac

Recommended Posts

Hello, I have a Firebird table (ORDEN) where I need to have a unique numbering in a field depending on the date. In the before insert of the table I define a trigger that basically does this:

 

SELECT MAX(NUMBER) FROM ORDEN WHERE FECHAINT >= :FECHA INTO :NUM;
IF (:NUM IS NULL) THEN NUM = 0;
NUM = :NUM + 1;
NEW.NUMBER = :NUM;

Sometimes numbers are repeated in this table, so I deduce that something is wrong in the handling of transactions to cause this error. I work with Firedac, what are the properties of the connection or the write and read transactions supposed to be to avoid this problem?, thanks

Share this post


Link to post

I don't think logic anymore. What happens if you have the same dates? Are the dates changing?
The date itself is a unique number. Isn't that enough for you?

  • Like 1

Share this post


Link to post
2 hours ago, Alberto Fornés said:

I deduce that something is wrong in the handling of transactions to cause this error.

Wrong conclusion. What is more likely? That there is something wrong with transactions or that there is something wrong with what you're doing?

 

Your method is flawed unless you can guarantee that there will never be more than one transaction active against the database at a time.

Consider this:

  • A: Start transaction
  • A: select max(NUMBER) returns 1
  • B: Start transaction
  • A: Post new row with NUMBER=2
  • A: Commit
  • B: select max(NUMBER) returns [...drumroll..] 1
  • B: Post new row with NUMBER=2
  • B: Commit

Like Hans suggested, use a generator. That's what they're there for.

  • Like 2
  • Thanks 1

Share this post


Link to post
3 hours ago, Hans J. Ellingsgaard said:

Is there a reason why you can‘t use generators?

Really is that there was no reason not to use it, finally it is what I have done and as Anders suggested it solves the problem. Thanks

3 hours ago, Stano said:

I don't think logic anymore. What happens if you have the same dates? Are the dates changing?
The date itself is a unique number. Isn't that enough for you?

In this case the date did not work because I need also assign a code from that number.

2 hours ago, Anders Melander said:

Wrong conclusion. What is more likely? That there is something wrong with transactions or that there is something wrong with what you're doing?

 

Your method is flawed unless you can guarantee that there will never be more than one transaction active against the database at a time.

Consider this:

  • A: Start transaction
  • A: select max(NUMBER) returns 1
  • B: Start transaction
  • A: Post new row with NUMBER=2
  • A: Commit
  • B: select max(NUMBER) returns [...drumroll..] 1
  • B: Post new row with NUMBER=2
  • B: Commit

Like Hans suggested, use a generator. That's what they're there for.

 Thanks Anders, now works without repetitions using a generator.

  • Like 1

Share this post


Link to post
SET SQL DIALECT 3;

CREATE GENERATOR GEN_RECORD_NUMBER;

SET TERM ^ ;

CREATE OR ALTER TRIGGER ORDEN_BI FOR ORDEN
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if ((new.number is null) or (new.number = 0))  then
    new.number = gen_id(GEN_RECORD_NUMBER,1);
  if ((new.guid is null) or (trim(new.guid) = '') ) then
    new.guid = '{' || uuid_to_char( gen_uuid() ) || '}';
end
^
SET TERM ; ^

I also use a GUID column as a unique field

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×