mazzini
asked on
Autoinc key fields in a Paradox multiuser database - URGENT
I have a program that runs on four computers, using the same database. I've set the NetFileDir and PrivFileDir properties to the correct places on the net.
When one station appends a record on a table at the same time another station is appending a record on the same table, both get the same value for the autoincrement field. As it is part of the key, I got a Key Violation.
The AfterPost event of all my tables is calling the FlushBuffers method, and CachedUpdates is False. The sequence to include records is:
- call the tables's Refresh method
- Append
- Post
- FlushBuffers
I'll try to use LockTable (read and write), but I'm not sure it will work. I've heard about the BDE OnChangeNotify, but don't know if it can help.
I need an urgent answer, because I must end this work in two days!
Please help.
Thanks
When one station appends a record on a table at the same time another station is appending a record on the same table, both get the same value for the autoincrement field. As it is part of the key, I got a Key Violation.
The AfterPost event of all my tables is calling the FlushBuffers method, and CachedUpdates is False. The sequence to include records is:
- call the tables's Refresh method
- Append
- Post
- FlushBuffers
I'll try to use LockTable (read and write), but I'm not sure it will work. I've heard about the BDE OnChangeNotify, but don't know if it can help.
I need an urgent answer, because I must end this work in two days!
Please help.
Thanks
ASKER
meikl:
Thanks for answering quickly !!
I'll verify netdir/privatedir, test again and tell what happened.
Your tip of using a separate table to implement an autoinc by myself sounds to be a solution. I'd prefer to use autoinc fields at first, but if I can't get a solution in a short period of time, I'll try your tip.
Thanks again.
Thanks for answering quickly !!
I'll verify netdir/privatedir, test again and tell what happened.
Your tip of using a separate table to implement an autoinc by myself sounds to be a solution. I'd prefer to use autoinc fields at first, but if I can't get a solution in a short period of time, I'll try your tip.
Thanks again.
hi again mazzini,
just a sample, how it could be done, if you do the autoinc byself
(in this case you must restructure your Table(s) to set the autoinc-field to a LargeInteger-field before)
(delphi code here)
//Function for Retrieving the next Value
//the physical table, where
//table2 is pointed to, holds only one record with one integerfield
//initial prefilled with the value 1
//Three tries to open the table exclusive
Function TForm1.Get_Next_Id : Integer;
const Retries = 3;
var
OK : Boolean;
Retry : Integer;
begin
OK := False;
Retry := 0;
Result := -1;
Table2.Exclusive := True; //Open Exclusive
while (not ok) and (Retry < Retries) do
begin
try
Table2.Open;
Result := Table2.Fields[0].AsInteger ;
Table2.Edit;
Table2.Fields[0].AsInteger := Result + 1;
Table2.Post;
Table2.Close;
Ok := True;
except
Inc(Retry);
Sleep(100); //wait a bit, adjustable if to short
end;
end;
If Not ok then
Raise Exception.Create('Problem Getting ID-Value');
end;
//Sample to call
//Just in the BeforePost-event
procedure TForm1.Table1BeforePost(Da taSet: TDataSet);
begin
If Table1.State = dsInsert then
try
Table1.FieldByName('Id').A sInteger := Get_Next_Id;
except
ShowMessage('Insert Failed :-(');
Abort;
end;
end;
just if you need to go this way
meikl
just a sample, how it could be done, if you do the autoinc byself
(in this case you must restructure your Table(s) to set the autoinc-field to a LargeInteger-field before)
(delphi code here)
//Function for Retrieving the next Value
//the physical table, where
//table2 is pointed to, holds only one record with one integerfield
//initial prefilled with the value 1
//Three tries to open the table exclusive
Function TForm1.Get_Next_Id : Integer;
const Retries = 3;
var
OK : Boolean;
Retry : Integer;
begin
OK := False;
Retry := 0;
Result := -1;
Table2.Exclusive := True; //Open Exclusive
while (not ok) and (Retry < Retries) do
begin
try
Table2.Open;
Result := Table2.Fields[0].AsInteger
Table2.Edit;
Table2.Fields[0].AsInteger
Table2.Post;
Table2.Close;
Ok := True;
except
Inc(Retry);
Sleep(100); //wait a bit, adjustable if to short
end;
end;
If Not ok then
Raise Exception.Create('Problem Getting ID-Value');
end;
//Sample to call
//Just in the BeforePost-event
procedure TForm1.Table1BeforePost(Da
begin
If Table1.State = dsInsert then
try
Table1.FieldByName('Id').A
except
ShowMessage('Insert Failed :-(');
Abort;
end;
end;
just if you need to go this way
meikl
hi mazzini,
any results?
just forgot to say, that the netdirfile must be on a shared directory, where all clients have access to it.
meikl
any results?
just forgot to say, that the netdirfile must be on a shared directory, where all clients have access to it.
meikl
ASKER
Hi meikl,
sorry for the silence, but I was busy. :-)
Since I don't have much time for test, I will use your routine in place of all the autoinc fields I'm using now.
After correcting the use of NetFileDir, as you have pointed, I used the sequence with LockTable (read/write) / Refresh / Append / Post / UnlockTable (write/read) and all runs properly, but I don't have time to test it hardly. So, since I don't want to put my neck on it, I will use your routine in place of autoinc fields.
If you post an answer, the points are yours. And, once again, thanks for your tips. They really helped!
Best regards
Mazzini
sorry for the silence, but I was busy. :-)
Since I don't have much time for test, I will use your routine in place of all the autoinc fields I'm using now.
After correcting the use of NetFileDir, as you have pointed, I used the sequence with LockTable (read/write) / Refresh / Append / Post / UnlockTable (write/read) and all runs properly, but I don't have time to test it hardly. So, since I don't want to put my neck on it, I will use your routine in place of autoinc fields.
If you post an answer, the points are yours. And, once again, thanks for your tips. They really helped!
Best regards
Mazzini
hi mazzini,
just to say that the softwaresolution is a workaround,
because if paradox properly configured, then it will be hard
to reproduce your problem, but possible.
if you have further problems (i don't hope this),
then contact me via email
meikl@spektracom.de
or post it in this q
(i let the notification-mark checked here)
well, glad to helped you
good luck again
meikl ;-)
just to say that the softwaresolution is a workaround,
because if paradox properly configured, then it will be hard
to reproduce your problem, but possible.
if you have further problems (i don't hope this),
then contact me via email
meikl@spektracom.de
or post it in this q
(i let the notification-mark checked here)
well, glad to helped you
good luck again
meikl ;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One more time, thanks. I will keep your e-mail, but hope not to annoy you too much ;-)
Mazzini
mazzini@via-rs.net
Mazzini
mazzini@via-rs.net
that would be difficult, because the next autoincrement-value is part of the table header and will be read and increased just as the record will be inserted.
well, its a very short time, where it can be happen,
that two clients retrieved the same autoinc-value.
if it appears often then review the netdir and private-dirsettings
to following rules:
- netdir-entry -> must be char by char the same on each client
- privatedir-entry -> must be physical different for each client
if it appears sometimes,
then i would recommend to do a autoincrement byself, which a seperat table holding one integerfield, which is opened exclusive, read the value and stores the value+1 back.
another solution would be to catch the keyviolation-exception,
waiting some milliseconds and then post again.
hope this helps a bit
meikl