Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

"Getting Sql server identiy column value after insert

by zhirsr (Initiate)
on Jul 15, 2008 at 16:13 UTC ( #697793=sourcecode: print w/replies, xml ) Need Help??
Category: database programming
Author/Contact Info
Description: Getting the identity column value of your last insert statement is tricky with DBI. the "SELECT SCOPE_IDENTITY()" statement MUST be in the same sql statement as the execute. Below is sample code from a mantis conversion program which inserts a row into the database and gets the identity column value back. Look at $insertSQL for details
#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);

#set up connection
my ( $data_source, $database, $user_id, $password ) =qw ( MANTIS-1\\SQ
+LEXPRESS lc_bugtracker_db  bugadmin 1bug2admin. );

#set up column values
my ( $name, $status, $enabled, $view_state, $access_min ,$file_path ) 
+=qw (project1 2 0 1 1 C:\\defect_tracking_project\\testproject);
my $description =  "this is the project description";

my $conn_string = "driver={SQL Server};
                          RaiseError => 1,
                          AutoCommit => 0

#Connect the database handle.
my $dbh = DBI->connect("DBI:ODBC:$conn_string");

#SQL Insert statement and get identity value created by insert
my $insertSQL = qq{ INSERT INTO mantis_project_table 
(name,status,enabled,view_state,access_min,file_path,description) VALU
+ES(?,?,?,?,?,?,?); SELECT SCOPE_IDENTITY() };

#Prepare SQL statement for execution
my $sth = $dbh->prepare($insertSQL);

#Bind parms and parm sql types for insert statement
    $sth->bind_param( 1, $name, SQL_VARCHAR );
    $sth->bind_param( 2, $status, SQL_SMALLINT );
    $sth->bind_param( 3, $enabled, SQL_BIT);
    $sth->bind_param( 4, $view_state, SQL_SMALLINT );
    $sth->bind_param( 5, $access_min, SQL_SMALLINT );
    $sth->bind_param( 6, $file_path, SQL_VARCHAR );
    $sth->bind_param( 7, $description, SQL_LONGVARCHAR);

#execute the sql statement on the server

#get the id value in a varArray to use later -- it could be null (unde
    my @idarray = $sth->fetchrow_array();
    my $id = @idarray ? $idarray[0] : undef;
    print "the identity column id used this time was: $id \n" ;
#clean up     
Replies are listed 'Best First'.
Re: "Getting Sql server identiy column value after insert
by graff (Chancellor) on Jul 15, 2008 at 21:58 UTC
    In your description you say:
    Getting the identity column value of your last insert statement is tricky with DBI.

    Do you say this because you tried the "last_insert_id" method in DBI and it didn't work for your particular database server? (If "$dbh" is your DBI handle, then  $dbh->last_insert_id(), with parameters needed for some servers, should return the autoincrement ID from the latest insert.)

    Have you checked the DBD module that goes with your particular database server, to see whether there is any mention of an "insert_id" package variable?

    It's true that the different database servers (and their respective DBD modules) have different behaviors with regard to the last_insert_id, but I think the issue is documented reasonably well in DBI as well as in the various DBD man pages. I know it works fine for mysql -- I use it all the time.

    Apart from that, some people include use warnings (or the "-w" flag) in their scripts, which means that your assignment of a value to $id should probably go like this:

    my $id = @idarray ? $idarray[0] : "undef";
    so that the following "print" does not set off a warning about "Use of initialized value in print".

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: sourcecode [id://697793]
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2023-12-11 03:34 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (41 votes). Check out past polls.