Tony
Take a deep breath you seem to be getting flustered
Two things that may help
Firstly The @@Identity is local to a connection so is MU safe - I know this
doesn't work in your case.
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
(
TableName 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
