The downside of it is this. When you issue a SELECT @@INSERT, how do you know that the most recent ID inserted is the one that you want? If two inserts happen as the same time, you run the risk of grabbing another users id.
If you don't need to know the id when inserting, you can simply issue a
INSERT INTO {table} VALUES ({sequence}.NEXTVAL, ...)
If you need to know the id, get the id from the sequence, then use it for the insert statement. That is two trips to the database, but so is INSERT then SELECT @@INSERT. And it ensures that you have the correct id.
It is a bad idea to use the currval for production code, because you cannot rely on it. And locking a table to ensure the correct id, is bad practice in a multi-user system.
And if you want a front end to not card about the ID's, then you can create a BEFORE INSERT TRIGGER to do the select from the sequence.
Dave Polito
-----Original Message-----
From: Gloss Mathias
To: [EMAIL PROTECTED]; 'Dittmar, Daniel'
Sent: 10/29/2001 9:38 AM
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
