Updating generator value issue

Question

I'm currently working on modifying a Firebird v. 1.5 database.

The database structure will be modified running queries from a delphi application using interbase components, the problem I'm facing is that I need to run a lot of queries, some of which include creating generators and updating the generator value, the problem is that I need to achieve this in as few queries as possible, but it seems(at least to me) that this is not really possible, 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've created a generator, now I need to set it's value at the current max id from table TABLENAME, like so:

/* 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 the generator
get the max id
update the generator value

and repeat process for every table?

I also expected that

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

would be a workaround to get the max id's from every table in one command, but it doesn't.

I've interested names of delphi components. TIBDatabase? TIBQuery? TIBDataSet? — rstrelba, Dec 13 '11 at 18:00
well, simplest and very fast solution is to use one generator for all tables ;-) — rstrelba, Dec 14 '11 at 01:30
it's not possible(at least I think it's not), all tables on which I need a generator, have different number of records, therefore the last index in TABLE_1 could be 200, while on TABLE_2 is 1,200, also, the code "logic" is enforcing a generator per table similar to GEN_TABLENAME, you see, if I change that, it will break a lot of code. — , Dec 14 '11 at 09:05

yonojoy · Answer 1 · 2019-01-01T20:21:09.977

8

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.

edited Jan 01 '19 at 20:21

answered May 03 '16 at 12:43

yonojoy

5,486
1
31
60

If you know the generator is 0, this is even more simple:`SELECT GEN_ID( GEN_TABLENAME, (SELECT MAX(ID) FROM TABLENAME)) FROM RDB$DATABASE;` – yonojoy May 03 '16 at 12:44

score 8 · Accepted Answer · answered Dec 13 '11 at 10:42

8

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

answered Dec 13 '11 at 10:42

ain

22,394
3
54
74

+1 thank you ain, I can't upgrade, because the code base it pretty big and the time to test all functionality is limited, therefore upgrade is out of the question. I was hoping there was a more elegant solution ): – Dec 13 '11 at 10:55
I think I gave enough time to this question, your answer is more close to my question, unfortunately I have to go with the "longer way" of solving the issue, meaning multiple queries..., thank you for your answer (: – Dec 14 '11 at 14:46

score 5 · Answer 3 · answered Dec 13 '11 at 10:49

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.

+1 thank you TOndrej, you were right, GEN_TABLENAME is the convention, but I prefer a simpler solution — , Dec 13 '11 at 10:54

Updating generator value issue

3 Answers3

Linked