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

[Matt S Trout]

On Thu, Dec 20, 2007 at 08:15:24AM -0800, Marc Mims wrote:
> * 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.

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?

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/