how to delete TFDTable after open

[tobenschain 0]

I am unable to delete a TFDTable after creating it. I tried Close and Disconnect.

[weirdo12 17]

14 minutes ago, tobenschain said:

I am unable to delete a TFDTable after creating it. I tried Close and Disconnect.

 

What database are you using? Show us  the code you are using that is not working as expected along with an explanation of what you think should be happening.

[tobenschain 0]

Using SQLite

 

Module_DB: TFDTable;

 

Module_DB.Close;
Module_DB.Disconnect;
 

SysUtils.DeleteFile('Test.DB');

[tobenschain 0]

The file should be gone.

[tobenschain 0]

if I try to delete the file manually it says it's still in use by the program 

[Remy Lebeau 1279]

11 minutes ago, tobenschain said:

if I try to delete the file manually it says it's still in use by the program 

Are you sure it is YOUR program that has the file open?  Did you verify that, such has with a tool like SysInternals Process Explorer?  Maybe the file is open by the OS, or an antivirus/antimalware, etc.

Edited May 14 by Remy Lebeau

[weirdo12 17]

In the direction Remy suggested, maybe TFDConnection,Connected = true in the IDE.

[tobenschain 0]

I wasn't qualifying the Close statement 

[Cristian Peța 96]

Closing a table will not close the connection to the database and if you are using PRAGMA locking_mode=EXCLUSIVE then you need to close the connection to unlock the file.

Using locking_mode=NORMAL and deleting the file with an open connection is not a good thing. In this case you don't need to close the table either.

https://www.sqlite.org/pragma.html#pragma_locking_mode

[Brian Evans 90]

This might not apply but: most databases have temporary tables of various lifetimes. 

 

SQLite has temporary tables that are database session specific and go away on their own when the session is closed. 

[tobenschain 0]

I tried   Module_Con.Close;

 

Didn't help 

[Cristian Peța 96]

Have you checked what PRAGMA locking_mode is returning? You can use TFDQuery for this.

With UniDAC implicit is EXCLUSIVE and maybe also for FireDAC.

 

Can you delete the file before opening any connection?

Better try this after a system restart (or unlock the file) because if you kill the processes in debug, for example, before the connection is closed the file will remain locked.

 

PS: I prefer to use locking_mode=NORMAL. There is some performance penalty but I don't need any more to respond with: restart the OS.

Edited May 16 by Cristian Peța

[tobenschain 0]

<<Have you checked what PRAGMA locking_mode is returning?>>

 

I use: ExecSQL('PRAGMA locking_mode = NORMAL');

 

<<Can you delete the file before opening any connection?>>

 

The file is being created.

[tobenschain 0]

Created, Copied and Deleted 

[Cristian Peța 96]

54 minutes ago, tobenschain said:

Created, Copied and Deleted 

That means you solved the issue using locking_mode = NORMAL?

Then probably you have not closed the connection properly before trying to delete.

[tobenschain 0]

This is what I do

 

  Module_DB.Close;
  Module_DB.Disconnect;
  Module_Con.Close;
 

[Cristian Peța 96]

This is working for me. Probably you are doing something else in your code.

program Project1;

{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  System.Variants,
  System.Classes,
  FireDAC.Stan.Intf,
  FireDAC.Stan.Option,
  FireDAC.Stan.Error,
  FireDAC.UI.Intf,
  FireDAC.Phys.Intf,
  FireDAC.Stan.Def,
  FireDAC.Stan.Pool,
  FireDAC.Stan.Async,
  FireDAC.Phys,
  FireDAC.Phys.SQLite,
  FireDAC.Phys.SQLiteDef,
  FireDAC.Stan.ExprFuncs,
  FireDAC.VCLUI.Wait,
  FireDAC.Stan.Param,
  FireDAC.DatS,
  FireDAC.DApt.Intf,
  FireDAC.DApt,
  FireDAC.Phys.SQLiteWrapper.Stat,
  Data.DB,
  FireDAC.Comp.DataSet,
  FireDAC.Comp.Client;

var
  FDConnection1: TFDConnection;
  FDTable1: TFDTable;

begin
  FDConnection1 := TFDConnection.Create(nil);
  FDTable1 := TFDTable.Create(nil);

  FDConnection1.DriverName := 'SQLite';
  FDConnection1.Params.Database := ExtractFilePath(ParamStr(0)) + 'test.db';
  FDTable1.Connection := FDConnection1;
  FDTable1.TableName := 'table1';

  //Default FDConnection1.Params.Values['LockingMode'] is 'Exclusive'
  FDConnection1.Open;
  FDConnection1.ExecSQL('CREATE TABLE table1 (a INTEGER)');
  FDTable1.Open;
  FDTable1.InsertRecord([10]);
  FDTable1.Close;
  DeleteFile(FDConnection1.Params.Database);//here is not working because LockingMode = Exclusive
  FDConnection1.Close;
  DeleteFile(FDConnection1.Params.Database);//here is working for me
end.

 

[tobenschain 0]

I needed:

 

try
  FDQuery1.ExecSQL('insert into MyTab(code, name) values (:code, :name)', [100, 'Tokyo']);
except
  on E: EFDDBEngineException do begin

//  E.Message - Actual error message. For the DBMS messages only Message property is used. 
//  E.ErrorCode - DBMS-specific error code. 
//  E.Kind - DBMS-independent error code and other properties. 

    if E.Kind = ekUKViolated then
      ShowMessage('Please enter unique value !');
    raise;
  end;
end;
 

[tobenschain 0]

Many thanks for the example.