Hi,
starting with kernel version 7.3.0.18 (18 !!!) :
1. the first call for NEXTVAL in selects will not need one NEXTVAL-value for
private use unseen to the user any more.
That means for sequences with INCREMENT 1 there will be no 'hole' in the
sequence of numbers
SELECT <seqname>.NEXTVAL FROM dual ; FETCH INTO :hostvar -->
hostvar = 1
SELECT <seqname>.NEXTVAL FROM dual; FETCH INTO :hostvar -->
hostvar = 2
By now there is no bug in the sequence-numbering, although it looks like
a bug if one only
uses select with exactly one fetch afterwards to get the next
sequencenumber.
BTW: don't forget:
in case of multiple users working with the database and using
the same sequence, one session
can not be sure to get sequencenumbers without 'holes'.
==> select <sequence>.nextval from dual does NOT eat the next value of
the sequence any more, starting with
kernel version 7.3.0.18
2. <seqname>.CURRVAL will return the last NEXTVAL-value used by THIS SESSION
for this sequence.
If THIS SESSION did not use NEXTVAL for this sequence before,
<seqname>.CURRVAL will return a new errorcode.
By now <seqname>.CURRVAL returns the last NEXTVAL-value used by ANY
session.
But, please pay attention, the change is included in kernel version >=
7.3.0.18 !
We are checking what we can do to return the last used SERIAL-value.
But there will be NO change with kernel version 7.3.0.18, no chance.
We will keep you informed.
Elke
SAP Labs Berlin
-----Original Message-----
From: Gloss Mathias [mailto:[EMAIL PROTECTED]]
Sent: Montag, 29. Oktober 2001 17:38
To: [EMAIL PROTECTED]; Dittmar, Daniel
Subject: AW: Serial vs Seq
Importance: Low
Aloha,
> Von: Dittmar, Daniel[SMTP:[EMAIL PROTECTED]]
>
> Accepted as an abstract change request. It would be great if those in the
> know would supply us with the details how other databases handle this:
>
> - MySQL seems to return the value as a result of the insert. How is this
> embedded into the various programming languages?
>
the facts you find on:
http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html
Short:
- in sapdb you use serial as default, in mysql there is a attribute to the
columns named "autoincrement".
- in sapdb you only know what serial value was set if you
lock table; insert into table ....; select max(...) from table; unlock
table;
- in mysql there is an api function called mysql_insert_id() to get
the last autoincrement value of your connection (so you have to call
it immediately after inserting a value.
Elke wrote sometimes ago that sapdb works now similar with sequences
in a future version if you use sequences ( select <sequence>.currval from
dual
will give the last generated value of <secuence.nextval>, not of the
sequence
if i understood it right).
unfortunately select <sequence>.nextval from dual eats still the next value
of the sequence, perhaps this could be fixed some times. (if somebody
is confused .... look at the mailing list archive ;)
Regards, Mathias
