Board index » delphi » Identity error?? Please help

Identity error?? Please help


2004-04-19 02:53:10 PM
delphi28
Hi all
I have an appplication that does an insert into a table that has an identity
column. In the afterpost event of the adoquery i want to get the newly
inserted indentity value. Normally i don't have a problem. However, when I
have a detail dataset{*word*154} off it (relationship is defined using the
Datasource property of the detail query) AND I have trigger on the master
table that inserts into the detail table(which also has an identity column),
the value I get in my afterpost event is the value of the details identity
column!!!!!!!!!! What the...??? As soon as I remove the insert in the
trigger, I get the correct value back (i.e. the identity value of the master
record).
How do I overcome this problem? Any suggestions on what is the best way to
retrieve an identity value given the above situation (I may have multiple
detail tables of master table) or generally.
Any help would be greatly appreciated
TIA
Andre
 
 

Re:Identity error?? Please help

Hi Andre,
Sounds like you're an expat now living in Aus ;-).
I do know that in SQL Server, if you get the value of an IDENTITY column in
a trigger, depending upon which global variable you look at, you will always
get the last IDENTITY value inserted. This would be consistent with the
value that you are gettting back. I am not too sure how Delphi handles this,
but how are you getting the last inserted identoty value? A bit of detail
might help you solve the problem.
Regards,
Norman
"Andre Greyling" <XXXX@XXXXX.COM>writes
Quote
Hi all

I have an appplication that does an insert into a table that has an
identity
column. In the afterpost event of the adoquery i want to get the newly
inserted indentity value. Normally i don't have a problem. However, when I
have a detail dataset{*word*154} off it (relationship is defined using the
Datasource property of the detail query) AND I have trigger on the master
table that inserts into the detail table(which also has an identity
column),
the value I get in my afterpost event is the value of the details
identity
column!!!!!!!!!! What the...??? As soon as I remove the insert in the
trigger, I get the correct value back (i.e. the identity value of the
master
record).

How do I overcome this problem? Any suggestions on what is the best way to
retrieve an identity value given the above situation (I may have multiple
detail tables of master table) or generally.

Any help would be greatly appreciated

TIA
Andre


 

Re:Identity error?? Please help

Check out @@SCOPE_IDENTITY in SQL Servers BOL maybe that will help
"Morpheus" <XXXX@XXXXX.COM>writes
Quote
Hi Andre,

Sounds like you're an expat now living in Aus ;-).

I do know that in SQL Server, if you get the value of an IDENTITY column
in
a trigger, depending upon which global variable you look at, you will
always
get the last IDENTITY value inserted. This would be consistent with the
value that you are gettting back. I am not too sure how Delphi handles
this,
but how are you getting the last inserted identoty value? A bit of detail
might help you solve the problem.
Regards,
Norman

"Andre Greyling" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>Hi all
>
>I have an appplication that does an insert into a table that has an
identity
>column. In the afterpost event of the adoquery i want to get the newly
>inserted indentity value. Normally i don't have a problem. However, when
I
>have a detail dataset{*word*154} off it (relationship is defined using the
>Datasource property of the detail query) AND I have trigger on the
master
>table that inserts into the detail table(which also has an identity
column),
>the value I get in my afterpost event is the value of the details
identity
>column!!!!!!!!!! What the...??? As soon as I remove the insert in the
>trigger, I get the correct value back (i.e. the identity value of the
master
>record).
>
>How do I overcome this problem? Any suggestions on what is the best way
to
>retrieve an identity value given the above situation (I may have
multiple
>detail tables of master table) or generally.
>
>Any help would be greatly appreciated
>
>TIA
>Andre
>
>


 

Re:Identity error?? Please help

Thanks for the help - yes the problem is @@IDENTITY related. It is still
strange how Delphi behaves though. The code is simply:
(1) qry1.append;
(2) qry1.fieldbyname('ID').asinteger
qry1 does a simple SELECT on table Master. Master has a trigger (on INSERT)
which creates detail tables
each table has an identity column.
line (2) then returns the identity value of the last detail table - odd !
However, what is the best way to create a master record and retrieve it's
identity value ? Should the code be in a stored procedure or is there a
simpler way ?
Any ideas ?
Bye
"Brian Hollister" <bhollisterATfuturaintlDOTcom>writes
Quote
Check out @@SCOPE_IDENTITY in SQL Servers BOL maybe that will help

"Morpheus" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>Hi Andre,
>
>Sounds like you're an expat now living in Aus ;-).
>
>I do know that in SQL Server, if you get the value of an IDENTITY column
in
>a trigger, depending upon which global variable you look at, you will
always
>get the last IDENTITY value inserted. This would be consistent with the
>value that you are gettting back. I am not too sure how Delphi handles
this,
>but how are you getting the last inserted identoty value? A bit of
detail
>might help you solve the problem.
>Regards,
>Norman
>
>"Andre Greyling" <XXXX@XXXXX.COM>writes
>news:XXXX@XXXXX.COM...
>>Hi all
>>
>>I have an appplication that does an insert into a table that has an
>identity
>>column. In the afterpost event of the adoquery i want to get the newly
>>inserted indentity value. Normally i don't have a problem. However,
when
I
>>have a detail dataset{*word*154} off it (relationship is defined using
the
>>Datasource property of the detail query) AND I have trigger on the
master
>>table that inserts into the detail table(which also has an identity
>column),
>>the value I get in my afterpost event is the value of the details
>identity
>>column!!!!!!!!!! What the...??? As soon as I remove the insert in the
>>trigger, I get the correct value back (i.e. the identity value of the
>master
>>record).
>>
>>How do I overcome this problem? Any suggestions on what is the best
way
to
>>retrieve an identity value given the above situation (I may have
multiple
>>detail tables of master table) or generally.
>>
>>Any help would be greatly appreciated
>>
>>TIA
>>Andre
>>
>>
>
>


 

Re:Identity error?? Please help

Andre,
The "problem" is that all that stuff is happening on the server, not the
client (Delphi). For master-detail(s) situations, I recommend using a seed
table and a stored procedure to increment/return the next available number.
I'll post a demo to borland.public.attachments.
krf
"Andre Greyling" <XXXX@XXXXX.COM>writes
Quote
Thanks for the help - yes the problem is @@IDENTITY related. It is still
strange how Delphi behaves though. The code is simply:
(1) qry1.append;
(2) qry1.fieldbyname('ID').asinteger

qry1 does a simple SELECT on table Master. Master has a trigger (on
INSERT)
which creates detail tables
each table has an identity column.
line (2) then returns the identity value of the last detail table - odd !

However, what is the best way to create a master record and retrieve it's
identity value ? Should the code be in a stored procedure or is there a
simpler way ?
Any ideas ?

Bye


"Brian Hollister" <bhollisterATfuturaintlDOTcom>writes
news:40844810$XXXX@XXXXX.COM...
>Check out @@SCOPE_IDENTITY in SQL Servers BOL maybe that will help
>
>"Morpheus" <XXXX@XXXXX.COM>writes
>news:XXXX@XXXXX.COM...
>>Hi Andre,
>>
>>Sounds like you're an expat now living in Aus ;-).
>>
>>I do know that in SQL Server, if you get the value of an IDENTITY
column
>in
>>a trigger, depending upon which global variable you look at, you will
>always
>>get the last IDENTITY value inserted. This would be consistent with
the
>>value that you are gettting back. I am not too sure how Delphi handles
>this,
>>but how are you getting the last inserted identoty value? A bit of
detail
>>might help you solve the problem.
>>Regards,
>>Norman
>>
>>"Andre Greyling" <XXXX@XXXXX.COM>writes
>>news:XXXX@XXXXX.COM...
>>>Hi all
>>>
>>>I have an appplication that does an insert into a table that has an
>>identity
>>>column. In the afterpost event of the adoquery i want to get the
newly
>>>inserted indentity value. Normally i don't have a problem. However,
when
>I
>>>have a detail dataset{*word*154} off it (relationship is defined using
the
>>>Datasource property of the detail query) AND I have trigger on the
>master
>>>table that inserts into the detail table(which also has an identity
>>column),
>>>the value I get in my afterpost event is the value of the details
>>identity
>>>column!!!!!!!!!! What the...??? As soon as I remove the insert in
the
>>>trigger, I get the correct value back (i.e. the identity value of
the
>>master
>>>record).
>>>
>>>How do I overcome this problem? Any suggestions on what is the best
way
>to
>>>retrieve an identity value given the above situation (I may have
>multiple
>>>detail tables of master table) or generally.
>>>
>>>Any help would be greatly appreciated
>>>
>>>TIA
>>>Andre
>>>
>>>
>>
>>
>
>


 

Re:Identity error?? Please help

Andre Greyling writes:
Quote
Thanks for the help - yes the problem is @@IDENTITY related. It is still
strange how Delphi behaves though. The code is simply:
(1) qry1.append;
(2) qry1.fieldbyname('ID').asinteger

qry1 does a simple SELECT on table Master. Master has a trigger (on INSERT)
which creates detail tables
each table has an identity column.
line (2) then returns the identity value of the last detail table - odd !

This behaviour is not caused by Delphi. It is working as advertised by
MS SQL Server. As Brian said: @@SCOPE_IDENTITY may be what you need.
Marjan
____________________________
Marjan Venema - BJM Software
www.bjmsoftware.com
www.bjmsoftware.nl
 

Re:Identity error?? Please help

"Andre Greyling" <XXXX@XXXXX.COM>writes news:<4085148d$XXXX@XXXXX.COM>...
Quote
Thanks for the help - yes the problem is @@IDENTITY related. It is still
strange how Delphi behaves though. The code is simply:
(1) qry1.append;
(2) qry1.fieldbyname('ID').asinteger

qry1 does a simple SELECT on table Master. Master has a trigger (on INSERT)
which creates detail tables
each table has an identity column.
line (2) then returns the identity value of the last detail table - odd !

However, what is the best way to create a master record and retrieve it's
identity value ? Should the code be in a stored procedure or is there a
simpler way ?
Any ideas ?

Bye
It sounds like you've run up against the ADO bug of the year... ADO
uses @@IDENTITY to retrieve the last inserted identity value after a
Post, when it should be using SCOPE_IDENTITY(). There has been much
discussion on whether this issue is a 'bug' as it works as
documented... But if it is not a bug, then it is certainly an
extremely serious design flaw, IMHO. I have seen various workarounds
listed online, none of which really solve the problem:
1. Don't use triggers.
2. Always use stored procedures for INSERTs and UPDATEs.
3. Don't use IDENTITY columns
4. Microsoft recommends: You could implement a business object that
dispenses "identity" column information directly to your application.
This dispenser should be free-threaded, in case multiple applications
are using it, and it probably needs to work with clients across a
network. In this case, a Microsoft Transaction Server implementation
would work the best. (KB article 195910)
5. Use GUIDs in place of IDENTITY
The scary and ugly solution that we are using (because it just
*works*) is to save the @@IDENTITY value on entry to a trigger and
restore it at the end. This is not a new idea - similar solutions
have been used with SQL Server 7.0 in the past to work around the lack
of a SCOPE_IDENTITY() function. For example:
DECLARE @example TABLE (ii INT IDENTITY (5,23))
INSERT @example DEFAULT VALUES
PRINT 'Initial Identity = '+CAST(@@IDENTITY AS VARCHAR)
-- START OF IDENTITY BACKUP
DECLARE @BackupIdentitySeederFunc VARCHAR(1000)
SET @BackupIdentitySeederFunc =
'DECLARE @BackupIdentity TABLE
(IdentityID INT IDENTITY('+CAST(@@IDENTITY AS VARCHAR)+', 1))
INSERT @BackupIdentity DEFAULT VALUES'
-- END OF IDENTITY BACKUP
INSERT @example DEFAULT VALUES
PRINT 'New Identity = '+CAST(@@IDENTITY AS VARCHAR)
INSERT @example DEFAULT VALUES
PRINT 'Another New Identity = '+CAST(@@IDENTITY AS VARCHAR)
-- RETRIEVE ORIGINAL IDENTITY
EXEC (@BackupIdentitySeederFunc)
PRINT 'Back to Original Identity = '+CAST(@@IDENTITY AS VARCHAR)
We have ending up with this solution because each of the other options
suggested cause no end of problems for us... and it is a simple patch
to our triggers that should work even if ADO behaviour changes in the
future.
GUIDs would probably be the most flexible solution - but they do have
a substantial performance and size cost - for performance, both in
INSERTs and in SELECTs, as their randomness makes the indexes
inefficient. I found one document that had a workaround for this
performance cost - but I don't know how safe the method described is:
www.informit.com/articles/printerfriendly.asp
You would probably also need to use GUIDs from the beginning of a
project, as the changeover cost would be very high.
I hope that one of these ideas might help resolve the issue for you.
 

Re:Identity error?? Please help

XXXX@XXXXX.COM (Marc Durdin) writes news:<XXXX@XXXXX.COM>...
Quote
"Andre Greyling" <XXXX@XXXXX.COM>writes news:<4085148d$XXXX@XXXXX.COM>...
>Thanks for the help - yes the problem is @@IDENTITY related. It is still
>strange how Delphi behaves though. The code is simply:
>(1) qry1.append;
>(2) qry1.fieldbyname('ID').asinteger
>
>qry1 does a simple SELECT on table Master. Master has a trigger (on INSERT)
>which creates detail tables
>each table has an identity column.
>line (2) then returns the identity value of the last detail table - odd !
>
>However, what is the best way to create a master record and retrieve it's
>identity value ? Should the code be in a stored procedure or is there a
>simpler way ?
>Any ideas ?
>
>Bye

It sounds like you've run up against the ADO bug of the year... ADO
uses @@IDENTITY to retrieve the last inserted identity value after a
Post, when it should be using SCOPE_IDENTITY(). There has been much
discussion on whether this issue is a 'bug' as it works as
documented... But if it is not a bug, then it is certainly an
extremely serious design flaw, IMHO. I have seen various workarounds
listed online, none of which really solve the problem:

1. Don't use triggers.

2. Always use stored procedures for INSERTs and UPDATEs.

3. Don't use IDENTITY columns

4. Microsoft recommends: You could implement a business object that
dispenses "identity" column information directly to your application.
This dispenser should be free-threaded, in case multiple applications
are using it, and it probably needs to work with clients across a
network. In this case, a Microsoft Transaction Server implementation
would work the best. (KB article 195910)

5. Use GUIDs in place of IDENTITY

The scary and ugly solution that we are using (because it just
*works*) is to save the @@IDENTITY value on entry to a trigger and
restore it at the end. This is not a new idea - similar solutions
have been used with SQL Server 7.0 in the past to work around the lack
of a SCOPE_IDENTITY() function. For example:

DECLARE @example TABLE (ii INT IDENTITY (5,23))

INSERT @example DEFAULT VALUES
PRINT 'Initial Identity = '+CAST(@@IDENTITY AS VARCHAR)

-- START OF IDENTITY BACKUP

DECLARE @BackupIdentitySeederFunc VARCHAR(1000)
SET @BackupIdentitySeederFunc =
'DECLARE @BackupIdentity TABLE
(IdentityID INT IDENTITY('+CAST(@@IDENTITY AS VARCHAR)+', 1))
INSERT @BackupIdentity DEFAULT VALUES'

-- END OF IDENTITY BACKUP

INSERT @example DEFAULT VALUES
PRINT 'New Identity = '+CAST(@@IDENTITY AS VARCHAR)

INSERT @example DEFAULT VALUES
PRINT 'Another New Identity = '+CAST(@@IDENTITY AS VARCHAR)

-- RETRIEVE ORIGINAL IDENTITY

EXEC (@BackupIdentitySeederFunc)
PRINT 'Back to Original Identity = '+CAST(@@IDENTITY AS VARCHAR)

We have ending up with this solution because each of the other options
suggested cause no end of problems for us... and it is a simple patch
to our triggers that should work even if ADO behaviour changes in the
future.

GUIDs would probably be the most flexible solution - but they do have
a substantial performance and size cost - for performance, both in
INSERTs and in SELECTs, as their randomness makes the indexes
inefficient. I found one document that had a workaround for this
performance cost - but I don't know how safe the method described is:
www.informit.com/articles/printerfriendly.asp

You would probably also need to use GUIDs from the beginning of a
project, as the changeover cost would be very high.

I hope that one of these ideas might help resolve the issue for you.
You can try EurekaLog (www.eurekalog.com).
EurekaLog is an add-in tool that gives to your application (GUI,
Console, Web, etc.) the ability to catch every exception (even those
raised by memory leaks) and every infinite-loops/deadlock bugs,
generating a detailed log of call stack (with unit, class, method and
line #), showing and sending it back to you via email.
Best regards...
Fabio Dell'Aria.