Db Full Text Search Without Sql

With SQL by using LIKE operator we can filter out records that do not match our search pattern. But this also means we have to re-query our database and also records that don't match will not be available to see. In some situations, we don't want any of these. For example, perhaps our database server is slow due to high load, or we simply want the user to still be able to inspect records that don't match.

Locate method from many TDataset descendant usually does not capable to do full text search, because it only capable to match searched string if located in the beginning, not in the middle. For example if you are searching for "ball", Locate will find record with field containing "ballpark" but will not find "waterball". Therefore we have to look to another place to find our solution.

After searching for a neat solution for this and found none, finally I settled with searching the whole dataset and inspect the field(s) for our searched string. And here is the codes I finally use.

The following method inspects all fields (as long as not blob field) for the searched string.

// this method returns true if matching record found, else it returns false
function TForm1.Search(const AStr: string; ADataset: TDataset): Boolean;
var
  i: Integer;
begin
  Result := False;
  if ADataset.IsEmpty then Exit;

  ADataset.DisableControls;
  try
    ADataset.First;
    while not ADataset.Eof do
    begin
      for i := 0 to ADataset.FieldCount-1 do
        if not ADataset.Fields[i].IsBlob then
        begin
          Result := Pos(AStr, ADataset.Fields[i].AsString) > 0;
          if Result then
            Exit;
        end;
      ADataset.Next;
    end;
  finally
    ADataset.EnableControls;
  end;
end;

This method inspects only content of the specified field (if the field is not blob) for the searched string. The lesser field it has to inspect making it is faster than the previous.

// this method returns true if matching record found, else it returns false
function TForm1.Search(const AStr, AField: string;
  ADataset: TDataset): Boolean;
var
  vField: TField;
begin
  Result := False;
  if ADataset.IsEmpty then Exit;
  vField := ADataset.FieldByName(AField);
  if (vField=nil) or vField.IsBlob then Exit;

  ADataset.DisableControls;
  try
    ADataset.First;
    while not ADataset.Eof do
    begin
      Result := Pos(AStr, vField.AsString) > 0;
      if Result then
        Exit;
      ADataset.Next;
    end;
  finally
    ADataset.EnableControls;
  end;
end;

Note that since both methods have same name, you have to declare them as overloads, like this (or inspect the attached source code).

  TForm1 = class(TForm)
    ...
    ...
    function Search(const AStr: string; ADataset: TDataset): Boolean; [B]overload[/B];
    function Search(const AStr, AField: string; ADataset: TDataset): Boolean; [B]overload[/B];
    ...
    ...
  end;

Let's inspect our demo program. Initially it will look like this:

FullDbSearch_ScrShot001.png 11.1K 62 downloads

Now let's try to search for string "Robin Locksley" and here is the result:

FullDbSearch_ScrShot_SearchRobinLocksley001.png 12.2K 40 downloads

Note that it had properly skipped a couple "Robin" records.

Now what if we searched for strings that no record has it, and here is the result. And it's correct.

FullDbSearch_ScrShot_NotFound.png 17.27K 41 downloads

Full source code of the demo program is attached feel free to use and improve it. Any feedback will be appreciated.