http://www.perlmonks.org?node_id=935233

Perobl has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks

I need to obtain the id of the last record inserted in my table (SQL database). I believe I need to use SCOPE_IDENTITY().

My code looks like this:

my $dbh = DBI -> connect("$dsn; server=$host; Database=$database", $us
+er, $auth, \%attr) || die "database connection not made: $DBI::errstr
+";
    
my $sth = $dbh -> prepare("INSERT INTO ocr_main (project_number, lead_
+order, employee_id, due_date, ocr_type, line_item, mech_rnr, mech_rfd
+, mech_rfa, mech_dre, ctrl_rnr, ctrl_rfd, ctrl_rfa, ctrl_dre) VALUES 
+(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") || die "unable to prepar
+e query";

$sth -> execute($project_number, $lead_order, $employee_id, $due_date,
+ $ocr_type, $line_item, $mech_rnr, $mech_rfd, $mech_rfa, $mech_dre, $
+ctrl_rnr, $ctrl_rfd, $ctrl_rfa, $ctrl_dre);

$sth -> finish();
$dbh -> disconnect();

[download]

I'd like to save the last inserted id in the variable 'id'. This is very straight forward in mySQL, but I'm having difficulty doing it in SQL. Can someone show me the proper syntax please? Thank you so much.

Replies are listed 'Best First'.
Re: Correct Perl Syntax for Last Record Insert in MS SQL?
by Anonymous Monk on Nov 01, 2011 at 22:33 UTC
[reply]
Re: Correct Perl Syntax for Last Record Insert in MS SQL?
by Anonymous Monk on Nov 01, 2011 at 22:34 UTC
    As I recall from MSSql compatible Sybase land, this would essentially be a 'select @@identity' on the current connection. DBD::Sybase supports this through $dbh->last_insert_id 'Fiq
[reply]
Re: Correct Perl Syntax for Last Record Insert in MS SQL?
by Plankton (Vicar) on Nov 01, 2011 at 22:35 UTC
    You can always test your script's syntax with perl -c like so ... 
    plankton@chum-bucket:~$ cat test.pl 
#!/usr/bin/perl -w

use strict;

my $var = 'hello world';

print "$var\n";
plankton@chum-bucket:~$ perl -c test.pl 
test.pl syntax OK
plankton@chum-bucket:~$ cat test2.pl 
#!/usr/bin/perl -w

use strict;

my $var = 'hello world';

print $var\n";
plankton@chum-bucket:~$ perl -c test2.pl 
Backslash found where operator expected at test2.pl line 7, near "$var
+\"
        (Missing operator before \?)
String found where operator expected at test2.pl line 7, at end of lin
+e
        (Missing semicolon on previous line?)
Can't find string terminator '"' anywhere before EOF at test2.pl line 
+7.

    [download]
    It even tells what's ... wrong most of the time. What error message do you get?
[reply]
[d/l]
[select]
Re: Correct Perl Syntax for Last Record Insert in MS SQL?
by Perobl (Beadle) on Nov 02, 2011 at 17:27 UTC

    SCOPE_IDENTITY() can be used this way to obtain the last record insert in MS SQL Server:

    my $dbh = DBI -> connect("$dsn; server=$host; Database=$database", $us
+er, $auth, \%attr) || die "database connection not made: $DBI::errstr
+";

my $sth = $dbh -> prepare("INSERT INTO ocr_main (project_number, lead_
+order, employee_id, due_date, ocr_type, line_item, mech_rnr, mech_rfd
+, mech_rfa, mech_dre, ctrl_rnr, ctrl_rfd, ctrl_rfa, ctrl_dre) VALUES 
+(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); SELECT SCOPE_IDENTITY()")
+ || die "unable to prepare query";

$sth -> execute($project_number, $lead_order, $employee_id, $due_date,
+ $ocr_type, $line_item, $mech_rnr, $mech_rfd, $mech_rfa, $mech_dre, $
+ctrl_rnr, $ctrl_rfd, $ctrl_rfa, $ctrl_dre);

my $ocr_number = $sth -> fetchrow_array();

$sth -> finish();
$dbh -> disconnect();

    [download]

    Note I've added a SELECT along with the SCOPE_IDENTITY() function to the same INSERT string. Now simply use fetchrow_array() to retrieve the last record.

    Maybe this will help someone else.

[reply]
[d/l]
      Just wanted to say thank you. Your answer was the only one that showed you could use the Select scope_identity() inside the insert query.
[reply]

Back to Seekers of Perl Wisdom