Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Correct Perl Syntax for Last Record Insert in MS SQL?

by Perobl (Beadle)
on Nov 01, 2011 at 21:58 UTC ( #935233=perlquestion: print w/ replies, xml ) Need Help??
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();

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.

Comment on Correct Perl Syntax for Last Record Insert in MS SQL?
Download Code
Re: Correct Perl Syntax for Last Record Insert in MS SQL?
by Anonymous Monk on Nov 01, 2011 at 22:33 UTC
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
Re: Correct Perl Syntax for Last Record Insert in MS SQL?
by Plankton (Priest) 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.
    It even tells what's ... wrong most of the time. What error message do you get?
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();

    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://935233]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2014-08-29 00:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (275 votes), past polls