PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
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, "michael.peppler@bnpparibas.com"
 wrote:

>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.
>
>
>========================================================================
> iBurst Wireless Broadband from $34.95/month   www.platformnetworks.net
> Forward undetected SPAM to:                   spam@mailsecurity.net.au
>========================================================================
>