Er, just a minor point not directly related, but I think you want to do that _right_after_ the insert. In other words not as two seperate calls to the DBI object. I do it something like this:

my ($index)=$dbh->selectrow_array('insert into foo (bar,baz) values (1
+,2) select @@identity');
[download]

I think if you do it as a do() and as a select() seperately the @@identity will be cleared. Or you can set up a stored proc and then have the stored proc return the identity after the insert for you.

BTW, the @@identity only exists on Sybase and MSSql to the best of my knowledge.

As long as the insert and the select @@identity happen sequentially on the same connection you'll be fine:

$dbh->do("insert ... ");
$sth = $dbh->prepare('select @@identity');
$sth->execute();
my $data = $sth->fetch;...
[download]

What will NOT work is selecting the @@identity value IF the insert happened in a stored procedure, because the @@identity variable is localized. So the following:

$dbh->do("exec some_proc_with_an_insert 1,2,3");
$sth = $dbh->prepare('select @@identity');
$sth->execute();
my $data = $sth->fetch;
[download]

will NOT return the @@identity value generated in the stored procedure. This is the reason why you can't get the @@identity value after doing an insert with placeholders (at least with Sybase): any statement with placeholders generates a temporary stored procedure, hence localizing the @@identity variable.

Michael

Ah. Ok I stand corrected. Thanks for the heads up.

Cheers mate. :-)



---
demerphq



First they ignore you, then they laugh at you, then they fight you, then you win.
-- Gandhi

I'm not trying this, but I'd use a semicolon between the first and the second SQL statement.

Anyway, a dirty and bad looking solution is to have a separate statement MAX() the value of the identity column (that will likely be a unique key, right?) so you get the last inserted value with no table or key scan, and the disk page will very likely be in RAM as you just used it. It's quite fast, if you can afford the two separate database statements. Of course demerphq solutiuon is better. :-)

I'd use a semicolon between the first and the second SQL statement

No semicolon needed between statements with MS-SQL, and in fact the semi-colon would be a syntax error with Sybase.

...a dirty and bad looking solution is to have a separate statement MAX() the value of the identity...

Unless you use a transaction around the insert and the select max() (which effectively single threads inserts) then there is no guarantee that the max() will be the value that you just inserted (unless of course you know that you are the only process inserting data to that particular table).

Michael

but how can I do this query in perl without it throwing up an error because I haven't declared @IDENTITY?

use strict;
use warnings;
my @STUFF = 1..4;
print "HI there @STUFF",$/;
print 'HI there @STUFF',$/;
__END__
HI there 1 2 3 4
HI there @STUFF
[download]