How to create (dynamically) a primary index with more than one field?

[MR]

How to create (dynamically) a primary index with *more than one
field*?

I tried the following:

TTable *table;
table=new TTable(NULL);
table->Active=false;
table->TableType=ttDefault; // uses adequate table-type (e.g.
ttParadox, ttDBase) automatically, dependent on the file-extention
table->TableType=ttParadox;
table->TableName=EditBDETableName->Text;
table->FieldDefs->Clear();
table->FieldDefs->Add("Id", ftString, 10, FALSE);
...
table->FieldDefs->Add("State", ftString, 25, FALSE);
table->IndexDefs->Clear();

and than

table->IndexDefs->Add("", "Id;Remark",
TIndexOptions()<<ixPrimary);

or instead

table->IndexDefs->AddIndexDef()->Name="";
table->IndexDefs->AddIndexDef()->Fields="Id"/*";Remark"*/;

table->IndexDefs->AddIndexDef()->Options=TIndexOptions()<<ixPrimary/*<<ixUnique*/;

and finally

table->CreateTable();
table->Free();

When CreateTable() is executed I got an -exception
'Zahl außerhalb des gültigen Bereichs
Tabelle nicht vorhanden
Tabelle: ...
translated
'Number outside the valid range
Table not available
Table: ...

What did I do wrong?

Thanks,

Michael
I do not get this exception when using

table->IndexDefs->Add("", "Id", TIndexOptions()<<ixPrimary);

, but unfortunatly 'Id' is not sufficent (i.e. there might be more
than one db-entry with the same id)

/* { Indizes der neuen Tabelle definieren }
IndexDefs.Clear;
with IndexDefs.AddIndexDef do begin
Name := '';
Fields := 'First';
Options := [ixPrimary];
end;
*/
// table->IndexDefs->AddIndexDef()->Name="";
// table->IndexDefs->AddIndexDef()->Fields="Id"/*";Remark"*/;
//
table->IndexDefs->AddIndexDef()->Options=TIndexOptions()<<ixPrimary;

// table->IndexDefs->Add("", "Id;Remark",
TIndexOptions()<<ixPrimary/*<<ixUnique*/);

table->CreateTable();
table->Free();
}

[MR]

Because I did not get an answer here and I found a quite
better-fitting group (BDE) I reposted it into this group.

Michael

[Minas]

Create a folder "C:\\TestDBTable" and
try a minimum piece of code.If the code is working
then you can add step by step additional fields in your table

TTable *table;
table=new TTable(NULL);
table->Active=false;
table->TableType=ttDefault;

table->TableType=ttParadox;
table->DatabaseName = "C:\\TestDBTable";
table->TableName= EditBDETableName->Text;

table->FieldDefs->Clear();
table->FieldDefs->Add("Id", ftString, 10, FALSE);

table->FieldDefs->Add("Remark", ftInteger, 0, FALSE);

table->FieldDefs->Add("State", ftString, 25, FALSE);

// ..... add additional fields here .......
table->IndexDefs->Clear();

table->IndexDefs->Add("", "Id;Remark",TIndexOptions()<<ixPrimary <<

ixUnique);
//table->IndexDefs->Add("StateX", "State",TIndexOptions()<<
ixCaseInsensitive);
table->CreateTable();
delete table;

You can use Database Explorer to verify the fields of
table Indices

Best Regards

_Minas Harokopos

------
"Only the virtue's conquests have certainty" Sofokleous Erephyle

<MR> wrote

[MR]

Hello Minas,

thanks for your sample...

Trying around a lot and wondering why your sample does work and mine
did not, I finally found out, that the reason is, when indexing over
more than one field, the fields being index *must* be subsequent.

So inserting a FieldDef between Id and Remark in your sample will
result in the same exception I described.

Is this always the case or is this specific for the data-base type,
i.e. Paradox.

Or is there a way to allow non-subsequent fields being indexed too?

Thanks,

Michael

[MR]

<MR> schrieb:

>Hello Minas,
>
>thanks for your sample...
>
>Trying around a lot and wondering why your sample does work and mine
>did not, I finally found out, that the reason is, when indexing over
>more than one field, the fields being index *must* be subsequent.

It even seems, that it has to start with the very first field of the
record...

Michael

[Minas]

? <MR> wrote

> So inserting a FieldDef between Id and Remark in your sample will
> result in the same exception I described.
>
> Is this always the case or is this specific for the data-base type,
> i.e. Paradox.
>
> Or is there a way to allow non-subsequent fields being indexed too?
>

You are right.Key Fields for the primary index must be consecutive.I tried
the same code
in a .mdb database and I get the same exception. To stop the exception I
used
the other way ,
TFieldDef *fldDef = table->FieldDefs->AddFieldDef();

and I used FieldNo to specify the physical field order.
In this case , though , I got a mess in the fields of indices

TTable *table;
table=new TTable(NULL);
table->Active=false;

table->TableType=ttParadox;
table->DatabaseName = "C:\\TestDBTable";// Database1->DatabaseName;
table->TableName= Edit1->Text;
table->FieldDefs->Clear();
//fields definition
TFieldDef *fldDef = table->FieldDefs->AddFieldDef();
fldDef->Name ="Id";
fldDef->DataType = ftString;
fldDef->Size =10;
fldDef->Required =false;
fldDef->FieldNo =1;

fldDef = table->FieldDefs->AddFieldDef();
fldDef->Name ="OtherFld";
fldDef->DataType = ftString;
fldDef->Size =25;
fldDef->Required =false;
fldDef->FieldNo =3;

fldDef = table->FieldDefs->AddFieldDef();
fldDef->Name ="State";
fldDef->DataType = ftString;
fldDef->Size =25;
fldDef->Required =false;
fldDef->FieldNo =4;

fldDef = table->FieldDefs->AddFieldDef();
fldDef->Name ="Remark";
fldDef->DataType = ftInteger;
fldDef->Size =0;
fldDef->Required =false;
fldDef->FieldNo =2;

table->IndexDefs->Clear();
// TIndexDef *idxDef = table->IndexDefs->AddIndexDef();
// idxDef->Name="";
// idxDef->Fields = "Id;Remark";
// idxDef->Options << ixPrimary;
// indices
table->IndexDefs->Add("", "Id;Remark",TIndexOptions()<<ixPrimary);

table->IndexDefs->Add("StateX", "State",TIndexOptions()<<
ixCaseInsensitive);

table->IndexDefs->Add("OtherX", "OtherFld",TIndexOptions()<<

ixCaseInsensitive);
table->CreateTable();
delete table;

To get right results with no exception, Remark field must be defined next to
Id field definition .

[MR]

>You are right.Key Fields for the primary index must be consecutive.I tried
>the same code in a .mdb database and I get the same exception.

Hmmm? The same in Access?

This is a little bit strange restriction, isn't it?
Is there a reason for this?

Unfortunately in BDS2006 I have not found a way to create a database
by something like a database-explorer to be able to create a
BDE-Paradox-database, like in the former versions? (Perhaps I forgot
to install something or it is a little bit more complicated (or they
really threw it out)? Is this restriction here too?

But I suppose I could live with this need of reordering my fields...

Thanks,

Michael

[Minas]

<MR> wrote:

> Hmmm? The same in Access?
>

In Access I got this only when I set FieldDef->FieldNo property.Otherwise is
working fine.

> by something like a database-explorer to be able to create a
> BDE-Paradox-database, like in the former versions? (Perhaps I forgot
> to install something or it is a little bit more complicated (or they
> really threw it out)? Is this restriction here too?

If you mean Database Desktop (I have it in BCB5 PRO), yes ,the fields must
be consecutive .
I have Turbo C++ Explorer which has only Database Explorer.

From the help: "Paradox:The fields in a key must be contiguous starting with
the first field"
An easy way is to use SQL in Database Explorer(since you haven't DB Desktop)
something like:

create table testTbl(
Id integer,
State varchar(25),
Remark varchar(10),
PRIMARY KEY (Id,Remark)
)

and for additional indices

create index StateX ON testTbl (State)

> But I suppose I could live with this need of reordering my fields...

Sure .Is not a big problem anyway.

[MR]

Thank you,

Michael