Re: Retrieving Identity Value of newly inserted row

On Fri, 2006-01-13 at 10:47 -0600, [email protected]
wrote:
> 
> 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: 

<snipped code with placeholders>

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
       column:

         $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
       behaviour.

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

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

Michael
-- 
Michael Peppler  -  [email protected]  -  http://www.peppler.org/
Sybase DBA/Developer - TeamSybase: http://www.teamsybase.com/
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html