MYDAC Findkey

[skytec]

I am using Delphi 7 (yes I know its old) MySQL 5.7, MYDAC for Delphi 7 Version 8.7.24 and the following code that worked with BDE components

TableSPECITEMS.Indexfieldnames := 'SpecID;GroupNo;Item';
TableITEMS.First;
while not TableITEMS.Eof do
begin
With TableSPECITEMS do
//If not locate('SpecID;GroupNo;Item',[TableSPECSSpecID.value,TableITEMSGroupNo.value,TableITEMSItem.value],[loCaseInsensitive]) then
if not findkey([TableSPECSSpecID.value,TableITEMSGroupNo.value,TableITEMSItem.value]) then
begin
TableSPECITEMS.Insert;
TableSPECITEMSSpecID.Value := TableSPECSSpecID.Value;
TableSPECITEMSGroupNo.Value := TableITEMSGroupNo.Value;
TableSPECITEMSItem.Value := TableITEMSItem.Value;
TableSPECITEMSDescription.Value := TableITEMSDescription.Value;
TableSPECITEMSQty.Value := TableITEMSQty.Value;
TableSPECITEMSUnit.Value := TableITEMSUnit.Value;
TableSPECITEMSNotes.Value := TableITEMSNotes.Value;
TableSPECITEMSIncl.Value := TableITEMSIncl.Value;
TableSPECITEMSPS.Value := TableITEMSPS.Value;
TableSPECITEMSPC.Value := TableITEMSPC.Value;
TableSPECITEMSEa.Value := TableITEMSEa.Value;
TableSPECITEMSStdItem.Value := True;
TableSPECITEMSIsExtra.Value := TableITEMSIsExtra.Value;
TableSPECITEMSDatabuild.Value := TableITEMSDatabuild.Value;
TableSPECITEMSImage.Value := TableITEMSImage.Value;
TableSPECITEMSBold.Value := TableITEMSBold.Value;
TableSPECITEMS.Post;
end
else begin
TableSPECITEMS.Edit;
TableSPECITEMSDescription.Value := TableITEMSDescription.Value;
TableSPECITEMSDatabuild.Value := TableITEMSDatabuild.Value;
TableSPECITEMSImage.Value := TableITEMSImage.Value;
TableSPECITEMS.Post;
end;
TableITEMS.Next;
end;

Have tried both the findkey and the locate command (which I get and error of there is no overloaded version of locate that can be called with these arguments- so I am probably doing something wrong on the command) and it fails to recognise a record that already exists (with findkey)
and results in the following error - #23000Duplicate Entry '18-24-2-0-Scaffold - As per Regulations for key 'PRIMARY''

Given that I have set the index to search of 'SpecID;GroupNo;Item' it would appear to be totally ignoring this and defaulting back to the PRIMARY key of 'Item;SpecID;GroupNo;Isextra;Description';. I have also tried 'if not findkey([TableSPECSSpecID.asstring,TableITEMSGroupNo.asstring,TableITEMSItem.asstring]) then
begin' without success.

I did note that there have been previous problems with findkey not working that supposedly have been fixed

Is this the way findkey it is supposed to work - always only using the PRIMARY key? If so then it loses some of its compatability with the way the BDE components work - or am I doing something wrong ?

[ViktorV]

1. To solve your issue, please try to add the following line to your code:

Code: Select all

TableSPECITEMS.KeyFields := 'SpecID;GroupNo;Item'; 

before calling the FindKey method.
2. For correct execution of the Locate method, please use the following code in your sample:

Code: Select all

 Locate('SpecID;GroupNo;Item', VarArrayOf([TableSPECSSpecID.Value, TableITEMSGroupNo.Value,TableITEMSItem.Value]), [loCaseInsensitive])

[skytec]

Many Thanks VictorV. That did the trick, using the code you posted and adding Variants to uses for locate and using 'keyfields' rather than 'IndexFieldNames'.
Am I correct in assuming that IndexFieldNames is only used for sorting of records and not for key lookups ?

[ViktorV]

It is good to see that the problem has been solved.
Yes, you are right. The IndexFieldNames property is used only for local sorting. See more details about this method in SDAC help: https://www.devart.com/mydac/docs/?deva ... dnames.htm