search

govert / SQLiteForExcel

A lightweight wrapper to give access to the SQLite3 library from VBA.
MIT License
256 stars 81 forks source link

Issue with latest SQlite DLL #14

Closed rggit2019 closed 3 years ago

rggit2019 commented 3 years ago

I have a Excel VBA that works fine with the SQLITE DLL downloaded together with the SQLiteforExcel package. But if I replace them with the latest ones (3.33) I get error during sqlite3close operation. (Error 5). It seems some locks are not getting released.

Is it not advised to change DLL versions or is there a change in behaviour in newer SQLite?

Sorry for posting something that is not directly related to this module and I am using unsupported DLL obviously, but just wanted to raise this in case..

govert commented 3 years ago

I would not expect you to have any problems when using a newer version of SQLite, and don't believe the behaviour changed.

Are you seeing the error with the test macro?

Can you confirm that you don't see the error with an older version of SQLite and do see it with newer, when running the same code against the same database file?

It could also be that some SQLite behaviour changed around the locking and closing.

In the SQLite documentation I see (http://www.sqlite.org/c3ref/close.html):

Ideally, applications should finalize all prepared statements, close all BLOB handles, and finish all sqlite3_backup objects associated with the sqlite3 object prior to attempting to close the object. If the database connection is associated with unfinalized prepared statements, BLOB handlers, and/or unfinished sqlite3_backup objects then sqlite3_close() will leave the database connection open and return SQLITE_BUSY. If sqlite3_close_v2() is called with unfinalized prepared statements, unclosed BLOB handlers, and/or unfinished sqlite3_backups, it returns SQLITE_OK regardless, but instead of deallocating the database connection immediately, it marks the database connection as an unusable "zombie" and makes arrangements to automatically deallocate the database connection after all prepared statements are finalized, all BLOB handles are closed, and all backups have finished. The sqlite3_close_v2() interface is intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary.

Error 5 means SQLITE_BUSY, so it could be that you're running into one of the mentioned cases.

rggit2019 commented 3 years ago

Thanks for the reply and I am sorry , please close the issue. I just did a test again and I am unable to replicate the issue. It works fine in both DLLs.

The code has changed since I posted the earlier message but at that point the issue came up when I click "Update DB" command more than once but vanished when I switched back to the old DLL. I had no other explanation.

I will continue with the newest DLL and raise again if I can replicate it.