Working with generators

Some tips from this document allow you to do things that are not directly supported in stored procedures (like SET GENERATOR command).

How to read generator's value in SP
How to set generator in SP
How to create generator in SP
How to drop generator

How to read generator's value in SP

Many people use 1-row table (rdb$database) to retrieve generator's value in SP

SELECT GEN_ID(g,1) FROM rdb$database INTO :x;

While using rdb$database table can be useful if client program needs to read generator (because you can't "execute" standalone GEN_ID() expression from client), it is absolutely unnecessary in stored procedure. Expressions can be assigned to SP variables/parameters directly by assignment statement. E.g. procedure to retrieve next value from generator can look like:

CREATE PROCEDURE ReadGen RETURNS (ret INTEGER) AS
BEGIN
  ret = GEN_ID(MyGen, 1);
END;

How to set generator in SP

Because command SET GENERATOR ... TO ...; is not supported inside stored procedures, the only way is to read current value in one GEN_ID call and use it to adjust value by another one. (But be careful in multiuser environment because these two GEN_ID calls are not guaranteed to be executed in one atomic operation !)

CREATE PROCEDURE SetGen (val INTEGER) AS
BEGIN
  val = GEN_ID (MyGen, val - GEN_ID (MyGen,0) );
END

How to create generator in SP

Of course InterBase has command CREATE GENERATOR, but because it is DDL command, you can't use it in stored procedure. But thanks to system of active tables, you can create generator by inserting it's name directly into system table:

  INSERT INTO RDB$GENERATORS (RDB$GENERATOR_NAME)
                      VALUES (UPPER('MYGEN'));

The new generator is available immediately, even before commit (but if you rollback, the generator-name/generator-id will be lost). Do not forget that old IB versions have bug that prevents you from creating and using too many generators. Also note that direct updating of system tables is not recommended practice.

How to drop generator

Generators can be dropped by DROP GENERATOR <generator_name>; command (which was implemented in Firebird 1).

In older InterBase versions there is no Drop Generator command, so the only possibility is to delete definition of the generator from system table:

  DELETE FROM RDB$GENERATORS
        WHERE RDB$GENERATOR_NAME='MYGEN';

But this method has some disadvantages:

InterBase does not properly track dependencies for generators, which means that it will allow you to delete generator even if it is used by stored procedure or trigger. Fortunately, simple new creating of missing generator (and setting its initial value) will remedy this problem.
Another problem is that each generator consists of two parts:
- record in RDB$GENERATORS table
- generator variable/counter itself, that is stored on special database page
RDB$GENERATOR_ID field in RDB$GENERATORS table is in fact index to array of generators on generator page. When you delete generator from system table, then the space assigned to it on generator page (and its generator_id) will remain unused; it means that newly created generator will always have new rdb$generator_id value instead of reusing free one. So simple deleting generator is worth only if you plan to backup/restore the database, or if you want to get rid of the generator and do not plan to create new one. In case you need the generator of different name, just rename old, unused one:
```
  UPDATE RDB$GENERATORS
     SET RDB$GENERATOR_NAME='NEW_GEN'
   WHERE RDB$GENERATOR_NAME='OLD_GEN';
  
```