RE: [firebird-support] Set generator in execute block

[Svein Erling Tysvær]

>When trying to execute the following it produces an error:
>
>SET TERM ^ ;
>
>EXECUTE BLOCK
>AS
>  DECLARE VARIABLE vMaxID BIGINT;
>BEGIN
>  SELECT MAX(ID) FROM A_TABLE
>  INTO :vMaxID;
>
>  SET GENERATOR GEN_A_TABLE_ID TO :vMaxID;
>END
>
>SET TERM ; ^
>
>the error being
>
>Engine Code    : 335544569
>Engine Message :
>Dynamic SQL Error
>SQL error code = -104
>Token unknown - line 10, column 3
>SET
>
>I have tried wrapping it in an EXECUTE STATEMENT block to the same effect.
>
>Is setting a generator value in an execute block not supported or am I
>doing something obvious wrong?

Why not:

EXECUTE BLOCK
AS
  DECLARE VARIABLE vMaxID INTEGER;
  DECLARE VARIABLE Dummy INTEGER;
BEGIN
  SELECT MAX(PK_TABLE_B) FROM TABLE_B
  INTO :vMaxID ;
  DUMMY = GEN_ID(TABLE_B_GEN, :vMaxID - GEN_ID(TABLE_B_GEN, 
-GEN_ID(TABLE_B_GEN,0)));
END ^

The inner GEN_ID will set the generator to 0, the outer to vMaxID.

HTH,
Set