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: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 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
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); ENDThis 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
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 sectionIt 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
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).
In the Interbase 5 Data Definition Guide, see Chapter 12 - Working with Generators
Start / Programs / Interbase / Documentation / Data Definition Guidewhich points to
C:\Program Files\InterBase Corp\InterBase\doc\DATADEF\DATADEF.PDF