cancel
Showing results for 
Search instead for 
Did you mean: 

Obtaining the last value that was generated by a sequence

Former Member
5,623

I have a sequence generator in my database. My C# code needs to occasionally query the sequence to get the last value that was allocated by the sequence. This works fine, as long as the NextVal function is called to generate a number before the CurrVal function is called.

My problem is that I need to know that value even if NextVal hasn't been called on the connection.

I've noticed that the SYSSEQUENCE view has a column in it called resume_at. If the CurrVal function hasn't been called yet, can I return the value of this column for my sequence?

Breck_Carter
Participant

Do you mean to say "My problem is that I need to know the CURRVAL value even if NEXTVAL hasn't been called on the current connection"?

Former Member
0 Kudos

Yes, that's what I mean.

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant

Experimentation shows that SYSSEQUENCE.resume_at contains the next value to be used ONLY when the database has been restarted after shutdown.

It seems to HOLD that value fixed while the database is running, until a CHECKPOINT is taken, at which point is is incremented with SYSSEQUENCE.cache + 1, or so it seems... very strange.

I looked all through the system catalog for another candidate BIGINT, no joy. FWIW the Help's description of SYSSEQUENCE.resume_at bears little relationship with reality.

Anyway, except at the point of database start, SYSSEQUENCE.resume_at is useless... the server must maintain the current value internally while it is running and even forcing a CHECKPOINT does not save it. Perhaps SQL Anywhere has adopted ALL of Oracle's bizarre behavior...

...I'm sticking with DEFAULT AUTOINCREMENT 🙂

Former Member
0 Kudos

Thank you, Breck. I need to use a sequence generator because the value is used to set the value of columns in new & updated rows in multiple tables, in the order that rows are inserted or updated. It's complicated; DEFAULT AUTOINCREMENT just won't work for us in this case. My code is generating XML and I need to include that value in an element. What I'm doing now is setting that element's value to null if CurrVal generates an error. I was hoping there was a way to get the value without an expensive query of most of the tales in my database. Looks like this possibility is a bust.

Answers (4)

Answers (4)

Former Member

Hello TonyV,

I believe I can explain most of Breck's observations but before I go "all long winded on this thread"

. . .

it might not hurt to ask if your application is a fully embedded, single connection design, or if you can tolerate an occassional skipped value in the sequence range?

Maybe doing something like this might be helpful?

SET currval_var = (*<seq-name>*.NEXTVAL - δ); 
// where δ is your unit of increment; assumptions here of course

  {which could be done inside your exception handler for the 
   SQLE_SEQUENCE_CURRVAL_NOT_DEFINED error returned when you use CURRVAL
   http://dcx/index.html#sa160/en/saerrors/errm1363.html }

Alternatively, getting the NEXTVAL directly and doing the conversion in client side code?

Of course either of those would burn a sequence increment on you. But that may not be as critical as it appears at first blush.

If you have more than more than one connection, or use snapshot isolation, or have some other mitigating factor ... you might need to use some user defined function or external function for this ... instead of using sequences.

Much would depend on the requirements here. But let us know so we can group-think other approaches ....

let us know if this was at all helpful

Former Member
0 Kudos

As the values are bigints, we could afford to use one up every now and then. I could easily call the NEXTVAL function if an exception is thrown and just return that value without worrying about it. We don't need the values to be consecutive, just increasing and non-repeating. I'll give this some more thought.

Former Member
0 Kudos

The application uses Entity Framework 4 and connection pooling to access the database. The EF context is created, used, and freed for each operation. So the context object lives for the duration of one transaction. The actual database connection, on the other hand, lives much longer, since it is taken from and returned to the connection pool at the start and end of each operation. In addition, there are other processed running that also access the database in the same manner, so the connection that just finished servicing one request in this process may now be servicing one of the other processes.

VolkerBarth
Contributor
0 Kudos

I'd think (but don't know) that the "clean-up" process that takes place when a connection is returned to the connection pool will reset connection-specific values, such as @@identity and the any sequence's CURRVAL, so connection pooling shouldn't matter here, methinks ...

VolkerBarth
Contributor
0 Kudos

Ah, forget about my comment. It's SQL Anywhere's own connection pooling I was thinking of - but I gues you're using the .NET pooling facility, and the docs tell:

The behavior of connection pooling in your product or the API may be significantly different than SQL Anywhere connection pooling. If the behavior of connection pooling in your product or API is inappropriate for an application, SQL Anywhere connection pooling can be used and may improve the performance of some applications.

Former Member

By the way, this is what we ended up doing. The code calls CURRVAL; if that throws an exception, it calls NEXTVAL in the catch block. This works fine. Usually, the CURRVAL returns a value & no exception. Its working great.

Former Member
0 Kudos

How about using SELECT GET_IDENTITY?

Breck_Carter
Participant
0 Kudos

...because GET_IDENTITY() works for DEFAULT AUTOINCREMENT columns in the table, not SEQUENCE values which are separate from any particular table.

Perhaps your question should be "How about using DEFAULT AUTOINCREMENT?" to which I would answer "Works for me!" 🙂

VolkerBarth
Contributor
0 Kudos

@TonyV: You can't let the database generate the value itself (i.e. using the sequence as a default, such as

ID INT PRIMARY KEY DEFAULT(mySequence.nextval), ...

and then query that value after the insert?

Some more observations (that do not act as answers, I agree...):

  • The behaviour of SYSSEQUENCE.resume_at (as of Breck's tests, I haven't checked for myself) seems different than that of the according DEFAULT (GLOBAL) AUTOINCREMENT values in the system catalog (i.e. the according max_identity of the particular column). These values do get updated during a checkpoint, as Glenn has explained in this FAQ. - Therefore I had expected the situation for a sequence would be similar...

  • However, even if the value would be updated during a checkpoint, it would be worthless when there are concurrent transactions, as querying the system table could return a value that already has been used by a different connection - and to be honest, facilities like sequences and default autoincrements are meant to handle concurrent inserts automatically, so you would surely not want to "burn" these smart facilities by a "manual intervention..."... - That's my opinion, obviously.

  • With DEFAULT AUTOINCREMENT, you are neither able to query the current value before you have done a real insert on that connection - or you can call get_identity() but that will "waste" an ID value if you do not use it afterwards. Therefore I think this is not "cheaper" than to have to select a sequence's NEXTVAL() initially...

So basically I would share Nick's suggestion:

If you can afford to "waste" a sequence value now and then (note, these are BIG INTs, so really huge ranges), then I'd call NEXTVAL() first. - If not, you may let the database create these values and read them after the fact (see my initial question).

0 Kudos

This seems crude, but can you setup a separate table that stores the last used ID and then use the same logic that the sequence generator uses to query that table, increment the result and "know" the next ID before the sequence generator is called?

Former Member
0 Kudos

Well, it's a possibility, but not something we want to attempt right now. I have to talk to my boss to see what he thinks.