Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Retrieving Identity Value of newly inserted row
Date: Jan 13 2006 4:54PM

On Fri, 2006-01-13 at 10:47 -0600,
> All 
> I am having a hard time trying to retrieve my identity value from an
> insert statement.  I keep getting a 0 returned.  Here is an example of
> my code: 

That's a documented limitation. From the DBD::Sybase manual:

       It is not possible to retrieve the last IDENTITY value after an insert
       done with ?-style placeholders. This is a Sybase limitation/bug, not a
       DBD::Sybase problem. For example, assuming table foo has an identity

         $dbh->do("insert foo(col1, col2) values(?, ?)", undef, "string1", "string2");
         $sth = $dbh->prepare(’select @@identity’)
           ││ die "Can’t prepare the SQL statement: $DBI::errstr";
         $sth->execute ││ die "Can’t execute the SQL statement: $DBI::errstr";

         #Get the data back.
         while (my $row = $sth->fetchrow_arrayref()) {
           print "IDENTITY value = $row->[0]\n";

       will always return an identity value of 0, which is obviously incor-
       rect.  This behaviour is due to the fact that the handling of ?-style
       placeholders is implemented using temporary stored procedures in
       Sybase, and the value of @@identity is reset when the stored procedure
       has executed. Using an explicit stored procedure to do the insert and
       trying to retrieve @@identity after it has executed results in the same

       Please see the discussion on Dynamic SQL in the OpenClient C Program-
       mer’s Guide for details. The guide is available on-line at

Work-around: don't use placeholders, or hack it with a SELECT max(...)
after the insert (ugh!)

Michael Peppler  -  -
Sybase DBA/Developer - TeamSybase:
Sybase on Linux FAQ: