How to create an auto-increment ID column in a Firebird embedded database table? Errors encountered

[frofa]

These steps what I have tried so far without success. (Note: Firebird embedded does not seem to require quotes around names like HSQLDB but also seems inconsistent?) What am I doing wrong?

*1 Create a table in the a LibreOffice Firebird embedded database called “phonecalls” … (this works)

CREATE TABLE "phonebook" (
"id_pn"  INTEGER NOT NULL PRIMARY KEY,
"phone_number" VARCHAR(16),
"name" VARCHAR(128),
"date" DATE,
"note" VARCHAR(256),
"flag" VARCHAR(2)
);

*2 Create auto-increment ID generator… (seems to work, but can’t verify if the generator has been created)

CREATE GENERATOR GEN_PHONEBOOK_ID;
SET GENERATOR GEN_PHONEBOOK_ID TO 0;

*3 Create TRIGGER to auto-increment the generator… (does NOT work)

set term !! ;
CREATE TRIGGER TRIG_PHONEBOOK_AUTOINC FOR PHONECALLS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_PHONEBOOK_ID, 1);
END!!
set term ; !!

*4 Try to get auto-increment generator value… (does not work…)

SELECT GEN_ID( GEN_PHONEBOOK_ID, 0 ) FROM PHONECALLS;

[Ratslinger]

Hello,

Table set up is OK. However be aware collate sequence can only be set when table is created. There is no default. There is an outstanding bug report concerning this - #124054 of which I don’t seem to agree with. See answers in this post regarding subject → Sorting in Base with german Umlaute.

Step #2 creating & setting is OK

Step #3 has some problems - this works:

CREATE TRIGGER TRIG_PHONEBOOK_AUTOINC FOR "phonebook"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    if (new."id_pn" is null) then
    new."id_pn" = gen_id(gen_phonebook_id, 1);
END

Have only needed Set Term with Flame Robin for server. Also your table nae was incorrect. Don’t have a why but generator name needs to be lower case (even when created it was upper case).

SELECT GEN_ID( GEN_PHONEBOOK_ID, 0 ) FROM “phonebook”;

will then work (you have incorrect table name here also) after at least one record is entered otherwise a blank is returned.

To list generators - SQL run in query:

execute block
returns (
    out_name char(31),
    out_value bigint)
as
begin
    for select rdb$generator_name from rdb$generators where rdb$system_flag is distinct from 1 into out_name do
    begin
        execute statement 'select gen_id(' || out_name || ', 0) from rdb$database' into out_value;
        suspend;
    end
end

Above obtained from → Get list of all sequences and its values in Firebird SQL - Stack Overflow

[drewjensen]

With the latest LibreOffice Firebird 3.0 is used, so you can do this much more easily using “identity”.

For instance your first table would be created with:

CREATE TABLE "phonebook" (
"id_pn"  INTEGER generated by default as identity primary key,
"phone_number" VARCHAR(16),
"name" VARCHAR(128),
"date" DATE,
"note" VARCHAR(256),
"flag" VARCHAR(2)
);

With that the database engine will take care of creating the nescessary generators and triggers automatically.