SQL Anywhere database

Author

@@Identity

E. ten Westenend

2005-12-27, 3:23 am

Hi,

Is there any way to get to last created autoincrement value for a specific
table. The change the global @@identity is not the roght values is to big
for me.

Eric

Dmitri

2005-12-27, 7:23 am

E. ten Westenend wrote:

> Is there any way to get to last created autoincrement value for a specific
> table.

select @@identity into :id to return value allocated during last insert,
get_identity() to preallocate identity values before insert (ASA 8.0.1+).

Dmitri.

E. ten Westenend

2005-12-27, 7:23 am

Hi Dmitri,

I have read that @@identity is for all tables, is there now wat to het the
last identity value for a specific tables.
I have also read that if you insert a record in a table with an
autoincreement that, if that table has an after insert trigger which also
inserts a record in a table with a autoincrement key the @@identity values
if from the second table, wherte i need the value from the first table.

It would be nice to run this statement:

select @@identity from my_table

Eric

"Dmitri" <NOdimSPAM@mail15.com> schreef in bericht
news:43b10d96$1@foru
ms-1-dub...
> E. ten Westenend wrote:
>
specific[color=darkr
ed]
>
> select @@identity into :id to return value allocated during last insert,
> get_identity() to preallocate identity values before insert (ASA 8.0.1+).
>
> Dmitri.

E. ten Westenend

2005-12-27, 7:23 am

Hi

the first line of my last message is bad to read , it should be:

Is there no way to get the last identity for a specific table.

Eric

"E. ten Westenend" <ewestenend@bcsbv.nl> schreef in bericht
news:43b111d9$1@foru
ms-1-dub...
> Hi Dmitri,
>
> I have read that @@identity is for all tables, is there now wat to het the
> last identity value for a specific tables.
> I have also read that if you insert a record in a table with an
> autoincreement that, if that table has an after insert trigger which also
> inserts a record in a table with a autoincrement key the @@identity values
> if from the second table, wherte i need the value from the first table.
>
> It would be nice to run this statement:
>
> select @@identity from my_table
>
> Eric
>
> "Dmitri" <NOdimSPAM@mail15.com> schreef in bericht
> news:43b10d96$1@foru
ms-1-dub...
> specific
8.0.1+).[color=darkred]
>
>

Martin Baur

2005-12-27, 9:23 am

In article <43b1166f@forums-1-dub>, ewestenend@bcsbv.nl says...
> Hi
>
> the first line of my last message is bad to read , it should be:
>
> Is there no way to get the last identity for a specific table.

Hi Eric

AFAIK, there is nothing like that. Even if you use select @@identity from table, you alsways get the global @@identity. Make a proposal/suggestion in the NG product_future.

I will support this idea because now I have to use triggers if I have inserts which in turn do inserts to other tables and therfore need the @@identity of all newly inserted rows.

Having a thing like your wish would eliminate some complexity from the triggers.

Regards

--
Martin Baur

MindPower.com, IT-Services

Rob Waywell

2005-12-27, 11:23 am

The @@identity values have worked as a stack since 7.0.0. See this section
of the docs for details:

ASA SQL Reference

SQL Language Elements

Variables

Global variables

@@identity global variable

Note also that the value is *connection specific* so as long as you check it
after each insert you should have no problem getting the value for the
specific table that you just inserted into.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Martin Baur" <tinu@mindpower.com> wrote in message
news:MPG. 1e1b72db985745da9898
35@forums.sybase.com...
> In article <43b1166f@forums-1-dub>, ewestenend@bcsbv.nl says...
>
> Hi Eric
>
> AFAIK, there is nothing like that. Even if you use select @@identity from
> table, you alsways get the global @@identity. Make a proposal/suggestion
> in the NG product_future.
>
> I will support this idea because now I have to use triggers if I have
> inserts which in turn do inserts to other tables and therfore need the
> @@identity of all newly inserted rows.
>
> Having a thing like your wish would eliminate some complexity from the
> triggers.
>
> Regards
>
> --
> Martin Baur
>
> MindPower.com, IT-Services

Greg Fenton

2005-12-27, 1:23 pm

E. ten Westenend wrote:
>
> Is there no way to get the last identity for a specific table.
>

We'd need even more details to this request. Are you looking for the
last autoincrement value inserted into a given table for the *current*
connection or across all connections?

Can you explain what it is you are actually trying to do? There may be
another approach to solving your particular problem than "get the last
identity for a specific table".

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Eric ten Westenend

2005-12-28, 3:23 am

Hi Greg,

I use Delphi with ClientDatasets (In Memory table) this table is filled
using an AsaDataset.
When i apply an insert to the ClientDataset , it can not read back the auto
increment value.
So straight after the insert i use a query to get that value. There can be
some time (milli seconds) between those 2 statements and that could be
enough for an other user to do an insert on a table with an autoincrrement
field so my call to @@identity would not result in the proper value i need.

So it must be acorss all connections..

Eric

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> schreef in bericht
news:43b17e15$1@foru
ms-1-dub...
> E. ten Westenend wrote:
>
> We'd need even more details to this request. Are you looking for the last
> autoincrement value inserted into a given table for the *current*
> connection or across all connections?
>
> Can you explain what it is you are actually trying to do? There may be
> another approach to solving your particular problem than "get the last
> identity for a specific table".
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Paul Horan[TeamSybase]

2005-12-28, 1:23 pm

"Eric ten Westenend" <e.ten.westenend[no_spam]@zonnet.nl> wrote in message
news:43b23b64$1@foru
ms-2-dub...
> Hi Greg,
>
> I use Delphi with ClientDatasets (In Memory table) this table is filled
> using an AsaDataset.
> When i apply an insert to the ClientDataset , it can not read back the
> auto increment value. So straight after the insert i use a query to get
> that value. There can be some time (milli seconds) between those 2
> statements and that could be enough for an other user to do an insert on a
> table with an autoincrrement field so my call to @@identity would not
> result in the proper value i need.
>
> So it must be acorss all connections..
>
> Eric
>

You "completely" misunderstand the use of @@identity. It most certainly IS
connection-specific. If my connection inserts a row (and gets ID #21, for
example), I can go to lunch and come back, and as long as I haven't been
disconnected, SELECT @@identity will return 21 for me. It doesn't matter
that while I was out, a million other inserts happened to that same table on
other connections - they'll get their values for @@identity, and mine will
remain at 21.

The only problem I've ever had with @@identity involved the use of INSERT
triggers, where a subsequent trigger also did an insert to a different table
that also had an AUTOINCREMENT column. At the end of that atomic insert,
@@identity holds the value that was inserted into the SECOND table, not the
first (which is what we mistakenly were expecting). We had to redesign the
trigger to NOT use an autoincrement column.

Paul Horan[TeamSybase]

Greg Fenton

2005-12-29, 3:23 am

Paul Horan[TeamSybase] wrote:
>
> You "completely" misunderstand the use of @@identity. It most certainly IS
> connection-specific.

I think the word that throws a lot of people off is "global".

@@identity is "global" in that it hold the most recent autoincrement
value inserted to *any* table. If you insert into table_A and then
insert into table_B, and both tables have autoincrement columns, then
@@identity will contain the value for table_B. If you need the value
from table_A, you need to get it from @@identity prior to inserting into
table_B...otherwise you have to revert to some *less efficient* (and
often *error prone*) method like "select max(id) from table_A" [again,
note the *error prone*...]

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Volker Barth

2005-12-29, 3:23 am

Hi all,

I definetely agree on the connection-specific character of
@@IDENTITY - otherwise it really would not make much sense.

But I think Paul's hint at the problematic use of @@IDENTITY
in triggers is obsolete, as this was changed in 7.0.0:

From the 9.0.2 docs:

What's New in SQL Anywhere Studio
What's New in Version 7.0.0
Behavior changes in version 7.0.0
Adaptive Server Anywhere behavior changes

[...]
@@identity in triggers If a table (T1) with an
autoincrement column has an insert trigger which causes an
insert into a second table (T2) also having an autoincrement
column, it was not previously possible to obtain the
autoincrement value assigned for T1 after the insert had
completed. At that point, the value of @@identity would be
the value assigned to T2. The behavior of @@identity has
been altered to make the value accessible.
[...]

So I think this should only be a problem for very old ASA
versions.

Best regards
Volker

Paul Horan wrote:
>
> ...
> The only problem I've ever had with @@identity involved
> the use of INSERT triggers, where a subsequent trigger
> also did an insert to a different table that also had an
> AUTOINCREMENT column. At the end of that atomic insert,
> @@identity holds the value that was inserted into the
> SECOND table, not the first (which is what we mistakenly
> were expecting). We had to redesign the trigger to NOT
> use an autoincrement column.
>
> Paul Horan[TeamSybase]

Eric ten Westenend

2005-12-29, 1:23 pm

Hi,

Thanks a lot for all these answers.

I will test what Volker told us.

Eric

<Volker Barth> schreef in bericht
news:43b3a856.1e0f.1681692777@sybase.com...[color=darkred]
> Hi all,
>
> I definetely agree on the connection-specific character of
> @@IDENTITY - otherwise it really would not make much sense.
>
> But I think Paul's hint at the problematic use of @@IDENTITY
> in triggers is obsolete, as this was changed in 7.0.0:
>
> From the 9.0.2 docs:
>
> What's New in SQL Anywhere Studio
> What's New in Version 7.0.0
> Behavior changes in version 7.0.0
> Adaptive Server Anywhere behavior changes
>
> [...]
> @@identity in triggers If a table (T1) with an
> autoincrement column has an insert trigger which causes an
> insert into a second table (T2) also having an autoincrement
> column, it was not previously possible to obtain the
> autoincrement value assigned for T1 after the insert had
> completed. At that point, the value of @@identity would be
> the value assigned to T2. The behavior of @@identity has
> been altered to make the value accessible.
> [...]
>
> So I think this should only be a problem for very old ASA
> versions.
>
> Best regards
> Volker
>
>
> Paul Horan wrote:

Sponsored Links