Re: [DUG]: Generators - Sequences, and Identities

1999-11-03 Thread Neven MacEwan

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

1999-11-03 Thread Tony Blomfield

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

1999-11-03 Thread Neven MacEwan

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

1999-11-03 Thread Nic Wise



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

1999-11-03 Thread Tony Blomfield

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

1999-11-03 Thread Chris Reynolds

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

1999-11-02 Thread Julien Thomas

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

1999-11-02 Thread Tony Blomfield

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

1999-11-02 Thread Julien Thomas

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