in reply to Sequences, last_insert_id, SQLite, and Oracle
The way that you do this *correctly* in oracle is to make use of its "returning" clause, and the DBI's bind_param_in_out() method. This is the only way to properly avoid concurrency issues, and it also works cleanly and is fast.
In code, it looks something like this:
Assuming that the id column is driven by a sequence and a trigger.my $sql = "insert into foo (name,value) values (?,?) returning id into + ?"; my $sth = $dbh->prepare($sql); $sth->bind_param(0, $name); $sth->bind_param(1, $value); $sth->bind_param_inout(2, \$id, 1024); # this is a reference that gets + written to! $sth->execute; print $id; # or whatever... $id has been written into by the execute
Granted, doing individual calls to bind_param / bind_param_inout is kind of painful and ugly, so when I do this I actually written a wrapper around calling all the binding, but this is the crux of what it does / how it works.
------------ :Wq Not an editor command: Wq
In Section
Seekers of Perl Wisdom