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

[Michael Higgins]

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.

-----------------------------------------
Begin forwarded message:

Date: Wed, 19 Dec 2007 22:59:57 -0800
From: Michael Higgins <linux at evolone.org>
To: Marc Mims <marc at questright.com>
Subject: Re: [Dbix-class] last insert ID from MSSQL?


On Wed, 19 Dec 2007 08:17:10 -0800
Marc Mims <marc at questright.com> wrote:

[8<]

> I've used the following script to test ident checking via ODBC for my
> DB2/400 backend.  Review the code and make the necessary edits in the
> configuration section for yours.  You'll definitely need to change
> $DSN.
> 
> Please send me the output.

[8< this is windows ODBC output] 

# with prepare SELECT @@IDENTITY:
$result = ['1'];
# with prepare SELECT SCOPE_IDENTITY():
$result = [undef];
# with prepare SELECT IDENT_CURRENT('IDENT_TEST'):
$result = ['1'];
# with prepare_cached SELECT @@IDENTITY:
$result = ['1'];
# with prepare_cached SELECT SCOPE_IDENTITY():
$result = [undef];
# with prepare_cached SELECT IDENT_CURRENT('IDENT_TEST'):
$result = ['1'];

And from my linux box: [freeTDS unixODBC]

# with prepare SELECT @@IDENTITY:
$result = ['1'];
# with prepare SELECT IDENT_CURRENT('IDENT_TEST'):
$result = ['1'];
# with prepare SELECT SCOPE_IDENTITY():
$result = [undef];
# with prepare_cached SELECT @@IDENTITY:
$result = ['1'];
DBD::ODBC::db prepare_cached failed: (DBD: st_prepare/SQLPrepare
err=-1) at ident_test.pl line 86.

[8< script and musings ]

"SCOPE_INDENTITY() was intended to be called in the same call as the
Insert or Update,"

Thats [what's meant by] current SESSION and scope. Only works for stored
procedure or batch. Hmmm. So:

[8<]

my $sth = $dbh->prepare(<<"");
insert into $TABLE (name) values(?);select SCOPE_IDENTITY();

for (1..10){

$sth->execute('foo'.$_);
my @last_id = $sth->fetchrow_array;
print @last_id, "\n";
}

 perl ident_test.pl 
1
2
3
4
5
6
7
8
9
10

[8<]

Any clue how to implement in DBIx-Class? 

----------------------------------------------------

Cheers,

-- 
 |\  /|        |   |          ~ ~  
 | \/ |        |---|          `|` ?
 |    |ichael  |   |iggins    \^ /
 michael.higgins[at]evolone[dot]org