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

[Marc Mims]

* Michael Higgins <linux at evolone.org> [071220 04:00]:
> Here is a trimmed excerpt from having taken this thread off list. I'm
> posting it in case anyone else finds it useful in some context, or
> maybe has some input.
> 
> [8< this is windows ODBC output] 
> # with prepare SELECT @@IDENTITY:
> $result = ['1'];
> # with prepare SELECT SCOPE_IDENTITY():
> $result = [undef];
> 
> And from my linux box: [freeTDS unixODBC]
> # with prepare SELECT @@IDENTITY:
> $result = ['1'];
> # with prepare SELECT SCOPE_IDENTITY():
> $result = [undef];
> 
> Any clue how to implement in DBIx-Class? 

In response to an earlier message on the list, some weeks ago, I offered
to implement _dbh_last_insert_id for Microsoft SQL Server over an ODBC
connection for Michael to test, with the intention of adding it to DBIC.

At issue, is which method to use to retrieve the last insert ID.  MSSQL
supports @@IDENTITY and SCOPE_IDENTITY().  The latter would be preferred
if we could use it since @@IDENTITY can return the wrong value in some
cases.  For example, if the insert results in a trigger insert in
another table, @@IDENTITY returns the ID of the trigger inserted row,
instead of the ID of the original inserted row.  I.e., @@IDENTITY
returns the ID of the most recent insert for the session.

However, it seems SCOPE_IDENTITY() must be called in the same statement,
not just the same connection.

This works:

    my ($id) = $dbh->selectrow_array(
    	'INSERT INTO FOO (bar) VALUES(?); SELECT SCOPE_IDENTITY()'
    );

This does not:

    $dbh->do('INSERT INTO FOO (bar) VALUES(?)', undef, @bind);
    my ($id) = $dbh->selectrow_array('SELECT SCOPE_IDENTITY()');


The implementation we were testing was:

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

    sub _dbh_last_insert_id {
	my ($self, $dbh, $source, $col) = @_;

	my $sth = $dbh->prepare_cached('SELECT SCOPE_IDENTITY()', {}, 3);
	$sth->execute();

	my @res = $sth->fetchrow_array();

	return @res ? $res[0] : undef;
    }

SCOPE_IDENTITY() doesn't work here.  @@IDENTITY does.

The existing DBIx::Class::Storage::DBI::MSSQL implements _dbh_last_insert_id
with @@IDENTITY.  Perhaps that is good enough. ??

	-Marc