Geek Answers Handbook

Generator Value Update

I am currently working on modifying the Firebird v database. 1.5.

The database structure will be changed to execute queries from the delphi application using interbase components, the problem I am facing is that I need to run many queries, some of which include creating generators and updating the generator value, the problem is that I need to achieve this as few requests as possible, but it seems to me (at least to me) that it’s really impossible, what I'm trying to do is the following:

 /* this command creates a generator to be used for table TABLENAME */
CREATE GENERATOR GEN_TABLENAME;

So, I created a generator, now I need to set its value in the current maximum id from the TABLENAME table, for example:

/* one would expect that the following command would work, well it doesn't */
SET GENERATOR GEN_TABLENAME TO (SELECT MAX(ID) FROM TABLENAME);

Now, is there any workaround for this, or am I forced to:

  • create generator
  • get maximum id
  • update generator value

and repeat the process for each table?

I also expected

SELECT
  SELECT MAX(ID) AS ID_TABLENAME_1 FROM TABLENAME_1,
  ...
  SELECT MAX(ID) AS ID_TABLENAME_N FROM TABLENAME_N

there will be a workaround to get the maximum id from each table in one command, but this is not the case.

+5
source share
3 answers

Statement

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

mixes DDL ( SET GENERATOR) and DML ( SELECT), AFAIK is usually not supported, and Firebird does not support it for sure.

Firebird, EXECUTE BLOCK / EXECUTE STATEMENT " " , Firebird 1.5 ( , , ).

+6

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^

, execute statement Firebird 1.5. Firebird 2.0 execute block .

+4

ID SQL Firebird:

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

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

+2
source

More articles:


All Articles