How can I delete the SQLite DLL when I'm finished with it if the OS thinks it's still in use?

Question

How can I unlock or delete a file that is in use, so that I can delete it? The file in question is used by my own application.

More specifically, my application is using the freeware Zeos Lib. When opening and saving my database the sqlite3.dll file must reside in the same directory as my application to work correctly.

I want my application to be 100% standalone, so I have added the sqlite3.dll as RC_DATA to my project, and whenever I need to use it (ie, open or save database), I extract it to the same folder as my application. Once the open or save operation has completed, I would like to delete the sqlite3.dll file, and no one would even know it was there or have to worry about missing libraries etc. (While I can appreciate that some of you may not like the idea of storing the libraries inside the application, I have my reasons for doing this: I don't want my end-users knowing what is behind the functioning of my application (SQL), and they also don't need to worry about missing dynamic link libraries.)

The problem is, I can successfully extract the sqlite3.dll and use it for my operations, but the file becomes locked by my application (until I close my application), which brings me to either:

1. Force unlock the sqlite3.dll file, without closing my application

2. Force delete the sqlite3.dll file

Unless of course there is another suggestion?

Here is a sample of extracting and using it. No direct calls need to be made such as LoadLibrary etc from my part; the Zeos Lib units must take care of this, so long as the sqlite3.dll is in the same directory as the application.

procedure ExtractResource(ResName: String; Filename: String);
var
  ResStream: TResourceStream;
begin
  ResStream:= TResourceStream.Create(HInstance, ResName, RT_RCDATA);
  try
    ResStream.Position:= 0;
    ResStream.SaveToFile(Filename);
  finally
    ResStream.Free;
  end;
end;

//Open procedure
var
  sFileName: String = ExtractFilePath(ParamStr(0)) + 'Sqlite3.dll';    

if OpenDialog1.Execute then
begin
  ExtractResource('RES_SQLITE3', sFileName);
  ... //process my database
  ...
  ... // finished opening database
  if FileExists(sFileName) then
        DeleteFile(sFileName);
end;

EDIT

I think what I am attempting to do is not very practical, it is not a good idea to this as STATUS_ACCESS_DENIED earlier commented on. I have decided it is best not to proceed with what I set out to do.

Answer 1

You should better use static linking of the SQLite3 engine instead of relying on an external dll. By including the .obj into your .dcu SQLite3 unit.

It will also add some nice features like ability to use FastMM4 as the memory manager for the SQlite3 engine also (speed up), and potentially some nice low-level features (like encryption).

See for instance:

• our Open Source wrapper (which will include on-the-fly file encryption and some nice features like Virtual Tables coded as Delphi classes);
• DISQlite which is not free;
• AnyDAC which is not free at all but an open door to other DB engines;
• Aducom free but old version (some updated information).

Answer 2

Simple, don't do it. There is a reason why the file is locked. Usually it's a very good reason, such as some other process (or even your own) still using it. Find out what keeps the file locked (e.g. using Process Explorer) and as long as it's your process make sure you did free everything. E.g. FreeLibrary after LoadLibrary etc ...

If you absolutely must remove the file, try DeleteFile and when that fails call MoveFileEx with MOVEFILE_DELAY_UNTIL_REBOOT to remove the file upon reboot.

You can have another MoveFile or MoveFileEx in between to "rename" the file while it is in use (this usually works on the same partition). But this is only necessary if you rely on the file name and therefore another instance of your program could get blocked if the old (locked) stale file still exists.

There are methods to do what you want, but they should not end up in code that is released to end users. It's basically boiling down to hackery that would - for example - use injected threads to close/unlock files in the entity that holds them locked. But it's bad form. Try to avoid it.

Answer 3

Though you can't delete a file that's in use, you can rename it. Just rename the dll to something like SQLITE3.DELETE.guid. On startup you can have your app try to delete any sqlite.delete.* file so after the file frees it will go away. -don

Answer 4

It looks like the LibraryLoader in ZPlainSqLite3.pas is what is loading the DLL. You could try running the code from the finalization section before trying to delete the DLL:

  if Assigned(LibraryLoader) then
     LibraryLoader.Free;

Answer 5

You should first make sure the file isn't still in use. In your case, it is still in use because the database library loaded the DLL and hasn't freed it yet. Since you've already disconnected from the database, it's probably not still in active use, but the OS doesn't know that — if a DLL is loaded, the OS assumes it's still needed and disallows deletion.

When you connect to the database, Zeos finds the corresponding database driver (in this case, TZSQLiteDriver, from ZDbcSqLite.pas) and asks it to load its functions. But when you disconnect from the database, Zeos does not ask the database driver to unload its functions. That would be wasteful in a typical program, where there may be several connections established and destroyed over the lifetime of the program.

If you're sure that there are no open connections to the database, you can unload the functions yourself. The loader for SQLite is in ZPlainSqLite3.pas. Although you could free the loader object entirely, that may cause problems later since there are other parts of Zeos that expect it to still be around when they need it. Instead, just tell it to unload:

ZPlainSqLite3.Loader.FreeNativeLibrary;

That causes the object to set flags indicating that it is unloaded, so if you need to use it again, it will reload everything.

---

If you really wanted to get fancy, you could try writing your own TZNativeLibraryLoader descendant that automatically fetches the DLL from the resource when loading and deletes the file when unloading. Then you wouldn't need to worry about database-connection lifetimes in the rest of your program. You could just connect and disconnect like everyone else.

Answer 6

There's a couple solutions available that allow you to compile the SQLite libraries into your exe, rather than using the DLL. I think that'd be a much better approach if you really must have a single file executable. What you are basically trying to duplicate by extracting/deleting the DLL is the functionality of an installer, and you really shouldnt. That approach is just asking for support problems.

Also keep in mind that your app may need to run with administrator rights to be able to extract the dll and save it into the program files directory.

If you can live with a DLL being installed along with the executable, you can rename the DLL file to something else (ie, Database.dll), and make changes in the zeos code so that it points to the new DLL name. Then the SQLite functionality would not be immediately apparent.