sybperl-l Archive

From: michael dot peppler at bnpparibas dot com
Subject: Re: Retrieving Identity Value of newly inserted row
Date: Feb 1 2006 7:20AM

No, for several reasons.

First, the @@identity value is localized to the connection it was 
generated on, and to retrieve it you need to execute a SELECT. You can't 
have multiple active statements on the same connection.

Second, the @@identity value is localized to the stored procedure where it 
was generated. When you use placeholders DBD::Sybase uses an API call to 
Sybase which causes Sybase to generate a temporary stored procedure on 
behalf of the client.

The localization of @@identity makes a lot of sense. Consider:

create proc tab_one_ins
    ... some parameters...
as
insert into table_with_identity_column(.-.....)
exec tab_two_ins with_some_parameters...
select @@identity


create proc tab_two_ins
.....
as
insert into other_table_with_identity...


The "select @@identity" in the first proc should obviously return the 
identity value that was generated when data was inserted to the first 
table, even though a row was inserted to another table (in tab_two_ins) 
that also generated an identity value.

Personally this issue has never been a problem - I tend to code 
*everything* as stored procs, and as such I have full control over the 
data and how the various pieces should fit together.

Michael






Internet
mark@aufflick.com@peppler.org - 01/02/2006 00:47
 
Sent by:        owner-sybperl-l@peppler.org
To:     mpeppler, Robert_Gorrebeeck
cc:     sybperl-l
Subject:        Re: Retrieving Identity Value of newly inserted row





Couldn't DBD::Sybase internally cache the value of @@identity at the end
of each sql call that has an insert, which could then be returned later
if the method was called?

On 1/1/1970, "Michael Peppler"  wrote:


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



This message and any attachments (the "message") is
intended solely for the addressees and is confidential. 
If you receive this message in error, please delete it and 
immediately notify the sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message. 
BNP PARIBAS (and its subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le 
"message") sont etablis a l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce 
message par erreur, merci de le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce 
message non conforme a sa destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf 
autorisation expresse. L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.