Error using SQLite ATTACH - DETACH in Delphi

Question

I experience a, for me unsolvable, problem with ATTACH and DETACH in SQLite, using Delphi (Firedac).

I have one database file connected and attach a second one with:

FDConnection1.ExecSQL('ATTACH DATABASE "' + Import_DB_filename + '" AS IMPORTDB;');

Therein, the variable 'Import_DB_filename' contains the full path and filename of the database file.

This works OK and I can access both databases within the connection through the FireDac queries, and can do my coding without problems. However, things go wrong upon detaching:

FDConnection1.ExecSQL('DETACH DATABASE IMPORTDB;');

In debugging mode, I always get the error:

Debugger Exception Notification
E Project My_Program.EXE raised exception class $C0000005 with message 'access violation at 0x00405d7b: read of address 0x00000000'.

Apparently something goes wrong with the memory assignments, since the debugger stops in a (assembly) function SysFreeMem(P:Pointer): Integer; in GETMEM.INC.

Whatever I try, the error persists and associates a memory leak that eventually leads to a crash of the compiler (Delphi Seattle Enterprise).

Even attaching and subsequent detaching of the database without passing any code results in the same error.

(FDconnection: locking mode = lmNormal; JournalMode = jmOff or jmWALL or jmdelete)

I do hope that you can help me out on this lasting problem.

Answer 1

If you run the project below, you should find that:

• You can access two Sqlite databases quite happily using separate FDConnections and FDTables.

• You can move data from a table in one db to a table of the same name in the other using a FireDAC FDDataMove component.

Code:

unit BatchMoveu;

interface

[...]

type
  TForm3 = class(TForm)
    FDConnection1: TFDConnection;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    DBNavigator1: TDBNavigator;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink;
    Button1: TButton;
    FDTable1: TFDTable;
    FDConnection2: TFDConnection;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    btnBatchMove: TButton;
    FDDataMove1: TFDDataMove;
    FDTable2: TFDTable;
    procedure btnBatchMoveClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    procedure PopulateTable1;
    procedure TestDataMove;
  public
    procedure CreateDatabase(DBName : String; FDConnection : TFDConnection;
      FDTable : TFDTable);
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

const
  DBName1 = 'd:\delphi\code\sqlite\db1.sqlite';
  DBName2 = 'd:\delphi\code\sqlite\db2.sqlite';

procedure TForm3.Button1Click(Sender: TObject);
begin
  CreateDatabase(DBName1, FDConnection1, FDTable1);
  CreateDatabase(DBName2, FDConnection2, FDTable2);

  PopulateTable1;

  FDTable2.Open;

end;

procedure TForm3.CreateDatabase(DBName : String; FDConnection : TFDConnection;
  FDTable : TFDTable);
var
  AField : TField;
  i : Integer;
begin
  if FileExists(DBName) then
    DeleteFile(DBName);

  AField := TLargeIntField.Create(Self);
  AField.FieldName := 'ID';
  AField.DataSet := FDTable;
  AField.Name := AField.DataSet.Name + 'IDField';

  AField := TWideStringField.Create(Self);
  AField.Size := 80;
  AField.FieldName := 'Name';
  AField.DataSet := FDTable;
  AField.Name := AField.DataSet.Name + 'NameField';

  FDConnection.Params.Values['database'] := DBName;
  FDConnection.Connected:= True;
  FDTable.CreateTable(False, [tpTable]);
end;

procedure TForm3.PopulateTable1;
var
  i : Integer;
begin
  FDTable1.Open;

  for i:= 1 to 1000 do begin
    FDTable1.InsertRecord([i, 'Row ' + IntToStr(i)]);
  end;
  FDTable1.Close;
  //FDConnection1.Commit;

  FDTable1.Open;
end;

procedure TForm3.TestDataMove;
var
  Item : TFdMappingItem;
begin
  Item := FDDataMove1.Mappings.Add;
  Item.SourceFieldName := 'ID';
  Item.DestinationFieldName := 'ID';

  Item := FDDataMove1.Mappings.Add;
  Item.SourceFieldName := 'Name';
  Item.DestinationFieldName := 'Name';

  FDDataMove1.Source := FDTable1;
  FDDataMove1.Destination := FDTable2;
  FDDataMove1.Options := FDDataMove1.Options - [poOptimiseSrc];
  FDDataMove1.Execute;

  FDConnection2.Connected := False;
  FDTable2.Open;
end;
procedure TForm3.btnBatchMoveClick(Sender: TObject);
begin
  TestDataMove;
end;

procedure TForm3.FormDestroy(Sender: TObject);
begin
  FDConnection1.Close;
end;

end.