Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

"Getting Sql server identiy column value after insert

by zhirsr (Initiate)
on Jul 15, 2008 at 20:13 UTC ( #697793=sourcecode: print w/replies, xml ) Need Help??
Category: database programming
Author/Contact Info zhirsr@hotmail.com
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};
                    Server=$data_source;
                    Database=$database;
                    UID=$user_id;
                    PWD=$password;
                        {
                          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
    $sth->execute();

#get the id value in a varArray to use later -- it could be null (unde
+f)
    my @idarray = $sth->fetchrow_array();
    my $id = @idarray ? $idarray[0] : undef;
    print "the identity column id used this time was: $id \n" ;
    
#clean up     
$sth->finish();   
$dbh->disconnect();
Replies are listed 'Best First'.
Re: "Getting Sql server identiy column value after insert
by graff (Chancellor) on Jul 16, 2008 at 01: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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2022-05-18 00:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (68 votes). Check out past polls.

    Notices?