Adding indexes to existing dBase table

[Adam]

I'm relatively new to Delphi here and could use any help I can get.

I've got a dBase table on which I need to do some complex filtering.
In order to do the filtering I have to create indexes on two fields,
NOT index expressions. I have been trying several ideas and am
running into errors. I have been searching newsgroups and have been
unable to find anything that really explains it in depth and I seem to
be repeatedly bashing my head against a brick wall... From what I've
read it seems that the TTable.AddIndex procedure is the only way to
add an index to an existing dBase table without an error but I keep
getting the error 'Index Name Required.' from the following lines of
code

AddIndex('AUTHIND', 'AUTHOR', []);
AddIndex('DATEIND', 'DATE', [], '');


The table is closed, readonly is false...

I'm using Delphi 5 and an old dBase table, not sure what version.

Thanks in advance for any help

Adam

[Markku Nevalainen]

Post by Adam
AddIndex('AUTHIND', 'AUTHOR', []);
AddIndex('DATEIND', 'DATE', [], '');

You did not mention anything about your DB Engine. dBase tables can
be accessed with multiple engines. But I'll assume you use BDE.

I _strongly_ suggest that you do not use SQL nor Delphi Reserved
words as your Field names.
So rename 'DATE' field to something like 'DATE1', 'PURCHDATE' or
something.

Anyways, this should do it, add the wanted two indexes:

procedure TForm1.Button2Click(Sender: TObject);
Var
aTable : TTable;
begin
aTable := TTable.create(nil);
aTable.TableName := 'C:\TEST.DBF';
try
with aTable do
begin
AddIndex('AUTHIND', 'AUTHOR', []);
AddIndex('DATEIND', 'PURCHDATE', []);
end;
finally
aTable.Free;
end;
end;

If you have any TTable components on your Form, pointing to that
DBF table, all these TTable components must of course have
Table.Active =False.

Markku Nevalainen.

[Adam]

Post by Markku Nevalainen

Post by Adam
AddIndex('AUTHIND', 'AUTHOR', []);
AddIndex('DATEIND', 'DATE', [], '');

You did not mention anything about your DB Engine. dBase tables can
be accessed with multiple engines. But I'll assume you use BDE.
I _strongly_ suggest that you do not use SQL nor Delphi Reserved
words as your Field names.
So rename 'DATE' field to something like 'DATE1', 'PURCHDATE' or
something.
procedure TForm1.Button2Click(Sender: TObject);
Var
aTable : TTable;
begin
aTable := TTable.create(nil);
aTable.TableName := 'C:\TEST.DBF';
try
with aTable do
begin
AddIndex('AUTHIND', 'AUTHOR', []);
AddIndex('DATEIND', 'PURCHDATE', []);
end;
finally
aTable.Free;
end;
end;
If you have any TTable components on your Form, pointing to that
DBF table, all these TTable components must of course have
Table.Active =False.
Markku Nevalainen.

That is essentially what I've got. I'm using the BDE and I've changed
the field name to 'DATE1', still with no success. I hope I'm not just
missing something stupid. Let me try explain a little more what I'm
trying to do... I have an existing table(.dbf) which the user opens
using an OpenDialog. I use a session and a database component since
the table is accessed via multiple forms. This function initializes
the session, table, database, and datasource.



procedure TForm1.LoadDBaseExecute(Sender: TObject);
var
FName: string;
index, nameLength: integer;
begin
if (Form1.OpenDialog1.Execute = True) then
begin
FileDir := Form1.OpenDialog1.FileName;
FileName := ExtractFileName(FileDir);
nameLength := Length(FileDir);
index := Pos('.', FileName);
Form5.Table1.Active := False;
Form5.Table1.TableName := Copy(FileName, 0, index-1);
Form5.DataSource1.DataSet := Form5.Table1;

Form5.Table1.TableType := ttDBase;
Form5.LitDBase.DatabaseName := 'dBASE Files';

Sessions.OpenSession('LitSession');
Form5.Session1.AddPassword('');
Form5.LitDBase.LoginPrompt := False;
Form5.Table1.DatabaseName := Form5.LitDBase.DatabaseName;
Form5.LitDBase.KeepConnection := True;
Form5.LitDBase.Open;
end;
end;


The following function is called when the user clicks a button and it
crashes on the line indicated. I've tried several variations of the
line with no results.. I've also tried using the TTable.IndexDefs
property with no success.


procedure TForm5.CreateTableIndices;
begin
try
with Table1 do
begin
ReadOnly := False;
Active := False;
AddIndex('AUTHIND', 'AUTHOR', []); <--- Crashes here with error
{ EDBEngineError with message 'Index name required.'. ... }
AddIndex('DATEIND', 'DATE1', []);

Active := True;
end;
finally
end;
end;


Thanks again for your help

[Markku Nevalainen]

Post by Adam
procedure TForm5.CreateTableIndices;
begin
try
with Table1 do
begin
ReadOnly := False;
Active := False;
AddIndex('AUTHIND', 'AUTHOR', []); <--- Crashes here with error
{ EDBEngineError with message 'Index name required.'. ... }
AddIndex('DATEIND', 'DATE1', []);
Active := True;
end;
finally
end;
end;

You said you don't know what version of dBase tables you currently
have.

I suggest you create a totally new, simple TestProject. Then use your
Database Desktop or Database Explorer to create a new dBase table, with
only those two Fields, AUTHOR and DATE, and no indexes in it.

Then start testing with the example above, it should work.
If it works, then the problem maybe has something to do with your original
Table's version level, or maybe with the existing Indexes in it.

Markku Nevalainen

[Rick Carter]

Post by Adam
the table is accessed via multiple forms.

And that may be your problem. The BDE must be able to place
an exclusive lock on the table before doing any resturcturing,
and I think that includes adding indexes. See online help
for TTable.Exclusive. Make sure no other session has the table
open. As Mark mentioned, that includes your own form in design
mode in the IDE.

Try using opening your table in Database Desktop, then
selecting Table-Restructure. That will show you, in the
title bar of the window, what the table level of your
table is. Try interactively adding the indexes, and see
if you get any error messages.

Rick Carter
Chair, Paradox/Delphi SIG, Cinicinnati PC Users Group

[Adam]

Post by Rick Carter

Post by Adam
the table is accessed via multiple forms.

And that may be your problem. The BDE must be able to place
an exclusive lock on the table before doing any resturcturing,
and I think that includes adding indexes. See online help
for TTable.Exclusive. Make sure no other session has the table
open. As Mark mentioned, that includes your own form in design
mode in the IDE.

I have a session created through which the table and the database are
accessed. The table is accessed by multiple forms BUT only one form
at a time and I can still set it to exclusive and inactive to add
indexes but... no jones. Do I need to do something with the session
or database before I try adding indexes??

Post by Rick Carter
Try using opening your table in Database Desktop, then
selecting Table-Restructure. That will show you, in the
title bar of the window, what the table level of your
table is. Try interactively adding the indexes, and see
if you get any error messages.
Rick Carter
Chair, Paradox/Delphi SIG, Cinicinnati PC Users Group

I tried using restructure in the Database Desktop but that still gives
me the error message 'invalid index descriptor'. The table is a dBase
for Windows table. I've also tried creating a new table (dBase III+,
dBase IV, Visual dBase) with only the two fields 'AUTHOR' and 'DATE1',
all with the same results when I try adding indexes ('Invalid index
descriptor'). For each of those I tried using the Database Desktop to
add the indexes as well as programmatically using the AddIndex()
procedure..... nothing.

There's one thing that looks like it might work for me. I created a
new table with a seperate field (unique number) which I have been able
to create an index for. If that doesn't work I'll be back with more
questions.

Thanks again for all your help and suggestions.


Adam
CEng student

[David McCallum]

I've always found BDE terribly unreliable with DBase table, especially where
indices are involved.

In fact to the point I only ever use BDE to read DBase tables.

Best tool I use is Apollo (www.vistasoftware.com). Extremely fast and
reliable with their VCL engine.

David McCallum

Post by Adam

Post by Rick Carter

Post by Adam
the table is accessed via multiple forms.

And that may be your problem. The BDE must be able to place
an exclusive lock on the table before doing any resturcturing,
and I think that includes adding indexes. See online help
for TTable.Exclusive. Make sure no other session has the table
open. As Mark mentioned, that includes your own form in design
mode in the IDE.

I have a session created through which the table and the database are
accessed. The table is accessed by multiple forms BUT only one form
at a time and I can still set it to exclusive and inactive to add
indexes but... no jones. Do I need to do something with the session
or database before I try adding indexes??

Post by Rick Carter
Try using opening your table in Database Desktop, then
selecting Table-Restructure. That will show you, in the
title bar of the window, what the table level of your
table is. Try interactively adding the indexes, and see
if you get any error messages.
Rick Carter
Chair, Paradox/Delphi SIG, Cinicinnati PC Users Group

I tried using restructure in the Database Desktop but that still gives
me the error message 'invalid index descriptor'. The table is a dBase
for Windows table. I've also tried creating a new table (dBase III+,
dBase IV, Visual dBase) with only the two fields 'AUTHOR' and 'DATE1',
all with the same results when I try adding indexes ('Invalid index
descriptor'). For each of those I tried using the Database Desktop to
add the indexes as well as programmatically using the AddIndex()
procedure..... nothing.
There's one thing that looks like it might work for me. I created a
new table with a seperate field (unique number) which I have been able
to create an index for. If that doesn't work I'll be back with more
questions.
Thanks again for all your help and suggestions.
Adam
CEng student