SQLite Busy Timeout for TSqliteConnection?
lazarus / 16-Databases

----------------------------------------------------------------------
snorkel · 2016-02-16 15:52

Hi,
I just noticed there appears to be no way to set the SQLite busy
timeout for the stock Lazarus SQLite connection component.
This causes a problem if a row in the db is locked even for a second,
and when the connection reports the db is locked it never checks again
even if the other process has commited the transaction.
For example in SQLiteStudio, I can update a table row and leave the
transaction in a uncommited state, then in my app, i try to write to
that row it immediately reports it as locked instead of waiting for a
few seconds.  I then commit the trans in sqlitestudio and my app
continues to report it as locked even though it is no longer locked.

Just wondering what others are doing in this case?  Are people using
Zeos instead which appears to have a way to set the busy timeout.

I have the transaction component set to commit.

This is in Lazarus 1.6 RC2 on win32.

----------------------------------------------------------------------
bigeno · 2016-02-16 17:51

https://www.sqlite.org/pragma.html#pragma_busy_timeout
https://www.sqlite.org/c3ref/busy_timeout.html

----------------------------------------------------------------------
Never · 2016-02-16 18:38

There is in sqlite3 unit [ sqlite3_busy_timeout(ASqliteHandle),
(Your_value)); ]
and
You can set pragmas with sql as well ex:['PRAGMA recursive_triggers =
TRUE;']

----------------------------------------------------------------------
snorkel · 2016-02-17 05:46

Thanks,
I knew about the pragma calls, however I believe a call back has to be
used on the client for optimal results.

I switched everything in my app to zeoslib and it works perfectly now.
The TSQLiteConnection would almost always report the database was
locked even with the db in WAL mode.
Zeos allows the busy time out to be set in the connection properties
list and implements a callback so the client knows the db is ready to
write.

Only issue with zeos was it treats booleans as N or Y instead of 0 or
1, but I was able to find where the sqlite driver sets the booleans
and changed it.