Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Do you know where your variables are?
 
PerlMonks  

Re: Correct Perl Syntax for Last Record Insert in MS SQL?

by Perobl (Beadle)
on Nov 02, 2011 at 17:27 UTC ( #935452=note: print w/ replies, xml ) Need Help??


in reply to Correct Perl Syntax for Last Record Insert in MS SQL?

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.


Comment on Re: Correct Perl Syntax for Last Record Insert in MS SQL?
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2014-04-19 18:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (483 votes), past polls