SQLite - TFDQuery with Delphi

Question

I'm not a database expert at all. I need your help.

Some context first:

I need to post a Work Order to a REST server. When I get the WO, I know the customer name, the site name where the job must be executed, and the full site address. Site name I receive could be like:

• George 1 (The correct name)
• George1
• Georg 1

If I'm posting the WO to the server, the request is sometimes rejected because the site name is not 100% exactly the same as stored on the server.

To propose a better user experience, I created a local SQLite table that I can synchronize with the REST server. Before posting the WO, I'd like to give the opportunity to the user to correct the site name if a direct match is not found by proposing a dropdown list of the best possible matches.

At this point, my code is: (This may not be the best way to do it)

Query := TFDQuery.Create(nil);
try
  Query.Connection := DBConnection;
  SQL:= 'Select Sitename from SitesTable where (CUSTOMERNAME = :CustomerName) AND (Sitename LIKE :SiteName)';
  Query.SQL.Text := SQL;
  Query.ParamByName('CustomerName').AsString := aCustomerName;
  Query.ParamByName('SiteName').AsString := aSiteNam;
  Query.Open;

  while not Query.Eof do
    begin
      List.Add(Query.FieldByname('Sitename').AsString);
      Query.Next;
  end;
finally
  Query.Free;
end;

This is giving me:

• One result if the 100% match exists for the SiteName field
• Zero result if not

First, I would like to improve that query to get all possible sites containing 'George 1' including George 15. That's why I am using LIKE instead of = but it gives me only one result.

I also tried to get all possible 'George' records by removing the number but I always get zero result

Finally, if no match is found, I'd like to query the address field to propose all sites that are located in that street.

I tried to use Query.RecordCount to know if I have to search for more but it is always giving me 0

What is the best Delphi code to achieve this?

Thank you in advance for your help

Answer 1

You have to use a request like this:

Select Sitename 
from SitesTable
where (Sitename = "George 1") 
   or (Sitename like "%George%");

When using Like, the % is much like the * when searching for files in the OS. Of course you may add as many variations with the "Or".

Translated to a Delphi snippet, it gives:

procedure TForm1.Button1Click(Sender: TObject);
begin
    FDConnection1.DriverName                := 'SQLITE';
    FDConnection1.Params.Values['Database'] := 'E:\Temp\StackOverflow64067394.sqlite3';
    FDConnection1.Open;

    FDQuery1.SQL.Text := 'Select Sitename ' +
                         'from SitesTable ' +
                         'where (Sitename = :Param1) ' +
                            'or (Sitename like :Param2)';
    FDQuery1.ParamByName('Param1').AsString := 'George 1';
    FDQuery1.ParamByName('Param2').AsString := '%George%';
    FDQuery1.Open;
    while not FDQuery1.Eof do begin
        Memo1.Lines.Add(FDQuery1.FieldByName('Sitename').AsString);
        FDQuery1.Next;
    end;
    FDQuery1.Close;
end;