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.

Tachylyte

what components are you using? – Lalise

I've interested names of delphi components. TIBDatabase? TIBQuery? TIBDataSet? – Lalise

well, simplest and very fast solution is to use one generator for all tables ;-) – Lalise

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. – Tachylyte

Answer 1

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

Discreditable

+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 ): – Tachylyte

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 (: – Tachylyte

Answer 2

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, 
        COALESCE(MAX(ID), 0) - GEN_ID(GEN_TABLENAME, 0)) FROM TABLENAME;

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.

Scottiescottish

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; – Scottiescottish

It is recommended to use the format COALESCE( SELECT ... , 0 ), otherwise the result can be NULL ! Please fix your answer. – Catharina

@Catharina You are right. I have edited the question accordingly. The original statement fails with empty tables (and an existing generator value <> 0). – Scottiescottish

I have edited the statement to take the comment of @LoukOum into account. – Scottiescottish

Answer 3

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.

Melan

+1 thank you TOndrej, you were right, GEN_TABLENAME is the convention, but I prefer a simpler solution – Tachylyte

Answer 4

With old Interbase/Firebird versions, there is a trick to update generator with a SELECT :

/* first reset to 1*/
set generator my_gen to 1;
/* make your select */
select gen_id(my_gen ,max(id)) from my_table

Your generator will have a max(id)+1 value !

Atmosphere

The solution in this answer is simpler and doesn't require resetting the generator. – Rager

Thanks, And to simplify more the @MarkRotteveel link solution : SELECT GEN_ID( my_gen, MAX(id) - GEN_ID(my_gen, 0)) FROM my_table; – Atmosphere

@LoukOum You are right, this looks cleaner than my original suggestion using RDB$DATABASE. I edited my answer accordingly. – Scottiescottish