Databases - Firebird Generators

Generators provide a multi-user safe, thread safe way to produce a unique value. These are normally used to produce unique row identifiers in a way similar to the MS Access autoincrement field.

According to A Beginner's Guide to Firebird Generators, when inserting 1000 rows per second, a 64-bit (Dialect 3) generator would take around 300 million years (!) before it rolls over ... but a 32-bit (Dialect 1) generator would roll over after about 25 DAYS (!!!) .

Of course, at once a minute, a 32-bit generator rolls over in about 4,000 years.

Creating an auto-incrementing primary key

The following is directly from the Firebird FAQ.

In Firebird, you achieve an auto-incrementing PK using a generator and a BEFORE INSERT trigger:
CREATE GENERATOR GEN_PK_ATABLE;
COMMIT;
Define the column, e.g., ATABLE_ID, as BIGINT or INTEGER and yes, the NOT NULL constraint is mandatory for a primary key.
The trigger for automatic population of the key is this:
CREATE TRIGGER BI_ATABLE FOR ATABLE
ACTIVE BEFORE INSERT
AS
BEGIN
  IF(NEW.ATABLE_ID IS NULL) THEN 
    NEW.ATABLE_ID = GEN_ID(GEN_PK_ATABLE, 1);
END

This is a somewhat "better" BEFORE INSERT trigger - it allows you to set the ID to zero (so that it is not null) and still automatically generate a unique value. This significantly simplifies using some of the Delphi components.

CREATE TRIGGER BI_ATABLE FOR ATABLE
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.ATABLE_ID IS NULL) or (NEW.ATABLE_ID = 0) THEN 
    NEW.ATABLE_ID = GEN_ID(GEN_PK_ATABLE, 1);
END

This is how I define this type of trigger using isql (notice that the end of line terminator must be modified for isql)

CREATE GENERATOR "GEN_SOPs_tbl_SOP_ID";
   SET GENERATOR "GEN_SOPs_tbl_SOP_ID" TO 22;

SET TERM ^;
CREATE TRIGGER "TRIG_SOPs_tbl_BI" FOR "SOPs_tbl" BEFORE INSERT
AS BEGIN
  IF((NEW."SOP_ID" IS NULL) or (NEW."SOP_ID" = 0)) THEN NEW."SOP_ID" = GEN_ID("GEN_SOPs_tbl_SOP_ID",1);
END ^
SET TERM ;^

Delphi 5 Issues

If you attach form components to a table that has a generator, you will not be able to insert records if the autoincrement field is left blank - your application will complain that the autoincrement field can not be null.

One solution is to use an insert query to save new records. In this example, the "ID" field, the autoincrementing primary key, is not part of the insert query. As a result, the Before Insert trigger fires and creates a unique value.

  s :=
     'INSERT into "Experiment_Details_tbl" (' +
     '"Experiment_ID", "Element_Type_ID", "Element_Table", "Element_Record", ' +
     '"User_ID", "Date_Time_Created")' +
     ' Values (''' +
     Experiment_ID   + ''', ''' +
     Element_Type_ID + ''', ''' +
     Element_Table   + ''', ''' +
     Element_Record  + ''', ''' +
     User_ID         + ''', ''' +
     d               + ''' ' +
     ') '
    ;

  IBQuery1.SQL.Text := s;
  IBQuery1.ExecSQL;

Another approach is to query the generator and place that value in a hidden field.

  s := ?????   see the next section

It is also possible to use a BEFORE INSERT trigger that checks for an ID that is zero (or, perhaps, less than or equal to zero), as described in the previous section.

With Delphi 6 and beyond, you can associate a generator with some of the IB components and avoid these problems ... however, this creates code that is not backward compatible. (Normally, this is not an issue ... but sometimes, it is.)

Reading a Generator in Delphi 5

Delphi 5 does not provide a method to simply read a Generator. I use this method (from my Firebird toolbox) to get the next value.

  s := BasicDataModule.Get_Generator('generator_name');

To simply read the current value, I use

  s := BasicDataModule.Get_Generator('generator_name', 0);

This is the code from my tool box. Additional code configures IBQuery_Basic (based on TIBQuery) to connect to the correct database. IBQuery_Basic is also associated with its own TIBTransaction that is not shared (this is important).

type TBasicDataModule = class(TDataModule) IBQuery_Basic: TIBQuery; IBTransaction_Query_Basic: TIBTransaction; IBDatabase_Basic: TIBDatabase; public function Get_Generator(Generator_Name: string; Increment: integer=1): string; procedure ExecuteSelectSQL(s: string); . . . end; procedure TBasicDataModule.ExecuteSelectSQL(s: string); begin IBQuery_Basic.Close; IBQuery_Basic.SQL.Text := s; IBQuery_Basic.Open; end; // ExecuteSelectSQL // Based on code from - http://www.fingerbird.de/generatorguide.htm // SELECT GEN_ID ( <GeneratorName>,0 ) FROM RDB$DATABASE function TBasicDataModule.Get_Generator (Generator_Name: string; Increment: integer=1): string; var s : string ; begin s := 'SELECT GEN_ID ( "' + Generator_Name + '", ' + IntToStr(Increment) + ' ) FROM RDB$DATABASE'; ExecuteSelectSQL(s); result := IBQuery_Basic.FieldByName('GEN_ID').AsString; end; // Get_Generator

I actually never call BasicDataModule directly, I use it as a base class and inherit new datamodules from it.

References

In the Interbase 5 Data Definition Guide, see Chapter 12 - Working with Generators

Start / Programs / Interbase / Documentation / Data Definition Guide

which points to

C:\Program Files\InterBase Corp\InterBase\doc\DATADEF\DATADEF.PDF

Author: Robert Clemenzi - clemenzi@cpcug.org