Fw: [Dbix-class] last insert ID from MSSQL?

[Marc Mims]

* Matt S Trout <dbix-class at trout.me.uk> [071220 09:10]:
> On Thu, Dec 20, 2007 at 08:15:24AM -0800, Marc Mims wrote:
> > However, it seems SCOPE_IDENTITY() must be called in the same statement,
> > not just the same connection.
> 
> Well, the 'problem' with @@IDENTITY is common to pretty much every other
> last_insert_id implementation (certainly the pg one suffers from it).
> 
> The usual solution is to use a sequence and prefetch the value.
> 
> However, since we -can- fix this we probably might as well.
> 
> How about having the MSSQL storage(s) tack SCOPE_IDENTITY() onto the end of
> insert calls if appropriate and make last_insert_id effectively an accessor?

Something like this?

# in DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server

    sub _prep_for_execute {
	my $self = shift;
	my ($op, $extra_bind, $ident, $args) = @_;

	my ($sql, $bind) = $self->SUPER::_prep_for_execute(@_);
	$sql .= ';SELECT SCOPE_IDENTITY()' if $op eq 'insert';

	return ($sql, $bind);
    }

    sub insert {
	my $self  = shift;
	my $table = shift;

	$table = $self->_quote($table) unless ref($table);
	$self->{_scope_identity} = $self->SUPER::insert($table, @_);
    }

    sub last_insert_id {
	my ( $self ) = @_;

	my $id = $self->{_scope_identity};

	# discard after read so we don't pick up a stale value, later
	undef $self->{_scope_identiy};

	return $id;
    }

    	-Marc