Thanks for the effort Neven...
Na not flustered at all, but definatly grateful for the dialog, and just a
tad overwhelmed by the sheer volume.
1 Firstly The @@Identity is local to a connection so is MU safe
OK A good answer to question, but note Nics answer on this one as well. It
is the 6.5 issue that caused my doubt in this area for 7. So I assume you
know for fact that this is fixed for 7?
Re Question 2 Indentity Column V Increment Table.
I think we are having circular references... If you scroll down to the
bottom, my original question sort of asked if folk used an increment table,
or Identities. Really what you are proposing is an increment table solution,
but I must admit, I hadn't considered the implementation with a Stored Proc.
I must say I find that idea more appealing than the delphi code support I
have given it previously. Thanks for the Idea. I'l give it a shot.
Dunno why I didn't think of this myself. Stuck in a rut I guesse.
Appologies to the group for so many messages on this one issue, but for me
this one is pretty important to get right.
Cheers...
-----Original Message-----
From: Neven MacEwan <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Date: Thursday, 4 November 1999 12:25
Subject: Re: [DUG]: Generators - Sequences, and Identities
Tony
Take a deep breath you seem to be getting flustered
Secondly Generator are simply a specific case of a stored procedure so try
this. (This is non syntax checked SQL so you'll have to debug it)
CREATE TABLE TableKeyGenerator
(
LastKeyValue INT NOT NULL IDENTITY,
CONSTRAINT PK_TableKeyGenerator PRIMARY KEY NONCLUSTERED
(LastKeyValue)
)
CREATE Proc GetTableNextKey AS
INSERT INTO TableKeyGenerator (LastKeyValue) VALUES (NULL)
RETURN(@@Identity)
You then EXEC GetTableNextKey to get the ID (as per a generator)
This could also be done creating a table to hold all the next keys ie
CREATE TABLE TableSurrogateKeys
(
Table
Name CHAR(30) NOT NULL,
LastKeyValue INT NOT NULL,
CONSTRAINT PK_TableSurrogateKeys PRIMARY KEY NONCLUSTERED
(TableName)
)
CREATE Proc GetTableNextKey @TableName char(30)
AS
DECLARE
@LastSurrogateKey INTEGER,
@SurrogateKey INTEGER,
@Sucess INTEGER
-- Check to see if an entry exists for this table
IF NOT EXIST SELECT 1 FROM TableSurrogateKeys WHERE TableName = @TableName
BEGIN
INSERT INTO TableSurrogateKeys (TableName, LastKeyValue) VALUES
(@TableName, 0)
END
SELECT @Sucess=0
WHILE @Success <> 0
BEGIN
BEGIN TRANSACTION
SELECT @LastSurrogateKey = LastKeyValue FROM TableSurrogateKeys WHERE
TableName = @TableName
UPDATE TableSurrogateKeys SET LastKeyValue = LastKeyValue+1 WHERE
TableName = @TableName
SELECT @SurrogateKey = LastKeyValue FROM TableSurrogateKeys WHERE
TableName = @TableName
SELECT @Sucess = @SurrogateKey - @LastSurrogateKey -1
IF @Success <> 0 ROLLBACK
--Random pause
END
COMMIT
RETURN(@SurrogateKey)
This is off the top of my head but hope it helps
Regards Neven
N.K. MacEwan B.E. E&E
----- Original Message -----
From: Tony Blomfield <tonyb@ipro
link.co.nz>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Thursday, 4 November 1999 08:33
Subject: Re: [DUG]: Generators - Sequences, and Identities
> Quite simply the question was/is... Can @@identity be relied on?
>
> and also a statement of shock that generators dont exist in SQL Server 7!
> which means an entirely different design approach to Interbase, Oracle.
> -----Original Message-----
> From: Chris Reynolds <[EMAIL PROTECTED]>
> To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> Date: Thursday, 4 November 1999 06:58
> Subject: RE: [DUG]: Generators - Sequences, and Identities
>
>
> we use @@identity all the time without problems so i am slightly confused
by
> the diatribe. What exactly are you missing?
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of Tony Blomfield
> Sent: Wednesday, November 03, 1999 3:47 PM
> To: Multiple recipients of list delphi
> Subject: [DUG]: Generators - Sequences, and Identities
>
>
> Unless I have missunderstood, SQL7 has no equivolent of Interbases
> generators, or Oracles Sequences? Instead it has the identity data type
> which is akin to Access or paradox Autoincrement columns.
>
> The only time you seem to be able to see what your identity value was/is
is
> immediatly following a insert statement.
>
> Coming form an Interbase background for the last few years, this idea of
> identity columns looks very decadent, and extremely shortsighted of
> MS/Sybase, and really surprised me that it is still like this.
>
> So what about the multi user situation. Can we guarantee that Select
> Identity@@ will allways get the last identity for this specific user?
>
> What a dumb dumb idea. It more or less completely ignores the concept of
> using surrogate keys.
>
> What do others do about this for MSSQL based Delphi Apps? It seems to me
> that the only reasonable approach is to use an increment table, and
> copmpletely abandon the Indentity concept. What are others doing here?
>
> Thanks,
>
> Tony.
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
