SQLite Locked & sqlite3_busy_timeout
lazarus / 16-Databases

----------------------------------------------------------------------
NelsonN · 2011-02-19 16:34

I am working on a database application and working on getting it 100%
compatible with MySQL, and SQLite. Right now I have MySQL and SQLite
going and it is working as it should — until now.

I am currently working on a form with a DBGrid and I have to allow the
DBGrid to remain connected, this is where the locked SQLite issue
comes in.

I would like to use sqlite3_busy_timeout, as I know this is what I
have to use, just before the ApplyUpdates command so that I can write
the changes to the SQLite database.

Code: [Select]if [using SQLite] then begin
sqlite3_busy_timeout(TSqlite3Dataset(SQLQuery1.DataSource.DataSet).Sql
iteHandle, 1500);
end;

Code: [Select]  SQLQuery1.Edit;
  SQLQuery1.Post;
  SQLQuery1.ApplyUpdates;

The problem is that the use of sqlite3_busy_timeout has gone over my
head. I would appreciate it if someone could show me some code that
sets this up for use.

----------------------------------------------------------------------
eny · 2011-02-19 17:02

Quote from: NelsonN on February 19, 2011, 05:34:43 pmI would like to
use sqlite3_busy_timeout, as I know this is what I have to use, just
before the ApplyUpdates command
Any particular reason you want to use that function?
(I've tried using specific SQLite functions together with
TSQLite3Dataset, but that failed in the end.)

----------------------------------------------------------------------
NelsonN · 2011-02-19 17:25

Quote from: eny on February 19, 2011, 06:02:43 pmAny particular reason
you want to use that function?
(I've tried using specific SQLite functions together with
TSQLite3Dataset, but that failed in the end.)

I haven't had any issues with SQLite in Windows, my title says it all
— a locked database. Read here:

http://www.lazarus.freepascal.org/index.php/topic,9504.msg47586/topics
een.html#msg47586

Maybe LuizAmérico himself will help me out here, as it seems he's the
only one to post about sqlite3_busy_timeout.

----------------------------------------------------------------------
LuizAmérico · 2011-02-19 19:07

What type is SqlQuery1?

TSqlite3Dataset is not related to sqqldb.TSqlQuery so the code below
wont work

TSqlite3Dataset(SQLQuery1.DataSource.DataSet)

QuoteMaybe LuizAmérico himself will help me out here, as it seems he's
the only one to post about sqlite3_busy_timeout
If you are using TSqlite3Dataset from unit sqlite3ds it's as simple as

sqlite3_busy_timeout(Sqlite3Dataset1.SqliteHandle, 1500);

This must be called just after creating the handle.

To make this automatic add an handler to OnGetHandle event:

procedure TDataCenter.GenericDatasetGetHandle(Dataset: TDataSet);
begin
  sqlite3_busy_timeout(TSqlite3Dataset(Dataset).SqliteHandle, 1500);
end;

----------------------------------------------------------------------
NelsonN · 2011-02-19 19:26

Quote from: LuizAmérico on February 19, 2011, 08:07:53 pmWhat type is
SqlQuery1?

TSqlite3Dataset is not related to sqqldb.TSqlQuery so the code below
wont work

TSqlite3Dataset(SQLQuery1.DataSource.DataSet)

I was thinking about this, but wasn't sure. I am using
sqqldb.TSqlQuery. This makes my code easily maintainable across
database platforms.

Quote from: LuizAmérico on February 19, 2011, 08:07:53 pmIf you are
using TSqlite3Dataset from unit sqlite3ds it's as simple as

sqlite3_busy_timeout(Sqlite3Dataset1.SqliteHandle, 1500);

This must be called just after creating the handle.

Thank you very much! This makes my SIGSEGV error even clearer. This is
the missing piece of the mystery for me.

Now, for some reason my SQLite database is not reporting being locked
anymore. I had closed everything because I left, when I came back I
tried again. Maybe Firefox's plug-in SQLite Manager was holding on to
the database. I get no issues now and I can make any number of edits
and for SQLite just issue a commit when closing the form in the Close
method and the changes are posted.

Again, thank you for the info. I appreciate it very much.