The discussion so far has brought up 2 issues for me -
Why not use GUIDs
Size
Difficulty in manual SQL manipulation
Lack of a directly mapable datatype in Delphi
Unavailability in some databases - we interface to Ingres
Why use integers?
Habit
Sequencing the order of row creation
Size
The suggestion that Gary has put forward is exactly what I was thinking of,
but I still don't see how to "remember" rolled back ID's.
The main problem is that if I use a single row table as the basis for the
generator, a rollback will reset the row back to the state before the start
of the operation, regardless of the fact that the row has been used to
generate new ID's by other processes in the meantime. This would mean that
any IDs generated after this point may duplicate an ID generated during the
rolled back transaction. Locking the row until the transaction completes
would introduce an unacceptable delay. I have not heard of anyway of
exempting a table update from the current transaction, but that is really
what I need to do.
Of course GUIDs are the answer if I can find a simple way of handling these
in Ingres and am prepared to type in huge banks of guff in SQL when tracing
rows. I'll think about it.
Stephen
-----Original Message-----
From: Gary T. Benner [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 11 January 2001 3:48 p.m.
To: Multiple recipients of list delphi
Subject: RE: [DUG]: [DUG-DB]: Unique IDs
[Reply]
Your requirement needs to have a process that is not available in most
databases. You need some logic to not only give you a
sequential number, but to also remember "roll backed" ID's and issue those
before generating new ID's.
This usually requires the creation of a pool of available ID's, and a
process to issue them to calling processes, and to have the issue
confirmed (after a commit), or returned to the pool (after a roll back).
You can do this primarily in the database (stored proc), as part of a
middle tier, or in your client as appropriate.
A stored procedure returning an ID value sounds the best option. It could
contain the code to housekeep the overall process.
Good Luck.
BTW, I am curious as to why the ID's have to be sequential. As a general
rule, primary keys in databases (sometimes called surrogate
keys) should have no meaningful value, only be unique. If you wish to have
some meaning associated with data, then it should be a
separate field, eg invoice number in a financial application, where it is
obvious that the number must be unique, and generally sequential
- although in some cases not essential.
If you are using your unique ID for some process (such as replication or
briefcasing), then would it not be better to have the relevant
process deal with gaps in the sequential number rather than insist upon it
in the first place?
BTW, rules are meant to be broken, and if there is a good reason for the
uniqueness, and sequentiality, then certainly go down the
track you are.
Kind Regards
Gary
At 15:29 on 11/01/2001 Stephen wrote
>To :
>CC :
>From: Stephen Bertram, [EMAIL PROTECTED]
>I need a bulletproof routine to create unique integer IDs for inserting
rows
>into multiple tables in a MSSQL 7 database.
>
>The catches are that I want the ID's to be unique in the database, not just
>each table, and the generator must be available to stored procedures.
>
>Also the generator needs to unaffected by transactions - rollbacks should
>not reset the sequence of values - and the generator must not be locked by
a
>slow transaction.
>
>Any bright ideas?
>
>Stephen
>
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
