Home / Questions / 445476
Accepted
Franco Torres
Asked: 2022-04-20 12:34:47 +0800 CST 2022-04-20 12:34:47 +0800 CST

How to update the value of a sequence in Firebird 2.5?

  • 772

I am trying to update the value of a generator id. Trying to update the stream using a script throws me an error.

This is the script I'm trying to run

set term ^ ;
execute block
as
    declare variable codigo integer ;
begin
    select first 1 (peo2.codigo+1)
    from presupuestosestadosoportunidad peo2
    order by peo2.codigo desc
    into :codigo;

    execute statement
        'ALTER SEQUENCE GEN_PRESUPESTADOSOPORTUNIDAD RESTART WITH ' + cast(codigo as varchar(10))+ ';';
end ^
commit work ^
set term ; ^

This is the error that throws me:

can't format message 13:198 -- message file C:\Windows\firebird.msg not found. arithmetic exception, numeric overflow, or string truncation. string right truncation. At block line: 12, col: 1.

I can't understand why I get this error. My understanding is that to update a sequence, the value passed in has to be a constant. This is why I convert the code value to varchar. The value that the id should be having is approximately 453788.

sql

1 Answers

  1. Best Answer

    I suggest that you make sure what is the value that you are getting in the query that you do.

    I have adapted the code to run on firebird 3.0 (I don't have 2.5 at hand now), and when the value of the sequence exceeds 10 digits, I get an error similar to yours (only I do have the message file).

    My code is:

    set term ^;
execute block
as
  declare variable codigo integer;
  declare variable sql varchar(10000);
begin
  codigo = 1234567890;
  sql = 'ALTER SEQUENCE GEN_PRESUPESTADOSOPORTUNIDAD RESTART WITH ' || cast(codigo as varchar(50)) || ';';
  execute statement sql;
end 
^
set term ;^

    This runs without a problem, however if I change to:

    codigo = 12345678901;

    I get this:

    Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
-At block line: 6, col: 3

    The funny thing is that line 6 is not the execution of the alter sequence, but the assignment of the value to the variable. If I change the data type to bigint, the error again disappears, in fact, the following code runs without problems.

    set term ^;

execute block
as
  declare variable codigo bigint;
  declare variable sql varchar(10000);
begin
  codigo = 123456789012345678;
  sql = 'ALTER SEQUENCE GEN_PRESUPESTADOSOPORTUNIDAD RESTART WITH ' || cast(codigo as varchar(50)) || ';';
  execute statement sql;
end 
^

set term ;^

    One last thing: it strikes me that you do a select first 1 xxx order by xxx desc, I suspect that the plan for this equivalent version could be more efficient:

    select max(talcosa) + 1 from tabla

    As long as there is an index on the column talcosa.

    • 1