Does Findkey work in Ttable and/or Tquery

[JEFFB]

I am using RAD 10, mysql 5.7 and mydac 8.6.19.

I have a table with three fields marked as primary (id, state, city). I am reading text files and inserting the data into the table. I want to read the text for these three fields and use Findkey([id,state,city]) and verify that there is a record or not before I try to insert the record. Right now, it does not seem to work. but maybe I am doing something wrong.

I am getting ready to try to following to see if it works.

function TForm1.UserExists(const Username, Password: String): Boolean;
begin
sql.Close;
sql.SQL.Clear;
sql.SQL.Add('SELECT COUNT(`id`) as RecCount FROM `users` WHERE `username` = :name AND `password` = :pass;');
sql.ParamByName('name').AsString := trim(usernm);
sql.ParamByName('pass').AsString := trim(passwd);
sql.Open;
try
Result := sql.FieldByName(RecCount).AsInteger = 1;
finally
sql.Close;
end;
end;

I have to change it for my needs!!!!

But what is the best way to search a table for to see if a record exists or not. And if so, an example of how would be appreciated.

thanks.

Jeff

[ViktorV]

You can use the method FindKey(const KeyValues: array of System.TVarRec) for record search through TMyTable and TMyQuery. Please let us know, why you think the method is not working.
You can also use the following code:

Code: Select all

  function FindRecord(id: integer; state, city: string): boolean;
  var
    MyQuery: TMyQuery;
  begin
    MyQuery := TMyQuery.Create(nil);
    try
      MyQuery.Connection := MyConnection;
      MyQuery.SQL.Add('SELECT COUNT(*) as RecCount FROM `table` '+
        'WHERE `id` = :id AND `state` = :state AND `city` = :city;');
      MyQuery.ParamByName('id').AsInteger := id;
      MyQuery.ParamByName('state').AsString := state;
      MyQuery.ParamByName('city').AsString := city;
      MyQuery.Open;
      try
        Result := MyQuery.FieldByName('RecCount').AsInteger = 1;
      finally
        MyQuery.Close;
      end;
    finally
      MyQuery.Free;
    end;
  end;