How to set initial generator value?

Question

I need to initialise a new Firebird generator / sequence to the max primary key value of an existing, 'old' table. I tried the following but it is not working, I get the error "Token unknown - line 6, column 8 select". I cannot do this manually as it must be executed on many different DBs. I am using Firebird 2.5.1.

According to http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-execblock.html this should work - what am I doing wrong?

set term #;  
execute block  
as  
declare i int = 0;  
begin  
   i = select max(ID) from OrganizationType_OLU;  
    alter sequence OrganizationType_OLU restart with :i;  
end  
#  
set term ;#

Mark Rotteveel · Answer 1 · 2020-12-31T14:11:42.890

In general you can use ALTER SEQUENCE:

ALTER SEQUENCE sequence-name RESTART WITH <newval>

Or the legacy option SET GENERATOR

SET GENERATOR generator-name TO <new-value>

However you want to do this from an EXECUTE BLOCK and you can't as executing DDL from PSQL code isn't allowed in Firebird. So I guess the answer of rstrelba is probably the only option available.

Be aware though that sequences are outside of transaction control in Firebird (they are atomic), so make sure you only run it if you are the only active transaction, otherwise you might reset the sequence to an invalid value.

I strongly suggest to make sure the ID for OrganizationType_OLU is always generated by sequence, and never allow user specified values for these columns. This ensures that the sequence value is always valid (ie: not too low, causing primary key constraint violations).

score 3 · Accepted Answer · edited Jul 21 '16 at 08:29

3

set term #;
execute block    
as  
declare i int = 0;    
declare temp int = 0;
begin  
  i = (select max(id) from items);  
  temp = gen_id(GEN_ITEMS_ID,-(gen_id(GEN_ITEMS_ID,0))); ---set some_gen to 0  
  temp = gen_id(GEN_ITEMS_ID,:i);  --- set to i
end #  
set term ;#

edited Jul 21 '16 at 08:29

Mark Rotteveel

100,966
191
140
197

answered Apr 17 '13 at 09:54

rstrelba

1,838
15
16

Thank you, this got me into the right direction. The fully working SQL looks like this:set term #; execute block as declare i int = 0; declare g int = 0; begin i = (select max(ID) from OrganizationType_OLU); g = (select gen_id(OrganizationType_OLU,-(gen_id(OrganizationType_OLU,0))) from RDB$DATABASE); g = (select gen_id(OrganizationType_OLU,(select max(ID) from OrganizationType_OLU)) from RDB$DATABASE); end # set term ;# – Daniel Putra Apr 18 '13 at 08:50

score 1 · Answer 3 · answered Apr 17 '13 at 11:41

1

The error you got (Token unknown - line 6, column 8 select) is because if you want to use select as an expression you have to enclose it into parenthesis like

i = (select max(ID) from OrganizationType_OLU);

answered Apr 17 '13 at 11:41

ain

22,394
3
54
74

Thank you, this got my line 7 going. – Daniel Putra Apr 18 '13 at 08:49

score 1 · Answer 4 · answered Feb 21 '18 at 13:32

EXECUTE BLOCK
AS
DECLARE VARIABLE fMaxID INTEGER;
BEGIN
    SELECT COALESCE(MAX(id),0)
    FROM yourtable
    INTO :fMaxID ;

    EXECUTE STATEMENT('CREATE SEQUENCE YourSequence');
    EXECUTE STATEMENT('ALTER SEQUENCE YourSequence RESTART WITH '||CAST(:fMaxID AS VARCHAR(16)));
END;

score 0 · Answer 5 · edited May 23 '17 at 12:30

0

If you do not want to use EXECUTE BLOCK or if you still use an older Firebird version (<2.0), try this:

Given, that GEN_ITEMS_ID is your generator name, you can set the initial generator value by the following DML statement:

SELECT GEN_ID(GEN_ITEMS_ID, 
  (select max(ID) from OrganizationType_OLU) 
  - GEN_ID(GEN_ITEMS_ID, 0)) FROM RDB$DATABASE;

Explanation here.

edited May 23 '17 at 12:30

Community

1
1

answered Dec 09 '16 at 15:44

yonojoy

5,486
1
31
60

How to set initial generator value?

5 Answers5

Linked