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.

To be sure: so you already have a proper Firedac connection set up and now you want to use the SQLite ATTACH to add another database file to the current connection? And please edit your question to include the Delphi version. — Jan Doggen
Why do you need to use the same FDConnection with both database files? Why not have a separate connection for each of them. — MartynA
To be honest, I am not sure whether I tried this already. I will check it. Thanks for the suggestion. — JGMS
I want to use a single FDConnection because I copy data from the second database into the existing database by using "insert...select" statements. — JGMS
Well, you can do that regardless, if you're generating the Sql statements in code. — MartynA

MartynA MartynA · Accepted Answer · 2016-03-16T10:36:28

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.

Error using SQLite ATTACH - DETACH in Delphi

1 Answers