Search code examples
firebirdauto-incrementfirebird1.5

Updating generator value issue

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.

Solution

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