Ask Your Question
0

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

asked 2019-06-19 08:33:44 +0200

frofa gravatar image

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;
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2019-06-19 11:42:03 +0200

DrewJensen gravatar image

updated 2019-06-19 11:42:45 +0200

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.

edit flag offensive delete link more
1

answered 2019-06-19 10:17:24 +0200

Ratslinger gravatar image

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 -> https://stackoverflow.com/questions/2...

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-19 08:33:44 +0200

Seen: 63 times

Last updated: Jun 19