On Wed, 2003-03-12 at 03:11, Tim Bunce wrote:
> On Tue, Mar 11, 2003 at 01:30:30PM -0800, Michael Peppler wrote:
> >
> > It looks like it pays to question things.
> >
> > The following works, although I was sure it wouldn't:
> >
> > my ($sth, $sth2);
> >
> > $sth = $dbh->prepare("insert dbi_insert(c, i) values(?,?)");
> > for(my $i = 0; $i < 2; $i++) {
> > $sth->execute("foo", 1);
> > $sth2 = $dbh->prepare("select max(id) from dbi_insert");
> > $sth2->execute;
> > while(my $d = $sth2->fetch) {
> > print "identity = @$d\n";
> > }
> > }
> >
> > Both $sth and $sth2 are on the same connection, and $sth *can* be reused
> > after $sth2 is done.
> >
> > Which means that fetching the values will be a little easier (although
> > there are locking issues, of course!)
>
> Right.
>
> So this will work reliably for DBD::Sybase:
>
> sub last_insert_id {
> my ($dbh, $table, $field) = @_;
> $_ = $dbh->quote_identifier($_) foreach ($table, $field);
> return $dbh->selectrow_array("select max($field) from $table");
> }
>
> *if* AutoCommit is off. If AutoCommit is on then there's a risk
> that the returned value will be incorrect if some other process has
> performed an insert after yours. Right?
Yes. Though I'd probably do this instead:
return $dbh->selectrow_array(qq(
declare [EMAIL PROTECTED] numeric
select [EMAIL PROTECTED] = [EMAIL PROTECTED]@identity
if([EMAIL PROTECTED] = 0)
select [EMAIL PROTECTED] = max($field) from $table
select 'last_id' = [EMAIL PROTECTED]
));
which will return the @@identity value if it has been set, and the max()
otherwise.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
