1

I am trying to set a generator with a value that is in some table, I have already seen this question How to set initial generator value? and did what they suggested but I don't know where am I going wrong here.

set term #   
execute block    
as  
declare i int = 0;    
begin  
  i = (select max(some_col) from Table);  
  gen_id(some_gen,-(gen_id(some_gen,0))); ---set some_gen to 0  
  gen_id(some_gen,:i);  --- set to i
end #  
set term ;#
Community
  • 1
  • 1
Alec
  • 569
  • 2
  • 17
  • 27
  • Please describe what you are trying to do, what you expect and what actually happens. Note that meddling with sequences (generators) is usually a sign of a bad design, or abusing them for things they shouldn't be used for. – Mark Rotteveel Jul 21 '16 at 08:07
  • I am just trying to set the generator value to the max value of some column in a table, same as this question http://stackoverflow.com/questions/16056556/how-to-set-initial-generator-value/16056992#16056992 just that I am getting an SQL error code = -104 Token unknown - line 1, column 37 ( – Alec Jul 21 '16 at 08:20

2 Answers2

2

If you want to use "execute block", you may use something like :

execute block    
as  
declare i int = 0;    
begin  
  i = (select max(some_col) from some_table);
  execute statement ('set generator MY_GENERATOR to ' || :i);
end
Val Marinov
  • 2,705
  • 17
  • 22
2

The problem with your code is that you can't execute gen_id in isolation; the parser expects gen_id (or more precisely: a function call) only in a place where you can have a value (eg in a statement or an assignment). You need to assign its return value to a parameter, for example:

set term #;
execute block    
as  
declare i int = 0;    
declare temp int = 0;
begin  
  i = (select max(id) from items);  
  temp = gen_id(GEN_ITEMS_ID, -(gen_id(GEN_ITEMS_ID, 0))); ---set some_gen to 0  
  temp = gen_id(GEN_ITEMS_ID, :i);  --- set to i
end #  
set term ;#

Please be aware that changing sequences like this is 'risky': if there are any interleaving actions using this same sequence, you might not actually get the result you expected (the sequence might end up at a different value than i and you might get duplicate key errors when another transaction uses the sequence after you subtract the current value (set to 0) and before you add i.

As also noted in the comments, you can also replace your code with:

set term #;
execute block    
as  
declare i int = 0;    
declare temp int = 0;
begin  
  i = (select max(id) from items);  
  temp = gen_id(GEN_ITEMS_ID, :i - gen_id(GEN_ITEMS_ID, 0));
end #  
set term ;#

Doing it in one statement will reduce the risk of interleaving operations (although it will not remove it entirely).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • I have also edited the [answer on the other question](http://stackoverflow.com/questions/16056556/how-to-set-initial-generator-value/16056992#16056992), but note that the actual solution was already mentioned in the comment of Daniel Putra on that answer. – Mark Rotteveel Jul 21 '16 at 08:30
  • Is it necessary first to set the generator to 0 and then to some value? – Val Marinov Jul 21 '16 at 08:45
  • @ValMarinov Not really, you can also do `gen_id(GEN_ITEMS_ID, :i - (gen_id(GEN_ITEMS_ID, 0)))`, it reduces the risk of interleaving actions. – Mark Rotteveel Jul 21 '16 at 08:53
  • @MarkRotteveel Now is better. +1 for this. Although I would prefer to do it without using the function "gen_id" – Val Marinov Jul 21 '16 at 09:11
  • @ValMarinov I know, but `gen_id` is the only thing flexible enough to do this without having to resort to dynamic sql and `execute statement` – Mark Rotteveel Jul 21 '16 at 09:12
  • @MarkRotteveel just for interest sake, is there an alternate solution for achieving this without using the Execute statement? – Alec Jul 21 '16 at 09:26
  • @Fero68 No, but then again: you really shouldn't be doing this anyway. Don't be afraid of gaps in your ids. – Mark Rotteveel Jul 21 '16 at 09:27