Solved

Key violation in Paradox table

Posted on 2001-08-20
27
1,855 Views
Last Modified: 2012-06-27
Hi experts!

I am using a Paradox database with several tables.
One of the tables called Stock.db got a primary index called Stock.px.

Something went wrong in upgrading AND in the back-up sequence and now whenever I try to insert a record into that table I get: Key Violation.

If I disable the primary key (called IDSelf which is an auto-increment field type) and enable it again in Database Desktop I get the same error and why: The new record tries to insert with IDSelf is 1 ! When it should be something like 1180.

How can I restore or create the correct primary key.
I can't delete this table and create a new one, since it has got several relations to other tables.

I am using Delphi 5.0 and BDE 5.1.

Thanks a lot!

RoverM
0
Comment
Question by:roverm
  • 11
  • 9
  • 6
  • +1
27 Comments
 
LVL 4

Expert Comment

by:YodaMage
ID: 6407469
0
 
LVL 3

Expert Comment

by:VSF
ID: 6407480
0
 
LVL 12

Author Comment

by:roverm
ID: 6407484
Hi YodaMage!

Thanks for the link!
However, as far as I can tell without buying the PAQ :> this is not what I want.
I know the way to get the number prior to inserting the record.
My problem is that I can't insert at all!

Is the answer in that question ?
Can you please post it here ?

D'Mzzl!
RoverM
0
 
LVL 3

Expert Comment

by:VSF
ID: 6407505
YOu wrote:
>Something went wrong in upgrading AND in the back-up sequence

Try to avoid performing Backups or upgrades while your delphi program is running.
Close all the possible programs that may be accessing the database before this operations!

If you have any problems with the links I sent before, just go in www.borland.com and search for "Tutil"
or email me.

Hope this helps!
VSF
www.victory.hpg.com.br
victory@e-net.com.br


0
 
LVL 3

Expert Comment

by:VSF
ID: 6407512
You cannot insert because your autoinc engine is broken, use the tutil demo program to solve the problem!

VSF
0
 
LVL 3

Expert Comment

by:VSF
ID: 6407518
You cannot insert because your autoinc engine is broken, use the tutil demo program to solve the problem!

VSF
0
 
LVL 3

Expert Comment

by:VSF
ID: 6407534
Here is the text inside the link proposed by YodaMage
http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=20168409

Question: autonumber problem Date: 08/14/2001 11:09AM PST  
From: pin_plunder
Answer Grade: B  Points: 5
 Email A Friend  
 Ok, I've suppose I have the following table:

IdCustomer || Name || Address || etc, etc, etc
==============================================
autonumber    alpha    alpha
----------------------------------------------

I call datamodule.clientdataset.append;
The user enters all data and then
I call datamodule.clientdataset.applyupdates(-1);

This works just fine. But what happens if I want some label of mine to show the IdCustomer the user
is just entering now. I mean, while the user enters all data I want a label to show the autonumber.
However, this is not as easy as it may seem, because the autonumber, apparenttly is generated when I
call datamodule.clientdataset.applyupdates(-1);

Is there any way to calculate autonumber fields before the user enters data in the new record?

thanks.
paul.  
Accepted Answer
From: YodaMage Date: 08/14/2001 12:07PM PST
Text Below
Question History
Comment
From: kretzschmar  Date: 08/14/2001 11:19AM PST  
for paradox,
there is a (tricky) way to lookup the next generated number without to get it
(you need fileaccess to the table)
(not recommended for multiuser-access)  
Accepted Answer
From: YodaMage  Date: 08/14/2001 12:07PM PST  
Autoinc in Paradox is not good at all. You are better off keeping a table with int values seperate,
then having a global procedure to get value, then inc value in table. This is basically a mimic of a
SQL generator.  
Comment
From: comptebidon81  Date: 08/14/2001 02:01PM PST  
Or may I suggest you to Force a Post when the user starts editing. Your Index will be updated and you
won't have to de anything tricky to get the number...
GunDamn  
Comment
From: pin_plunder  Date: 08/14/2001 07:07PM PST  
I'm sorry to say this but I thought I was the one who was wrong or missed something, but now I clearly
see that the guys who made paradox are not very clever, are they?

thanks all for your help. although there wasn't much to say.
paul.  
Comment
From: kretzschmar  Date: 08/15/2001 01:14AM PST  
> the guys who made paradox are not very clever, are they?

this i would not say, just look at paradox history

its an old desktop-database comes up ~1990 and
was the first multiuser desktop-database on market

Comment
From: rondi  Date: 08/15/2001 03:47AM PST  
You could:
- read the previous record's AutoInc field into variable
  and just add 1 and display it (don't post it)

- or like comptebidon81 hinted, when the user starts
  editing, do an Append followed by a Post (fill in any
  required fields with junk first) and then Edit the
  record (clearing any junk before displaying)

rondi.  
Comment
From: YodaMage  Date: 08/15/2001 07:43AM PST  
rondi- The problem there is the multi-user aspect, and the fact that the autoinc value is not assigned
until the post. Say I enter the app first and start to insert, with 100 being the next logical value
from the previous record of 99. Then you enter the system after me, and do the same action, being 101.
If you post your record first, you will end up 100 with me being 101. With more users and more data
entry, the problem multiplies.

Removeing autoinc fields in favor of an integer field that you manually assign solves the issue as already
said, by simulating a generator. It involves more code, but allows you to assign and increment the count
on any event you like instead of being tied to the post event. You also have the benefit of manually
ajusting the generator if needed.  
Comment
From: pin_plunder  Date: 08/15/2001 07:25PM PST  
thanks.
0
 
LVL 12

Author Comment

by:roverm
ID: 6407572
Hi VSF:

Thanks for the links.
Of course, I never backup or update anything while the app is running! No, this is just a case of very stupid people who like the give me some extra work ;-)

I downloaded all the tools, and see some nice information.
The tutil50.zip contains a dll, but what to do with it ?
The pxrest.exe show table information and table interrogator even more but in none of them I can change the value of the autoinc field to, let's say, 1200.

YodiMage:
Thanks for the post, but as I thought, it is not what I was looking for.
I agree with you that the autoinc sucks, but it's used, and to alter this....I can hear my client scream already :>

D'Mzzl!
RoverM
0
 
LVL 3

Expert Comment

by:VSF
ID: 6407688
The tutil32.dll is a Dll designed to repair Paradox Corrupted tables and indexes.
If correctly used it can fix almost all the problems your Paradox table may present.

Download the Tutil32.dll Delphi usage demo source code from the following link:
http://www.borland.com/devsupport/bde/files/tutil32d.zip
Compile the demo and run it!
The tables will be fixed.

>none of them I can change
>the value of the autoinc field to, let's say, 1200.
Lets make things clear YOU CAN NOT CHANGE THE AUTOINC VALUE
just like that, what the correct Tool will to?
It will fix your autoinc engine and continue to increment from the LAST number it stopped, and not from a value you input.

Hope this helps!
VSF


0
 
LVL 12

Author Comment

by:roverm
ID: 6407868
Hi VSF!

Nope, didn't help!
The tool said that everything verified ok, even a rebuild didn't help.

I don't want to input a value, it was just a figure of speaking, but I know it stopped at 1130, so it should be continuing at 1131.

Any suggestions ?

D'Mzzl!
RoverM
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6408584
hi,

this is the way to adjust the autoinc-value in paradox to any value you want

unit pd_autoinc_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, Db, DBTables;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Edit1: TEdit;
    Table1: TTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure Button1Click(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}


Procedure SetAutoInc(filename : string; Value : Longint);
var
  mystream : tfilestream;
begin
  mystream := tfilestream.create(filename,
              fmOpenWrite + fmShareExclusive);
  try
    mystream.Seek(73, soFromBeginning);
    mystream.Writebuffer(Value, SizeOf(Value));
  finally
    mystream.Free;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  Table1.Close;
  SetAutoInc('C:\Test\Test.db',StrtoInt(edit1.text));
  Table1.Open;
end;

end.


meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6409134
just to say, that the adjustment is needed only once

use as value your last got value (in your case 1130)

meikl ;-)
0
 
LVL 12

Author Comment

by:roverm
ID: 6410943
Hi meikl!

Thanks for your post, but it doesn't seem to work ! Still got a key violation....
Any ideas ?

D'Mzzl!
RoverM
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 6411226
then your problem may not the autoincvalue,
maybe a relation or a unique secondary index
0
 
LVL 12

Author Comment

by:roverm
ID: 6412320
meikl:
I am not using any secondary indexes !

Maybe I am doing something wrong with your code.
I emailed the table+index to the address in your profile.
Can you take a look ?

D'Mzzl!
RoverM
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6412366
well, i will take a look to this at this evening
(in ~10 hours from now, sorry can't do it earlier)
0
 
LVL 12

Author Comment

by:roverm
ID: 6412422
I'll wait patiently ;-)
Thanks so far!!

D'Mzzl!
RoverM
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 200 total points
ID: 6414371
well,

i sent you this mail back:

hi roverm,
attached is your repaired table,
i was able to insert a record.
i have deleted it afterwards

your autoinc value stands on 1129,
the next new would have become 1130,
which already exists.

i have it repaired with the code,
which i have posted in ex-ex

your next new record will become 1132

hope the table works now also for you

i used this code:

Procedure SetAutoInc(filename : string; Value : Longint);
var
  mystream : tfilestream;
begin
  mystream := tfilestream.create(filename,
              fmOpenWrite + fmShareExclusive);
  try
    mystream.Seek(73, soFromBeginning);
    mystream.Writebuffer(Value, SizeOf(Value));
  finally
    mystream.Free;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  SetAutoInc('d:\roverm\Voorraad.DB',1130);
end;

meikl ;-)

0
 
LVL 12

Author Comment

by:roverm
ID: 6416427
Hi meikl!

Thanks! It works now!

I don't know what I did wrong, but who cares now ;-)

Now I just have to do this at the office...

One question: How did you see at what number the autoinc value was (1129) ?

D'Mzzl!
RoverM
0
 
LVL 12

Author Comment

by:roverm
ID: 6416429
Everybody else: Thanks for your efforts!
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6416792
glad to helped you, roverm :-)

about
>How did you see at what number the autoinc value was (1129) ?

i just dropped the index on a save-copy,
and inserted then a new record,
to see which value becomes the auotinc -> 1130

good luck again

meikl ;-)
0
 
LVL 12

Author Comment

by:roverm
ID: 6416836
Ok!

Just wondering how this problem occured ?! It want to prevent it next time. Any ideas ?

D'Mzzl!
RoverM
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6416994
>Just wondering how this problem occured ?!
>It want to prevent it next time. Any ideas ?

well, its really mysterious and
i myself never heard about this

(the code i gave i usually used
to reset the autoinvalue to zero
after emptying a table)

the only thing could be that during the
backup-process the last record is inserted,
just after the backup-software read the begin of the file
or it may a caching problem of the os so that the fileheader was not written to hd whereas the backup-process read physically

but this are only shootings in the dark,
don't know why this happened

meikl ;-)
0
 
LVL 12

Author Comment

by:roverm
ID: 6417021
My guess is that during an insert something went wrong in the commit.
I don't know how else to explain this...

Thanks for all your help ! I'm going to use your solution tomorrow at the office. We'll see what happens ;-) !

D'Mzzl!
RoverM
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6417748
well, i wish you good luck there :-)
0
 
LVL 12

Author Comment

by:roverm
ID: 6419609
meikl:
Couldn't resist to try, so I went tonight...

You never believe how I solved the problem (with your findings):
I deleted the last record, which had an IDSelf of 1130 and added a new one. It worked....The new record was added with an IDSelf of 1130.

You were right (again), the BDE tried to insert the new record with an autoinc value of 1130, which already existed!

Thanks for your help!

D'Mzzl!
RoverM

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6421076
:-)) a good workaround

well, glad thats now solved

good luck again

meikl ;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now