SQL statements for generators

Statement overview
Use of generator statements

Statement overview

The name of a generator must be a usual DB meta-identifier: 31 chars maximum, no special characters except the underscore “_” (unless you use quoted identifiers). The SQL commands and statements that apply to generators are listed below. Their use will be discussed in some detail in the section Use of generator statements.

DDL (Data Definition Language) statements:

CREATE GENERATOR <name>;
SET GENERATOR <name> TO <value>;
DROP GENERATOR <name>;

DML (Data Manipulation Language) statements in client SQL:

SELECT GEN_ID( <GeneratorName>, <increment> ) FROM RDB$DATABASE;

DML statements in PSQL (Procedural SQL, available in stored procedures and triggers):

<intvar> = GEN_ID( <GeneratorName>, <increment> );

Firebird 2 recommended sytax

Although the traditional syntax is still fully supported in Firebird 2, these are the recommended DDL equivalents:

CREATE SEQUENCE <name>;
ALTER SEQUENCE <name> RESTART WITH <value>;
DROP SEQUENCE <name>;

And for the DML statements:

SELECT NEXT VALUE FOR <SequenceName> FROM RDB$DATABASE;

<intvar> = NEXT VALUE FOR <SequenceName>;

Currently the new syntax does not support an increment other than 1. This limitation will be lifted in a future version. In the meantime, use GEN_ID if you need to apply another increment value.

Use of generator statements

The availability of statements and functions depends on whether you use them in:

Client SQL – The language you use when you, as a client, talk to a Firebird server.
PSQL – The server-side programming language used in Firebird stored procedures and triggers.

Creating a generator (“Insert”)

Client SQL

CREATE GENERATOR <GeneratorName>;

Preferred for Firebird 2 and up:

CREATE SEQUENCE <SequenceName>;

PSQL

Not possible. Since you cannot change database metadata inside SPs or triggers, you cannot create generators there either.

Note

In FB 1.5 and up, you can circumvent this limitation with the EXECUTE STATEMENT feature.

Getting the current value (“Select”)

Client SQL

SELECT GEN_ID( <GeneratorName>, 0 ) FROM RDB$DATABASE;

This syntax is still the only option in Firebird 2.

Note

In Firebird's command-line tool isql there are two additional commands for retrieving current generator values:

SHOW GENERATOR <GeneratorName>;
SHOW GENERATORS;

The first reports the current value of the named generator. The second does the same for all non-system generators in the database.

The preferred Firebird 2 equivalents are, as you could guess:

SHOW SEQUENCE <SequenceName>;
SHOW SEQUENCES;

Please notice again that these SHOW... commands are only available in the Firebird isql tool. Unlike GEN_ID, you can't use them from within other clients (unless these clients are isql frontends).

PSQL

<intvar> = GEN_ID( <GeneratorName>, 0 );

Firebird 2: same syntax.

Generating the next value (“Update” + “Select”)

Just like getting the current value, this is done with GEN_ID, but now you use an increment value of 1. Firebird will:

get the current generator value;
increment it by 1;
return the incremented value.

Client SQL

SELECT GEN_ID( <GeneratorName>, 1 ) FROM RDB$DATABASE;

The new syntax, which is preferred for Firebird 2, is entirely different:

SELECT NEXT VALUE FOR <SequenceName> FROM RDB$DATABASE;

PSQL

<intvar> = GEN_ID( <GeneratorName>, 1 );

Preferred for Firebird 2 and up:

<intvar> = NEXT VALUE FOR <SequenceName>;

Setting a generator directly to a certain value (“Update”)

Client SQL

SET GENERATOR <GeneratorName> TO <NewValue>;

This is useful to preset generators to a value other than 0 (which is the default value after you created it) in e.g. a script to create the database. Just like CREATE GENERATOR, this is a DDL (not DML) statement.

Preferred syntax for Firebird 2 and up:

ALTER SEQUENCE <SequenceName> RESTART WITH <NewValue>;

PSQL

GEN_ID( <GeneratorName>, <NewValue> - GEN_ID( <GeneratorName>, 0 ) );

Warning

This is more of a dirty little trick to do what you normally cannot and should not do in SPs and triggers: setting generators. They are for getting, not setting values.

Dropping a generator (“Delete”)

Client SQL

DROP GENERATOR <GeneratorName>;

Preferred for Firebird 2 and up:

DROP SEQUENCE <SequenceName>;

PSQL

Not possible, unless... (Same explanation as with Create: you can't – or rather, shouldn't – change metadata in PSQL.)

Dropping a generator does not free the space it occupied for use by a new generator. In practice this rarely hurts, because most databases don't have the tens of thousands of generators that Firebird allows, so there's bound to be room for more anyway. But if your database does risk to hit the 32767 ceiling, you can free up dead generator space by performing a backup-restore cycle. This will neatly pack the RDB$GENERATORS table, re-assigning a contiguous series of IDs. Depending on the situation, the restored database may also need less pages for the generator values.

Dropping generators in old IB and Firebird versions

InterBase 6 and earlier, as well as early pre-1.0 Firebird versions, do not have a DROP GENERATOR command. The only way to drop a generator in these versions is:

DELETE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = '<GeneratorName>';

...followed by a backup and restore.

In these versions, with the maximum number of generators typically a couple of hundred, it is much more likely that the need will arise to reuse space from deleted generators.