Clear a field when another field data doesn't exist

Question

I have a Field named "refFile" which may or may not have a Path description in it.

I need to go through the entire database and check whether Paths that are defined in "refFile" still actually exist.

Using Delphi (Pascal) this takes many, many minutes

bigDB.First;
while not(bigDB.EOF) do
begin 
  if Trim(bigDB.FieldByName('refFile').AsString) > '' then
  begin
    if not(FileExists(bigDB.FieldByName('refFile').AsString)) then
    begin
      bigDB.Edit;
      bigDB.FieldByName('refFile').AsString:='';
      bigDB.Post;
    end;
  end;
  bigDB.Next;
end;   

How do I do that in SQL?

Thank you.

Answer 1

You cannot check the validity of a path in SQLLite but you can filter records with something in the path and reduce the list of lines to check.

You can order the records on this field (if you have an index on it) and check only the paths you didn't checked before.

You also can use threads to do this long operation in background. Simply use TThread.Createanonymousthread(procedure begin end).Start;

Answer 2

You can't check the existence of a file in a plain SQLLite query. You could do that by using an UDF (User defined function) but it would be a little more complex and would requires some skills in other programming languages (Note that in that case your files should be accessible from the server, otherwise it wouldn't work).

If you are looking for a simpler solution, I think you can speed up your program by reducing the number of records resulted by the query and by improving your Delphi code in order to make it a little more efficient.

Select SQL:

• Use length and trim functions due to reduce the number of records to be verified by your Delphi code.

  select refFile 
  from myTable
  where (refFile is not null) and (length(trim(refFile)) > 0)
  

Delphi:

• Call TDataSet.FieldByName only once.

• Try using TDataSet.DisableControls and TDataSet.EnableControls (In this way, some dataset's components are faster, even if the dataset component is not linked to any control).

  var
    Fld : TField;
  begin
    BigDB.DisableControls();
    try
      Fld := BigDB.FieldByName('refFile');
  
      BigDB.First;
      while not(BigDB.Eof) do
      begin
        if not(FileExists(Fld.AsString)) then
        begin
          BigDB.Edit;
          Fld.AsString := '';
          BigDB.Post;
        end;
        BigDB.Next;
      end;
    finally
      BigDB.EnableControls();
    end;
  

Furthermore, you could consider these other optimizations:

• If the refFile field contains the same value multiple times, you could sort the query by the refFile field and change the Delphi code in order to verify each filename only once. (You can do that by storing the last value and the result of the FileExists function).
• You can run your code asyncronusly by using the TThread class. In this way your application won't freeze and it could be faster.

Answer 3

For example with FireDAC it's extremely easy to create user defined functions. If you're using it, try something like this. It could save some time because the engine doesn't need to fetch the resultset to the client application:

uses
  FireDAC.Phys.SQLiteWrapper;

type
  TForm1 = class(TForm)
    Button1: TButton;
    FDQuery1: TFDQuery;
    FDConnection1: TFDConnection;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    FValidator: TSQLiteFunction;
    procedure ValidateFile(AFunc: TSQLiteFunctionData; AInputs: TSQLiteInputs;
      AOutput: TSQLiteOutput; var AUserData: TObject);
  end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDConnection1.Open;

  FValidator := TSQLiteFunction.Create((TObject(FDConnection1.CliObj) as TSQLiteDatabase).Lib);
  FValidator.Args := 1;
  FValidator.Name := 'FileExists';
  FValidator.OnCalculate := ValidateFile;
  FValidator.InstallAll;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.SQL.Text :=
    'UPDATE MyTable SET FileName = NULL WHERE ' +
    'FileName IS NOT NULL AND NOT FileExists(FileName)';
  FDQuery1.ExecSQL;
end;

procedure TForm1.ValidateFile(AFunc: TSQLiteFunctionData; AInputs: TSQLiteInputs;
  AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsBoolean := FileExists(AInputs[0].AsString);
end;

Or simply drop the TFDSQLiteFunction component, fill out the FunctionName property with name of the function, write OnCalculate event handler similar to the above and enable the component by setting the Active property.