0

I needed a way to generate unique sequences of numbers so I followed the following simple approach that works fine:
I used a table that has the next value for each sequence.
I get the value increment it by one, store it and use it in my application code.
Problem: I don't know how can I reset this generator.

As an example assume that a part of the program runs and in a loop and at the beginning of a loop the sequence is 102012 and after the loop it is 1045000.
Let's say that my program finishes and then for some reason I need to reset the sequence back to 102012 so that if the program runs again it can generate the same sequence.

The only thing I could think of is to store in a table dates with the sequence at that point.
But my concern is that I would end up with a big table that would have more information than needed.
I was wondering is there a better approach for this? I assume this problem is not rare.

Jim
  • 18,826
  • 34
  • 135
  • 254

2 Answers2

0

According to your description, you have a table - I'll call it 'counter' - with one field in it, which I'll call 'current'. In order to reset the value, you have to use an update query

update counter
set current = 102012

I have concerns about your use of this table, but as you didn't ask about referential integrity and other such topics, I won't comment.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • But how do I know which value to reset it to? I will like to automate this and not try to figure out some value from the logs – Jim Mar 21 '14 at 12:21
  • @Jim: How are we supposed to know? You asked how to reset the value; I showed you how. Presumably you know what the value should be. – No'am Newman Mar 21 '14 at 13:45
0

for some reason I need to reset the sequence

What reason? And why did you start using a sequence in the first place?

Consider this:

  • If multiple instances of your application are concurrently accessing the database, resetting the sequence pretty much defeats its purpose. If it doesn't, then you probably didn't need a sequence in the first place.
  • If only one application instance is accessing the database, then you could have generated the numbers directly in the application code, and restarted the generation at will there.

My point is that if you are using the sequence the way it was meant to be used, you shouldn't normally need to reset it. So my guess would be that you probably need something else, not a sequence. The answers to the questions above would help indicate what.

That being said, there are DBMS-specific ways to reset a sequence, usually through ALTER SEQUENCE. What is your DBMS?

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • The reason to reset the sequence is because I may get a failure in some other part of the system and I need to rerun the program and use the same sequence again. This is rare but I would like to be able to do it. I am using MySQL – Jim Mar 21 '14 at 12:02
  • @Jim OK, so you are not talking about [database sequence](http://stackoverflow.com/a/1649126/533120), you are using that term for something else. If you implemented your "sequence" by interlocking updates to a table, it should be simple enough to just UPDATE the table to the staring value. Please provide more details about your implementation. – Branko Dimitrijevic Mar 21 '14 at 13:16
  • `It should be simple enough to just UPDATE the table to the staring value.` That is my question. How to store/find the starting value – Jim Mar 21 '14 at 15:54
  • @Jim Sorry, but I don't understand the question. What have you tried so far and where exactly is the problem? – Branko Dimitrijevic Mar 21 '14 at 23:09