Updating generator value issue

Statement

SET GENERATOR GEN_TABLENAME TO (SELECT MAX(ID) FROM TABLENAME);

mixes DDL (SET GENERATOR) and DML (SELECT), AFAIK this is not generally supported and Firebird doesn't support it for sure.

If you can upgrade to the latest version of Firebird then you could use EXECUTE BLOCK and / or EXECUTE STATEMENT to do it all "in one statement" and server side, but with Firebird 1.5 you have to settle for the long way (one statement to get the current max, then another one update the generator).

With the following trick you can set the generator value to the maximum ID value of a table with one SQL statement in Firebird:

SELECT GEN_ID( GEN_TABLENAME, 
  (SELECT MAX(ID) FROM TABLENAME) - GEN_ID(GEN_TABLENAME, 0)) FROM RDB$DATABASE;

That works, because GEN_ID( <GeneratorName>, <increment>) gets the generator value and increments it by <increment>. This should work in Firebird 1.5 as well as in newer versions.

You could create a stored procedure and call it from Delphi:

create procedure update_generators
as
  declare variable max_id integer;
  declare variable table_name char(31);
  declare variable generator_name char(31);
begin
  /* assuming generator naming convention GEN_XXX -> table name XXX */
  for select
    trim(g.rdb$generator_name),
    substring(trim(g.rdb$generator_name) from 5)
  from rdb$generators g
  where (coalesce(g.rdb$system_flag, 0) = 0)
  into
    :generator_name,
    :table_name
  do
  begin
    /* assuming that the field name is always ID */
    execute statement 'select max(id) from ' || :table_name into :max_id;
    execute statement 'set generator ' || :generator_name || ' to ' || :max_id;
  end
end^

It looks like execute statement is supported by Firebird 1.5 already. In Firebird 2.0 and later, you could also wrap the code in a execute block and avoid creating a stored procedure.