dbase and indices [SOLVED]

[timcs]

I am trying to add an index with three fields in a dbase database. I have tried the following but this errors with an operator/function missing :

dbf1.Active:=false;
   dbf1.Exclusive:=true;
   dbf1.Open;
   dbf1.AddIndex('custdate','field1.field2.field3' [ixCaseInsensitive]);
   dbf1.Close;
   dbf1.Exclusive:=false;
   dbf1.Active:=true;     

I have also tried other separators just as spaces, semi colons, colons and the plus sign with no luck. How do I add more that one field to an index in a dbase database?

[howardpc]

The second parameter to AddIndex() must be a string expression. So you have to consider the field type of each field you want to use in the index. For string fields you can just concatenate their values using '+', but for numeric fields you have to use the STR(numField) conversion function in the field expression before concatenation. Date fields require the DTOS(dateField) conversion function.
There is a 100 character limit on the expression that combines the fields.

[timcs]

The second parameter to AddIndex() must be a string expression. So you have to consider the field type of each field you want to use in the index. For string fields you can just concatenate their values using '+', but for numeric fields you have to use the STR(numField) conversion function in the field expression before concatenation. Date fields require the DTOS(dateField) conversion function.
There is a 100 character limit on the expression that combines the fields.

Thanks for this howardpc I will give this a try, looking at the code I supplied it was actually this:

Code: [Select]

dbf1.Active:=false;
   dbf1.Exclusive:=true;
   dbf1.Open;
[b]   dbf1.AddIndex('custdate','field1,field2,field3', [ixCaseInsensitive]);[/b]
   dbf1.Close;
   dbf1.Exclusive:=false;
   dbf1.Active:=true;     

I didn't put the correct separators in the first post and missed the , after listing fields

[timcs]

Code: [Select]

   dbf1.Active:=false;
   dbf1.Exclusive:=true;
   dbf1.Open;

   dbf1.AddIndex('custdate','field1, '+' field2,'+' field3',[ixCaseInsensitive]);
   dbf1.Close;
   dbf1.Exclusive:=false;
   dbf1.Active:=true;     
Tried it as above and also tried the delimiter with spaces, semi colons, is what I have done what you are referring to? I still get the error operator/function is missing with the comma, if I tried spaces it repeats the first field twice in the error and if I try any of the other delimiters it just asks to terminate the program.

I now know what the problem is after reading you reply again however how to I specify the date fields when all I am doing to is entering the reference as the field name dateon ?

[Knipfty]

timcs,

I believe that the syntax you need is

Code: [Select]

dbf1.AddIndex('custdate',field1+field2+field3,[ixCaseInsensitive]);
That is assuming that all 3 fields are of String type.  IF not, then you need to convert each one to a string.  For example:

Code: [Select]

dbf1.AddIndex('custdate',field1+inttostr(field2)+field3,[ixCaseInsensitive]);
Knipfty

[timcs]

timcs,

I believe that the syntax you need is

Code: [Select]

dbf1.AddIndex('custdate',field1+field2+field3,[ixCaseInsensitive]);
That is assuming that all 3 fields are of String type.  IF not, then you need to convert each one to a string.  For example:

Code: [Select]

dbf1.AddIndex('custdate',field1+inttostr(field2)+field3,[ixCaseInsensitive]);
Knipfty

Hi Knipfty

  Do I specify the fields as per their names or am I having to do dbf1.field....... ?

[Knipfty]

I don't a a dbf environment to play with here at work (they actually expct that I work while I'm at work ) so I'm not sure.

DId you look at this page? http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial

[timcs]

I don't a a dbf environment to play with here at work (they actually expct that I work while I'm at work ) so I'm not sure.

DId you look at this page? http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial

First place I looked at, the field names mentioned here for creating indices are no good for an example that I need as they are just for single fields not multiple. In the examples it states to put the fields surrounded by single quotes this is where I started using them when I have been doing it. What I cannot seem to get to work is that if I state a date field e.g. named as dateon then if I do datetostr(dateon) it errors with dateon is not a valid identifier . I then have tried to include dbf1.fieldbyname('dateon') method but I am getting no where on this either.

[Knipfty]

Hi timcs,

I found this on Koders Code:  http://www.koders.com/delphi/fid6041B675F67CF37C3E155BD0F0D2A8B51406CD18.aspx?s=dbf+AddIndex#L8

Code: [Select]

TimeLogDbf.AddIndex('PCNO.NDX', 'PCNO+TAG', [ixExpression]);
Hopefully this gives you a good example

Knipfty

[timcs]

Hi timcs,

I found this on Koders Code:  http://www.koders.com/delphi/fid6041B675F67CF37C3E155BD0F0D2A8B51406CD18.aspx?s=dbf+AddIndex#L8

Code: [Select]

TimeLogDbf.AddIndex('PCNO.NDX', 'PCNO+TAG', [ixExpression]);
Hopefully this gives you a good example

Knipfty

Knipfty , I did try this at the start using the + sign but how do I cope with date fields ? or is that what the [ixExpression] part is for?

Just tried it and it errors on the + symbols

[Knipfty]

At this point I do not know.  I played with this years ago in Delphi.  Please try the [ixExpression].

Can you get multiple fields to work that isn't a date field?  IN the example I provided, it is interesting that all of the indexes seem to only use a TStringField.  And the only use of  [ixExpression] is when there are two fields being indexed.

[timcs]

At this point I do not know.  I played with this years ago in Delphi.  Please try the [ixExpression].

Can you get multiple fields to work that isn't a date field?  IN the example I provided, it is interesting that all of the indexes seem to only use a TStringField.  And the only use of  [ixExpression] is when there are two fields being indexed.

Sorry Knipfty but I did use the [ixExpression]  at the same time but still got the error on the + symbol stating that this is not a valid field

[Knipfty]

What about only using fields of TStringField type?

[timcs]

What about only using fields of TStringField type?

I did try dbf1.fieldbyname('dateon').asString if this is what mean you mean.

[Knipfty]

no.  I want you to index on two fields that are string types without any type casting using the plus sign.  It looks like you have two issues right now.
1. building an index with two or more fields
2. building an index using a date field

Yes, you want them all together, but let's at least isolate the two issues.  If you can build the index with two string fields, then we are hald way home.