Fetching the last sequence value allocated by any database connection
All 2 messages in topic - view as tree
From: Doug Lawry - view profile
Date: Sun, Dec 3 2006 2:23 pm
Email: "Doug Lawry"


Does anyone know how to get the current value of a sequence without incrementing
it? If you have an existing sequence created with

CREATE SEQUENCE pk_seq


and in a new database connection you try


SELECT pk_seq.CURRVAL FROM systables WHERE tabid = 1


you get:


"-8315 Sequence (%s) CURRVAL is not yet defined in this session.


You cannot get the session CURRVAL because the sequence object has not been
initialized in this session. The session CURRVAL is the value generated
on a previous call to the sequence object's NEXTVAL.


If the sequence object has been renamed using RENAME SEQUENCE, you will
need to reinitialize the sequence object in your session.


Run a NEXTVAL first to define a CURRVAL for the sequence object in your
session."


If you use NEXTVAL as instructed, it does of course increment the sequence,
which I don't want, and using it within BEGIN WORK and ROLLBACK WORK has no
effect as documented. I also can't see anywhere obvious where the value might be
held in the system tables.


Any ideas?


Regards,
Doug Lawry
www.douglawry.webhop.org


Reply » Rate this post: Text for clearing space


From: Art S. Kagel - view profile
Date: Mon, Dec 4 2006 9:51 pm
Email: "Art S. Kagel"




- Hide quoted text -
- Show quoted text -

Doug Lawry wrote:
> Does anyone know how to get the current value of a sequence without
> incrementing it? If you have an existing sequence created with

> CREATE SEQUENCE pk_seq


> and in a new database connection you try


> SELECT pk_seq.CURRVAL FROM systables WHERE tabid = 1


> you get:


> "-8315 Sequence (%s) CURRVAL is not yet defined in this session.


> You cannot get the session CURRVAL because the sequence object has not been
> initialized in this session. The session CURRVAL is the value generated
> on a previous call to the sequence object's NEXTVAL.



Yeah, I wrestled with that one when I added sequence support to myschema.
You cannot get the last sequence value without incrementing it. It's not
stored anywhere that's accessible and as you note CURRVAL requires that you
have called NEXTVAL at least once in the session.

I've got a note to myself to poke around to find where it's hidden, but
haven't had time.


Art S. Kagel



- Hide quoted text -
- Show quoted text -

> If the sequence object has been renamed using RENAME SEQUENCE, you will
> need to reinitialize the sequence object in your session.

> Run a NEXTVAL first to define a CURRVAL for the sequence object in your
> session."


> If you use NEXTVAL as instructed, it does of course increment the
> sequence, which I don't want, and using it within BEGIN WORK and
> ROLLBACK WORK has no effect as documented. I also can't see anywhere
> obvious where the value might be held in the system tables.


> Any ideas?


> Regards,
> Doug Lawry
> www.douglawry.webhop.org



Reply » Rate this post: Text for clearing space


End of messages