Delphi AddIndex to Memory Table

[JimGilmour]

I am updating a Delphi App that used Paradox Tables to now use NexusDb Tables.
In Paradox I used a lot of Private Tables for Temporary storage and have emulated this using NexusDb memory Tables.
However, I used a lot of BatchMoves (which I have working OK) and then an AddIndex to add any Indexes (usually a primary)
This works fine the first time I use the table but upon any subsequent uses of the table AddIndex gives me an 'Index already Exists Error'
A cant seem to find the equivilent of an IndexExists command. Does anybody know how to test if an Index alreay exists ?

Jim

Have you tried

"nxMemTable1.Active := False;"

before using it again?

On 19.06.2012 5:42 AM, JimGilmour wrote:
> I am updating a Delphi App that used Paradox Tables to now use NexusDb
> Tables.
> In Paradox I used a lot of Private Tables for Temporary storage and have
> emulated this using NexusDb memory Tables.
> However, I used a lot of BatchMoves (which I have working OK) and then
> an AddIndex to add any Indexes (usually a primary)
> This works fine the first time I use the table but upon any subsequent
> uses of the table AddIndex gives me an 'Index already Exists Error'
> A cant seem to find the equivilent of an IndexExists command. Does
> anybody know how to test if an Index alreay exists ?
>
> Jim
>
>

Here is an Example:

EmpMem.IndexDefs.Clear;
EmpMem.FieldDefs.Clear;
EmpMem.FieldDefs.Add('Department', ftString, 25);
EmpMem.FieldDefs.Add('Name', ftString, 25);
EmpMem.FieldDefs.Add('Capacity', ftFloat);
for i := StartDt to EndDt do EmpMem.FieldDefs.Add(DateToStr(i), ftFloat);
with EmpMem.IndexDefs.AddIndexDef do begin
Name := 'DeptIx';
Fields := 'Department';
end;
EmpMem.IndexName := 'DeptIx';
EmpMem.MasterSource := CapMemSc;
EmpMem.MasterFields := 'Department';
CapMem.Active := True;
for i := 0 to CapMem.Fields.Count - 1 do begin
if CapMem.Fields[i].DataType = ftFloat then begin
(CapMem.FieldByName(CapMem.Fields[i].FieldName) as
TNumericField).DisplayFormat := '0.00';
end;
end;

EmpMem.Active := True;
On 19.06.2012 5:42 AM, JimGilmour wrote:
> I am updating a Delphi App that used Paradox Tables to now use NexusDb
> Tables.
> In Paradox I used a lot of Private Tables for Temporary storage and have
> emulated this using NexusDb memory Tables.
> However, I used a lot of BatchMoves (which I have working OK) and then
> an AddIndex to add any Indexes (usually a primary)
> This works fine the first time I use the table but upon any subsequent
> uses of the table AddIndex gives me an 'Index already Exists Error'
> A cant seem to find the equivilent of an IndexExists command. Does
> anybody know how to test if an Index alreay exists ?
>
> Jim
>
>

Forgot first line :

EmpMem.Active := False;
EmpMem.IndexDefs.Clear;
EmpMem.FieldDefs.Clear;
EmpMem.FieldDefs.Add('Department', ftString, 25);
EmpMem.FieldDefs.Add('Name', ftString, 25);
EmpMem.FieldDefs.Add('Capacity', ftFloat);
for i := StartDt to EndDt do EmpMem.FieldDefs.Add(DateToStr(i), ftFloat);
with EmpMem.IndexDefs.AddIndexDef do begin
Name := 'DeptIx';
Fields := 'Department';
end;
EmpMem.IndexName := 'DeptIx';
EmpMem.MasterSource := CapMemSc;
EmpMem.MasterFields := 'Department';
CapMem.Active := True;
for i := 0 to CapMem.Fields.Count - 1 do begin
if CapMem.Fields[i].DataType = ftFloat then begin
(CapMem.FieldByName(CapMem.Fields[i].FieldName) as
TNumericField).DisplayFormat := '0.00';
end;
end;

EmpMem.Active := True;

On 19.06.2012 5:42 AM, JimGilmour wrote:
> I am updating a Delphi App that used Paradox Tables to now use NexusDb
> Tables.
> In Paradox I used a lot of Private Tables for Temporary storage and have
> emulated this using NexusDb memory Tables.
> However, I used a lot of BatchMoves (which I have working OK) and then
> an AddIndex to add any Indexes (usually a primary)
> This works fine the first time I use the table but upon any subsequent
> uses of the table AddIndex gives me an 'Index already Exists Error'
> A cant seem to find the equivilent of an IndexExists command. Does
> anybody know how to test if an Index alreay exists ?
>
> Jim
>
>

It sounds as if you would be better of using a TnxCachedDataSet

It will automatically take it's fields and indices from the attached source
dataset, adding any indices you have defined in AddIndexDefs, and use batch
read operations to copy all visible records from the source dataset (visible
means that you can apply a range and filter to the source dataset before
opening the cached dataset).

JimGilmour wrote:

>
> I am updating a Delphi App that used Paradox Tables to now use NexusDb
> Tables.
> In Paradox I used a lot of Private Tables for Temporary storage and have
> emulated this using NexusDb memory Tables.
> However, I used a lot of BatchMoves (which I have working OK) and then
> an AddIndex to add any Indexes (usually a primary)
> This works fine the first time I use the table but upon any subsequent
> uses of the table AddIndex gives me an 'Index already Exists Error'
> A cant seem to find the equivilent of an IndexExists command. Does
> anybody know how to test if an Index alreay exists ?
>
> Jim

[JimGilmour]

Thank you all,
This is typical of what is being done :

qryGetHeldLoads.Open;
{Copy}
BatchMove(qryGetHeldLoads, dmGinProg.tblHeldMote, batCopy);
dmGinProg.tblHeldMote.AddIndex('Load No','Mote Load',[ixprimary]);

ie. Execute a query, copy results to a Memory Tables (Used to be a Private Table in Paradox) and add an Index.

The BatchMove is some code that I copied from somewhere, but did not have an 'IndexDefs.Clear' which I have added thanks to your responses.

Also - thanks for the info about CachedDataSets I will give this a go for stuff in the future.

It seems to me, that you could do much better as follows:

Use a normal TnxTable and set the TableName property to ##Temp;

Use a TnxQuery to retrieve data from a Table into a Temporary Table like so:

nxTable1.Active := False;
with nxQuery1 do begin
Active := False;
SQL.Clear;
SQL.Add('select * into ##Temp from aTable');
SQL.Add('where (selectioncriteria);');
SQL.Add('create index ix on ##Temp (Field);');
Active := True;
end;
nxTable1.Active := True;
nxTable1.IndexName := 'ix';

On 20.06.2012 6:27 AM, JimGilmour wrote:
> Thank you all,
> This is typical of what is being done :
>
> qryGetHeldLoads.Open;
> {Copy}
> BatchMove(qryGetHeldLoads, dmGinProg.tblHeldMote, batCopy);
> dmGinProg.tblHeldMote.AddIndex('Load No','Mote Load',[ixprimary]);
>
> ie. Execute a query, copy results to a Memory Tables (Used to be a
> Private Table in Paradox) and add an Index.
>
> The BatchMove is some code that I copied from somewhere, but did not
> have an 'IndexDefs.Clear' which I have added thanks to your responses.
>
> Also - thanks for the info about CachedDataSets I will give this a go
> for stuff in the future.
>
>