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: "Mark Aufflick" <mark at aufflick dot com>
Subject: Re: Retrieving Identity Value of newly inserted row
Date: Feb 2 2006 1:13AM

Makes sense. I've never needed to do this in my life either, but that's
because I have always used sequences...

On 1/1/1970, ""

>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...
>insert into table_with_identity_column(.-.....)
>exec tab_two_ins with_some_parameters...
>select @@identity
>create proc tab_two_ins
>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.
> - 01/02/2006 00:47
>Sent by:
>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
>>       rect.  This behaviour is due to the fact that the handling of
>>       placeholders is implemented using temporary stored procedures in
>>       Sybase, and the value of @@identity is reset when the stored
>>       has executed. Using an explicit stored procedure to do the insert
>>       trying to retrieve @@identity after it has executed results in the
>>       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.
> iBurst Wireless Broadband from $34.95/month
> Forward undetected SPAM to: