Re: [DUG]: Generators - Sequences, and Identities
Tony Glad I could be of help - once I undstood that you need the surogate key before creation the differences become clear - My case tool uses generators to replicate the behaviour of Identiy cols by inserting them in the trigger when you define a col as a 'counter' (for consistency of behaviour i guess) I felt you just needed a little lateral help (re the fact that generastor can be replicated and more powerfully as a stored proc) On this topic I'm coding a table with surrogate key (MSSQL 7 and ADOExpress) so I'll tell you how I get on. Regards Neven N.K. MacEwan B.E. E&E --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: Generators - Sequences, and Identities
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 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
Re: [DUG]: Generators - Sequences, and Identities
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 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
Re: [DUG]: Generators - Sequences, and Identities
Tony Blomfield wrote: > > 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. In 6.5 - no. In 7.0, maybe. 6.5 had a habit of giving back the last identiry generated - which might not be your one, if 2 transactions are going at the same time. N -- Nic Wise - Inprise New Zealand / Brocker Technologies Web Monkey. mob:+64.21.676.418 - wk:+64.9.481. x9753 - wk-em:[EMAIL PROTECTED] hm: +64.9.277.5309 - hm-em:[EMAIL PROTECTED] --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
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
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
RE: [DUG]: Generators - Sequences, and Identities
No - but then we have not tested it with lots of users. We are using Midas and have customised the Dataset Provider to go and get the Unique ID for the table when an insert is made. It works well under these conditions. > -Original Message- > From: Julien Thomas [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, November 03, 1999 3:52 PM > To: Multiple recipients of list delphi > Subject: RE: [DUG]: Generators - Sequences, and Identities > > We use a separate table to maintain the nextID for all the tables. > > > -Original Message- > > From: Tony Blomfield [SMTP:[EMAIL PROTECTED]] > > 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
Re: [DUG]: Generators - Sequences, and Identities
OK. Thats what I meant by an increment table. Do you experiance any deadlock issues with this idea? -Original Message- From: Julien Thomas <[EMAIL PROTECTED]> To: Multiple recipients of list delphi <[EMAIL PROTECTED]> Date: Wednesday, 3 November 1999 3:53 PM Subject: RE: [DUG]: Generators - Sequences, and Identities >We use a separate table to maintain the nextID for all the tables. > >> -Original Message- >> From: Tony Blomfield [SMTP:[EMAIL PROTECTED]] >> 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
RE: [DUG]: Generators - Sequences, and Identities
We use a separate table to maintain the nextID for all the tables. > -Original Message- > From: Tony Blomfield [SMTP:[EMAIL PROTECTED]] > 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
