(Ovid) Re: Perl ODBC question

Actually, this is an SQL issue and not a Perl-specific one.

If you want to find the last identity after an insert and you are using MS SQL Server, you can use @@IDENTITY:

    SELECT @@IDENTITY
[download]

Unfortunately, this is not a terribly robust solution. This will return the last identity created from the current connection. However, you must run this immediately after your insert as subsequent inserts will overwrite this value. Further, if you have triggers in your database that fire when you do your insert and if these triggers do inserts, then @@IDENTITY will likely not return the data you are expecting (of course, I have serious reservations about triggers themselves). If you are using MS SQL Server 2000, this problem has been solved.

If you run the select immediately after your insert and you do not have any triggers, you should have no problem.

Cheers,
Ovid

Vote for paco!

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Comment on (Ovid) Re: Perl ODBC question Download Code

Replies are listed 'Best First'.
@@identity (boo) by boo_radley (Parson) on Sep 14, 2001 at 01:36 UTC
@@identity also works for sybase. best used in transactions.	[reply]
Re: (Ovid) Re: Perl ODBC question by mpeppler (Vicar) on Oct 09, 2001 at 01:23 UTC
You will probably not be able to get the @@identity value if you use placeholders (I know this is so for Sybase, and I suspect it is for MS-SQL). The problem is that the @@identity value is local to the currently executing code batch or procedure (which is a good thing, really), but as placeholders are implemented as temporary stored procedures the @@identity value is lost once the execution of the proc is done. And you'd have to finish (free) the prepared statement anyway to run the query to fetch the @@identity value anyway, which would defeat (at least partially) the advantage of using prepared statements with placeholders. Michael	[reply]