[ I moved this to 'database' too ]
> OK. Thats what I meant by an increment table. Do you experiance any
deadlock
> issues with this idea?
As long as you don't access the table inside a transaction, and don't care
if you lose the odd sequence number, you should be fine.
Implement something like this (except make it syntactically correct):
create table incrementTable( keyid : varchar, nextValue : integer );
function getNextSequence( keyid : String ) : integer
var originalValue : integer;
newValue : integer;
begin
repeat
with Query1 do
begin
SQL.Add('select nextValue from incrementTable
where keyid = :keyid');
Params[0].AsString := keyid;
Open;
originalValue := fieldByName('nextValue');
newValue := originalValue + 1;
SQL.Clear;
SQl.Add('update incrementTable
set nextValue = :newValue
where keyid = :keyid
and nextValue = :originalValue');
ParamsAsAppropriate :-)
ExecSQL;
// See if the update worked
until Query1.RowsAffected = 1;
result := newValue;
end;
Cheers,
Kerry S
PS: That's about right, isn't it Greg?
-----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
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
