Title: Again a Unique Key Generating Function..:-)
Question: The problem with generating a unique key is caused by the fact, that record locking is inadequate in databases like Access and MS SQL Server. Directly using the autoincrement function of the tables gives other problems.
Answer:
Many ways of generating a unique key have been discussed on this site, but none was good enough for an database application (ADO and SQL Server), in which I needed a very near '100 %' safe key generator. So here is another key generating function.
The concept is very simple:
Use a table (here named KeyGen) with two fields: PrimKey,Autoincrement and UserName,Text,50. Whenever you need a key you add a record in the table (providing the UserName). The new record has an unique key. Read back the key in that record (with the UserName). After that assign "VOID" to the UserName field of that record.
Use an ADOCommand (here named Ado_KeyComm) component for manipulating the Keygen table and ADOQuery (here named AQ_KeyGen) component to read the key.
However, don't access the KeyGen table for every single key to generate. The performance will be far better by re-using the generated tablekey (here named KeyMain) 10,100 or even 1000 times (here named KeySequence times) by using a counter (here named KeyCounter) incrementing from 0 to KeySequence-1 :
Result := KeyMain * KeySequence+ KeyCounter;
Inc(KeyCounter,1);
This method results in a less efficient use of the 'key-space'. So stay at the lower site (10 is safe and sound) with KeySequence!
To keep the KeyGen table small, delete the redundant ("VOID") records behind you. It is important to do this before the field UserName of the last read record is changed to "VOID". The autoincrement function of an empty table is reset to 0 at the moment that - in Access terminology - the database is 'repaired and compacted'. This sequence deleteing the redundant records before the last read record (of this user) is changed to VOID guarantees that there will allways be at least one record in the table.
In order to cope with the situation with users having the same UserName, the MS Windows UserName should be extended with a variable part. I use here the string FormatDateTime('YYYYMMDDHHMMSS',now) as extension, but this is only an example. The result is here named SessionName
Everything happens in the function KEYGEN in the MainUnit. In the Tmainform.FormCreate(Sender: TObject) the KeyCounter is set to KeySequence, so a new KeyMain will be generated after startup of the application.
Mainform:
=========
cons
KeySequence = 10 ;
var
KeyCounter, KeyMain : integer ;
SessionName, UserName: string;
procedure Tmainform.FormCreate(Sender: TObject);
begin
with MainUnit do
begin
UserName := MygetUserName ;
KeyCounter := KeySequence;
KeyMain := 0 ;
SessionName := UserName + FormatDateTime('YYYYMMDDHHMMSS',now) ;
end ;
end ;
function MyGetUserName: string;
var
Len: Cardinal;
begin
Len := 255;
SetLength(Result, Len - 1);
if GetUserName(PChar(Result), Len)
then SetLength(Result, Len - 1)
else result := 'LocalUser';
end;
function genkey: integer;
begin
if MainUnit.KeyCounter = MainUnit.KeySequence then
try
DataForm.Ado_KeyComm.commandtext :=
'insert into keygen (UserName) values ("' + MainUnit.SessionName + '")';
DataForm.Ado_KeyComm.execute;
DataForm.Ado_KeyComm.commandtext :=
'delete from keygen where UserName = "VOID"';
DataForm.Ado_KeyComm.execute;
DataForm.Aq_KeyGen.open;
DataForm.Aq_KeyGen.locate('UserName', MainUnit.SessionName, []);
KeyMain := DataForm.Aq_KeyGen.fieldbyname('PrimKey').asinteger;
DataForm.Aq_KeyGen.close;
DataForm.Ado_KeyComm.commandtext :=
'update keygen set UserName = "VOID" where UserName = "' +
MainUnit.SessionName +'"' ;
DataForm.Ado_KeyComm.execute;
MainUnit.KeyCounter := 0;
except
showmessage('Fatal: Key Generation Error');
mainform.close;
end;
result := (MainUnit.KeyMain * MainUnit.KeySequence) + MainUnit.KeyCounter ;
inc(MainUnit.KeyCounter, 1)
end;