Re: Last ID Problem

On Wed, Feb 09, 2005 at 08:10:57AM -0800, operationsengineer1(at)yahoo(dot)com wrote:

> however, when i use currval(), as recommended, i get
> no result. i probably get an error, but i haven't
> checked for that yet.

Error checking is A Good Thing.

When I suggested using currval() I wasn't necessarily recommending
it over nextval() (although I usually prefer it); I was pointing
out that nextval() isn't the only method and that currval() is
common practice. Not that common practice makes it a good idea,
but rather that it's common practice because it works if used
properly and it can be convenient. Depending on what you're doing,
you can use currval() in a subsequent INSERT or UPDATE without
having to fetch the ID into the client code at all:

INSERT INTO foo (name) VALUES ('some name');
INSERT INTO log (fooid) VALUES (currval('foo_fooid_seq'));

> is it OK to use nextval() to get the next id value in
> the sequence before doing an insert?

Yes. If you defined a SERIAL column, that's what the column's
default expression does.

> how come currval() doesn't work.

You didn't show your currval() code so we can't say for sure why
it doesn't work. Did you call currval() *after* the INSERT? Did
you call currval() in the same connection as the INSERT? As has
been pointed out, if you use connection pooling and your call to
currval() ends up in a different connection than the INSERT, then
it won't work.

The following works for me:

$db = ADONewConnection($driver);
# $db->debug = true;
$db->Connect($connectstr);
$db->Execute("INSERT INTO foo (name) VALUES ('test')");
$id = $db->getone("SELECT currval('foo_id_seq')");
print "last insert id = $id<br>\n";

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/