mORMot Open Source

igors233

Member

Registered: 2012-09-10

Posts: 241

SQLite proper cleaning

Hi, I'm a new sqlite user and I need to execute some simple select query, retrieve result and clean up (release file locks).
I'm having problems with release part, after this procedure is finished there is a still some handle left on db file, could you help me with a proper way of releasing it?
I'm using statically linked sqlite library (just added SynSQLite3Static to uses list) and nigtly build from a couple of days ago.

function GetSqliteResultset(const ADB: string; const ASQL: string; ATimestamptoDT: TBrowserTimestamptoDT): TResultSet;
var
  conn: TSQLDBSQLite3ConnectionProperties;
  db: TSQLDBSQLite3Connection;
  rows: ISQLDBRows;
begin
  if not FileExists(ADB) then
    Exit;

  // Establish sqlite connection, execute passed query and fetch results
  conn := TSQLDBSQLite3ConnectionProperties.Create(ADB, '', '', '');
  try
    try
      db := conn.MainConnection as TSQLDBSQLite3Connection;
      rows := conn.Execute(ASQL, []);

      while rows.Step do
      begin
         // some result processing
      end;
    except
    end;

    // rows := nil;
    // db.Disconnect;
    // conn.ClearConnectionPool;
  finally
    conn.Free;
  end;
end;

Thanks,
Igor

Last edited by igors233 (2013-12-23 15:42:29)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SQLite proper cleaning

I suppose you reached a limitation of the Delphi compiler itself.
In your code, you are mixing interface-based variables (rows) and classic classes (conn, db).
It is just not working.
But not due to our units.
This is just some weird behavior of the Delphi compiler automatic reference counting of interfaces.

See http://synopse.info/forum/viewtopic.php?pid=9174#p9174

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: SQLite proper cleaning

> I suppose you reached a limitation of the Delphi compiler itself.
> In your code, you are mixing interface-based variables (rows) and classic classes (conn, db). It is just not working.

Thank you very much for your reply, I really don't know better.
Can you point me in the right direction (methods, classes) to use so that I could completly avoid interfaces?

Here is a simplified order of tasks in case it might help:

CopyExistingSQLiteDBtoTmpDir;
try
  ExecuteMethodToRetrieveResults;
finally
  DeleteCopiedSQLiteDBFromTmpDir; // Deletion fails since there is a file lock left
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SQLite proper cleaning

The easiest is to create a sub-procedure in which you define your Rows interface as local variable.
Then it will force to clean the Rows just after use.

Something like that:

function GetSqliteResultset(const ADB: string; const ASQL: string; ATimestamptoDT: TBrowserTimestamptoDT): TResultSet;
var
  conn: TSQLDBSQLite3ConnectionProperties;
  db: TSQLDBSQLite3Connection;
procedure GetResult;
var
  rows: ISQLDBRows;
begin
  rows := conn.Execute(ASQL, []);
  while rows.Step do
  begin
     // some result processing
  end;
end;
begin
  if not FileExists(ADB) then
    Exit;

  // Establish sqlite connection, execute passed query and fetch results
  conn := TSQLDBSQLite3ConnectionProperties.Create(ADB, '', '', '');
  try
    try
      db := conn.MainConnection as TSQLDBSQLite3Connection;
      GetResult;
    except
    end;

    // rows := nil;
    // db.Disconnect;
    // conn.ClearConnectionPool;
  finally
    conn.Free;
  end;
end;

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: SQLite proper cleaning

> The easiest is to create a sub-procedure in which you define your Rows interface as local variable.
> Then it will force to clean the Rows just after use.

It worked, thank you very much.