How to validate SQLite DB in Console Application using TFDSQLiteValidate

Question

We are able to use the validation in TForm and it works perfectly fine, but now we have to validate/Analyze the SQLite DB from the Console Only Application. Please share any detailed example of working work in XE7+.

Here is one function which does that

  try
      goodsqlitedb:=true;
      //Check if Db is closed and not in use
      if(FDSqliteQuery1.Active) then
      begin
            FDSqliteQuery1.Close;
            updatelog('logfilename.txt', 'Closed Query');
      end
      else
          updatelog('logfilename.txt', 'Query Already Closed');

      FDSqliteConn.Close;
      updatelog('logfilename.txt', 'Connection Closed');

      //FDSqliteConn.Open;
      FDPhysSQLiteDriverLink2:=TFDPhysSQLiteDriverLink.Create(nil);
      FDSQLiteValidate2:=TFDSQLiteValidate.Create(nil);
      FDPhysDriverService:=TFDPhysDriverService.Create(nil);
      FDSQLiteSec2:=TFDSQLiteSecurity.Create(nil);
      FDSQLiteSec2.DriverLink:=FDPhysSQLiteDriverLink2;
      FDSQLiteValidate2.DriverLink:=FDPhysSQLiteDriverLink2;

      //FDSQLiteValidate2.OnProgress(FDPhysDriverService,DBMessage);

      with FDSQLiteValidate2 do
      begin
            updatelog('logfilename.txt', 'To Start the Validation');
            Database := dbfile;
            Analyze;
            updatelog('logfilename.txt', 'Database To be Analyzed');
            if not CheckOnly then
                Result:='1' //Database has problems
            else
                Result:='0'; //Database is good
      end;
  except
  on E: Exception do
      begin
            updatelog('logfilename.txt', 'Database Exception='+E.Message);
            Result:='2';    //Database is Corrupt
            goodsqlitedb := False;
            //raise;
      end;
  end;

  try
      FDPhysSQLiteDriverLink2.Destroy;
      FDSQLiteValidate2.Destroy;
      FDPhysDriverService.Destroy;
      FDSQLiteSec2.Destroy;
  finally
         updatelog('logfilename.txt', 'Drivers destroyed');
  end;

Answer 1

For porting your working implementation from a visual to a non-visual application, you have at least two options for where to get your components from:

1. TDataModule is a non-visual component, that you can put other non-visual components on and edit them via IDE's object inspector, just like on a TForm or a TFrame. You'll find it within the New...-Wizard in your Delphi IDE. So, if you want to stick to GUI based components, you can put them there.
2. Create needed components at runtime by yourself.

Answer 2

Below is a console application which should do what you want, subject to the following caveats:

1. I couldn't get a GUI testbed I set up based on your code to run without raising an exception on FDSQLiteValidate2.Analyze In my gui version, the error reported is that the database is locked, whereas in the console version, the error message is "Database Exception=Object factory for class {3E9B315B-F456-4175-A864-B2573C4A2201} is missing. To register it, you can drop component [TFDGUIxWaitCursor] into your project".

   However, since you say "Here is one function which does that", I haven't wasted any time debugging either version and leave that to you.

2. Returning the result of a database function as a string is not the way I'd do it, but your choice.

3. The use of a TStringList as a log isn't great if there are a great many log entries to add, but at least it permits a v. simple UpdateLog routine.

Code :

program ConsoleValidateDB;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Classes,
  // following come from a GUI testbed
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf,
  FireDAC.Stan.Async, FireDAC.DApt, FireDAC.UI.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Phys,
  FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef, FireDAC.Stan.ExprFuncs,
  FireDAC.VCLUI.Wait, Data.DB,
  FireDAC.Comp.Client, FireDAC.Comp.DataSet;

const
  DBFileName  = 'D:\Delphi\Code\Sqlite\db2.sqlite';
  LogFileName = 'C:\temp\sqlitelog.txt';

var
  Log : TStringList;

procedure UpdateLog(LogFN, Msg : String);
begin
  if Log = Nil then
    Log := TStringList.Create;
  Log.LoadFromFile(LogFN);
  Log.BeginUpdate;
  try
    Log.Add(Msg);
    Log.SaveToFile(LogFN);
  finally
    Log.EndUpdate
  end;
end;

function ValidateDB : String;
var
  goodsqlitedb : Boolean;
  FDPhysSQLiteDriverLink2 : TFDPhysSQLiteDriverLink;
  FDSQLiteValidate2 : TFDSQLiteValidate;
  FDPhysDriverService : TFDPhysDriverService;
  FDSQLiteSec2 : TFDSQLiteSecurity;
begin
  Result := 'Undefined';
  try
    goodsqlitedb:=true;

(*  These are evidently components in a Gui app.  Ignore them.
    //Check if Db is closed and not in use
    if(FDSqliteQuery1.Active) then
    begin
          FDSqliteQuery1.Close;
          updatelog('logfilename.txt', 'Closed Query');
    end
    else
        updatelog('logfilename.txt', 'Query Already Closed');

    FDSqliteConn.Close;
    updatelog('logfilename.txt', 'Connection Closed');
*)

    //FDSqliteConn.Open;
    FDPhysSQLiteDriverLink2:=TFDPhysSQLiteDriverLink.Create(nil);
    FDSQLiteValidate2:=TFDSQLiteValidate.Create(nil);
    FDPhysDriverService:=TFDPhysDriverService.Create(nil);
    FDSQLiteSec2:=TFDSQLiteSecurity.Create(nil);
    FDSQLiteSec2.DriverLink := FDPhysSQLiteDriverLink2;
    FDSQLiteValidate2.DriverLink:=FDPhysSQLiteDriverLink2;

    //FDSQLiteValidate2.OnProgress(FDPhysDriverService,DBMessage);

   //  "with" is evil, so omit it // with FDSQLiteValidate2 do begin
      updatelog(LogFileName, 'To Start the Validation');
      FDSQLiteValidate2.Database := DBFileName;
      FDSQLiteValidate2.Analyze;
      updatelog(LogFileName, 'Database To be Analyzed');
      if not FDSQLiteValidate2.CheckOnly then
          Result:= '1' //Database has problems
      else
          Result:= '0'; //Database is good
    //end;
  except
    on E: Exception do  begin
              updatelog(LogFileName, 'Database Exception='+E.Message);
              Result:= '2';    //Database is Corrupt
              goodsqlitedb := False;
              //raise;
        end;
    end;

  try
    FDPhysSQLiteDriverLink2.Destroy;
    FDSQLiteValidate2.Destroy;
    FDPhysDriverService.Destroy;
    FDSQLiteSec2.Destroy;
  finally
    updatelog(LogFileName, 'Drivers destroyed');
  end;

end;

begin
  writeln(ValidateDB);
end.