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 ;# 

Answer 1

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).