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;
SET GENERATOR ... TO ...;is not supported inside stored procedures, the only way is to read current value in one
GEN_IDcall and use it to adjust value by another one. (But be careful in multiuser environment because these two
GEN_IDcalls 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
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.
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:
UPDATE RDB$GENERATORS SET RDB$GENERATOR_NAME='NEW_GEN' WHERE RDB$GENERATOR_NAME='OLD_GEN';