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

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:
Copyright © 2001, 2005 Ivan Prenosil