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: 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:

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. This is how I define this type of trigger using isql (notice that the end of line terminator must be modified for isql)


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.

Another approach is to query the generator and place that value in a hidden field. 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. To simply read the current value, I use 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). 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

which points to


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / Databases / Firebird / Generators.html