SetKey

[Manuel Parra]

•Setkey - gotokey

We tested setkey MDX
But it only works with a single field
Applying multiple field does not work
Is there any way that works ...?

We Version 7.01 of TDbf and Delphi XE7

     Dbf2.IndexName := 'InaRut';
     Dbf2.SetKey;
     Dbf2.FieldByName('InaRut').AsString := Edit1.Text;
     if Dbf2.GotoKey then
     Begin
            // It works right

     end;

     nombapel := campo1+campo2

     Dbf1.IndexName := 'nombapel';
     Dbf1.SetKey;
     Dbf1.FieldByName('campo1').AsString := Edit1.Text;
     Dbf1.FieldByName('campo2').AsString := Edit2.Text;
     if Dbf1.GotoKey then
     Begin
             // It works not correct

     end;

[LS Ploeger]

I don't know exactly what is going on here, but it looks suspicious that you set Dbf1.IndexName to the string constant 'nombapel', while you also have a variable nombapel that is set to campo1+compo2. Perhaps this is the problem?

[Manuel Parra]

Thank you for answering...

Did not know how exposed the idea...

The file index is made up of those two fields...

When we work with the version of Delphi 7 Ttable component works perfect the setkey and TDBF and Delphi XE7 does not work.

the setkey only works when the index has a single field

I hope I expressed correctly

[LS Ploeger]

Do you mean that there is an existing index that is set to 'campo1+campo2', where you only included the statement "nombapel := campo1+campo2" to indicate the value of this expression?

I found that TDbf is somewhat sensitive to index files that were modified over time. If you still have "Database Desktop" available (shipped with D7) than you may want to try to pack the file to see if this helps. Select "Restructure" from the "Table" menu and tick the box "Pack Table" and then press Save. Of course, you can also try to regenerate the index using the TDbf component, but I found this to be an easy technique to recreate a proper MDX file.

[Manuel Parra]

Yes...

Regarding the latter ... that was the first to perform before resorting to the Forum.
And it did not work ....
I also tested with tables created from TDbf and does not work .

Sabra somehow work with multiple fields ... ?

I have been using as an alternative the LOCATE command , but works slower and does not work well when you delete records .....

[LS Ploeger]

Do you use multiple Locate() commands instead? I found an old post while searching this forum for "MDX with multiple index fields" where the original author of TDbf (Micha Nelissen) stated that multiple indexes were not supported (in 2002!). I would not be surprised if this issue is still open.

[LS Ploeger]

While looking into this in more detail I found that this might be related to the potential bug in TMdxPage.GetIsInnerNode that I mentioned elsewhere in this forum. Can you please try if changing this function in dbf_idxfile.pas as follows solves the issue:

function TMdxPage.GetIsInnerNode: Boolean;
begin
  Result := SwapIntLE(PMdxPage(FPageBuffer)^.NumEntries) <= SwapWordLE(PIndexHdr(FIndexFile.IndexHeader)^.NumKeys);
  // if there is still an entry after the last one, this has to be an inner node
  if Result then
    Result := PMdxEntry(GetEntry(PMdxPage(FPageBuffer)^.NumEntries))^.RecBlockNo <> 0;
end;

[Manuel Parra]

Thank you....
Do the proposed change, but it did not .
Maximum index fields are 2. If we add two fields over the gotokey not work

[LS Ploeger]

Too bad.. Can you share your DBF and MDX file, or does it contain sensitive data?

[Manuel Parra]

yes,
Attach

[LS Ploeger]

Thank you. I don't see an index for "campo1+campo2", are you sure this is the same DBF file that you mentioned in your original question? In fact, there are no fields called campo1 and campo2.

[Manuel Parra]

Hello,

The expression " campo1+campo2 " was just one example.

So goes the associated index file if you open the Desktop Database delphi vera structure indices

[LS Ploeger]

These are the indexes as seen by Database Desktop:

PCCTE01: PCTCRUT+STR(PCTCPRE)+STR(PCTCNUM)
PCCTE02: PCTCRUT+STR(PCTCPRE)+STR(PCTCNUM)+STR(PCTCUOT)
PCCTE03: PCTCRUT+STR(PCTCMES)+STR(PCTCAGN)
PCCTE04: PCTCRUT+STR(PCTCPRE)+STR(PCTCNUM)+STR(PCTCMES)+STR(PCTCAGN)
PCTCPRE: PCTCPRE
PCTCRUT: PCTCRUT

Which fields did you use for SetKey/GotoKey?

[Manuel Parra]

with BDAdministrador , he used
Table4.IndexName := 'Pccte01';
Table4.SetKey;
Table4.FieldByName('PctcRut').AsString := rut;
Table.FieldByName('Pctcpre').AsInteger := codp;
Table4.FieldByName('Pctcnum').AsInteger := nump;
if Table4.GotoKey then

      and it worked ok , but TDbf not work
      of course the rut variable is string and two integers

[LS Ploeger]

I was able to reproduce this issue. The reason for this problem most likely is that the STR() expression is not handled correctly in TDbf. On e.g. http://www.dbase.com/help/String_Objects/IDH_STR_FUNC.htm is stated that the default length of the returned string is 10 characters, where TDbf expects 11 characters to accommodate the longest possible integer value of "-2147483648". This should be fairly easy to fix, as soon as I have a solution I will let you know.

[LS Ploeger]

Try the following:

In dbf_prscore.pas change procedure FuncStr(Param: PExpressionRec) as follows:

begin
  if Param^.Args[1] <> nil then
    Size := PInteger(Param^.Args[1])^
  else
  begin
    // http://www.dbase.com/help/String_Objects/IDH_STR_FUNC.htm states:
    // STR(<expN> [, <length expN> [, <decimals expN> [, <expC>]]])
    // <length expN>
    // The length of the character string to return. The valid range is 1 to 20,
    // inclusive, and includes a decimal point, decimal digits, and minus sign
    // characters. The default is 10. If <length expN> is smaller than the
    // number of integer digits in <expN>, STR( ) returns asterisks (*).
    Size := 10;
  end;
  { keep the rest the same, i.e.
  if Param^.Args[2] <> nil then
  (..)
  }
end;

[Manuel Parra]

Hello,

I was conducting tests and follow all the same, but I think it is not that, because the maximum the whole I use is 100 and if we make the sum 100 + 100 + 100 is equal to over nine .
So let 's change in function

We left the change as follows, and the result was the same , finds data

if Param^.Args[1] <> nil then
Size := PInteger(Param^.Args[1])^
else
begin
Size := 10;
{
case Param^.ArgsType[0] of
etInteger: Size := 11;
etLargeInt: Size := 20;
else
Size := 10;
end; }

By the time our solution was next, it works but is slower and also when two or more records of the same type must be recorrear need to thin table ...

   Dbf4.IndexName := 'Pccte04';
   Setlength(keysearch, 5);
   keysearch[0] := rut;
   keysearch[1] := pres;
   keysearch[2] := Dbf3.Fields[2].AsInteger;;
   keysearch[3] := mes;
   keysearch[4] := agn;
   if dbf4.Locate('pctcrut;pctcpre;pctcnum;pctcmes;pctcagn',keysearch,[loCaseInsensitive,loPartialKey]) = true then
                Begin

                .......

[LS Ploeger]

So, does my proposed solution work or not?

In this case the Locate() function will most likely do a linear search, but your original question was about GotoKey.

Can you please try to be more clear? I don't understand your remark about the relation between 100 and nine.

[Manuel Parra]

The proposed solution does not work ....

Then Locate() If it is linear so it takes longer and requires us to recorresr the rest of the table. It is slower but we have not found another solution

with 100 , referred to throughout the entire variable that was only 3 digits and we validate that does not exceed the number 100 .
what I mean if simamos the three integer variables only

[LS Ploeger]

Settings the index to 'PCCTE01' worked for me. In the .MDX file the values are stored with a field width of 10, while 11 was incorrectly expected.

This was my test code:

  dbf1 := TDbf.Create(nil);
  dbf1.TableName := 'E:\paraprueba\pagccte.DBF';
  dbf1.Active := True;
  dbf1.IndexName := 'PCCTE01';
  dbf1.SetKey;
  dbf1.FieldByName('PCTCRUT').AsString := '14.481.056-2';
  dbf1.FieldByName('PCTCPRE').AsInteger  := 2;
  dbf1.FieldByName('PCTCNUM').AsInteger  := 1;
  if dbf1.GotoKey then
    ShowMessage(dbf1.FieldByName('PCTCVAL').AsString);

This pops up a message showing "9507" with my fix applied, without my fix there is no message displayed.

[Manuel Parra]

Hello,

Had not tested it with 3 courses and effectively functions as you mentioned thanks .
But with 4 fields it does not work, I've been testing and found no records ...

                Dbf4.IndexName := 'Pccte04';
                Dbf4.SetKey;
                Dbf4.FieldByName('PctcRut').AsString   := '14.440.615-K';
                Dbf4.FieldByName('Pctcpre').AsInteger  := 24;
                Dbf4.FieldByName('Pctcnum').Asinteger  := 4;
                Dbf4.FieldByName('Pctcmes').AsInteger  := 3;
                Dbf4.FieldByName('pctcagn').AsInteger  := 2016;
                if Dbf4.GotoKey then
                       ShowMessage(dbf1.FieldByName('PCTCVAL').AsString);

                Brings nothing
                and should show '27683'

[Manuel Parra]

as I already have the system running with some user, today we found out that the problem of the multiple index this when we eliminate records.
apparently the file index not updated when deletes any record and only returns to work properly when we do a pack

[LS Ploeger]

The listed entry (for "14.440.615-K") is not found in the sample database file that you provided.

However, the following code works on an unmodified DBF file:

Dbf1.IndexName := 'PCCTE04';
Dbf1.SetKey;
Dbf1.FieldByName('PctcRut').AsString   := '12.070.983-6';
Dbf1.FieldByName('Pctcpre').AsInteger  := 27;
Dbf1.FieldByName('Pctcnum').Asinteger  := 1;
Dbf1.FieldByName('Pctcmes').AsInteger  := 3;
Dbf1.FieldByName('pctcagn').AsInteger  := 2015;
if Dbf1.GotoKey then
  ShowMessage(dbf1.FieldByName('PCTCVAL').AsString);

This pops up a message showing '36252'.

Indeed, the MDX file is not updated when records are deleted, but this should be harmless. I tried if removing records broke the index, but that did not seem to be the case: searching was still successful. However, when records are added the MDX file does get updated.