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