SQLite and locked databases
lazarus / 16-Databases

----------------------------------------------------------------------
eshjim · 2016-06-25 22:29

A topic that appears to crop up frequently in the forum is that of a
"Database locked" error message being received when changes attempt to
be committed.  As a newcomer to SQLite, I have been struggling with
this same problem, and despite following all of the advice, this
problem persists.  Using the standard code:

  SQLQuery1.Edit;
  SQLQuery1.Upda]"]>Blockedde := UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;

always produces the error (NB, even when using CommitRetaining).  Then
by chance I came across the PP4S website
http://www.pp4s.co.uk/main/tu-db-sqlite.html, which confirms that this
error does occur and suggests that the executable be run directly
rather than from Lazarus.  Lo and behold, this does work.  Obviously,
this is an exceptionally cumbersome workaround.  Is this a known
problem and are there any settings within the Lazarus IDE which would
prevent the locking error occurring?

----------------------------------------------------------------------
Leledumbo · 2016-06-25 23:50

Don't set Active to true in the object inspector, as the IDE will then
open connection to the database and write lock happens. Provide
another mechanism (OnCreate, button click, whatever) to activate that
after application starts. AFAIK there's a pragma that allows multiple
connections to write the same db, but I don't remember the name or
value to set.

----------------------------------------------------------------------
eshjim · 2016-06-26 10:45

Quote from: Leledumbo on June 26, 2016, 01:50:24 amDon't set Active to
true in the object inspector, as the IDE will then open connection to
the database and write lock happens. Provide another mechanism
(OnCreate, button click, whatever) to activate that after application
starts. AFAIK there's a pragma that allows multiple connections to
write the same db, but I don't remember the name or value to set.

Do you mean make all of the components (Transaction, Queries and
Connection) Active in the code or just some of them?  I have tried
this in various combinations in the Form Create method but whichever
component is made Active first receives an "External SIGSEGV" error.

----------------------------------------------------------------------
JanRoza · 2016-06-26 11:55

I always use a datamodule in my projects and have the activating of
the database and opening of the tables in an OnCreate procedure of the
datamodule (see example below).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TDataModule1.DataModuleCreate(Sender: TObject);var  strDbName:
String;begin  // First assemble the complete databasename including
path  strDbName := SysUtils.ExtractFilePath(ParamStr(0)) +
'Flightlog.db';  DbConnection.DatabaseName := strDbName;   // Open
database  DbConnection.Connected := True;  TransactionDb.Active :=
True;   // Open all tables  qryFoutMeldingen.Active := True; 
qryInstellingen.Active := True;  qryLogboek.Active := True; 
qryRegistraties.Active := True;  qryStartMethoden.Active := True; 
qryStatistieken.Active := True;  qryStatistiekTypen.Active := True; 
qryTerreinen.Active := True;  qryTotalen.Active := True; 
qryTotalenVluchten.Active := True;  qryVluchten.Active := True;end; 
This always works and also guarantee that no matter where a user puts
your program and database (as long as they are in the same folder)
your program will always find it and open it.
And as Leledumbo already stated: don't set anything to active via the
IDE object inspector (except temporarily  when you want to design your
forms in the IDE).

Hope this helps you.

----------------------------------------------------------------------
eshjim · 2016-06-26 13:34

Thank you, that was a great help. I too always use a data module and
putting the code in there does work.  It has, however, thrown up
another error which I don't think is related.  To become accustomed to
Lazarus and sqlite, I have created a test record collection database. 
The master table contains the CD reference number and title and the
detail table the individual tracks.  The master table's primary key is
REFERENCE (string field as it is alpha-numeric).  The detail table is
primary keyed on REFERENCE (to link with the master) and TRACKORDER
(integer). Both tables scroll with each other correctly so the
underlying structure is sound.  However, after posting a test entry in
the detail table, I get an error message stating the field 'REFERENCE'
can not be found in the master query.  After terminating the program
and going back into it, the detail record HAS been posted correctly! 
As stated in my original question, the committing and updates are
carried out in the After Post event of the Detail query.  Do you have
any suggestion as to what might be causing this?

----------------------------------------------------------------------
snorkel · 2016-07-13 16:52

To avoid locked databases enable WAL mode on your SQLite database.
you can get really good concurrency this way, the only gotcha is all
processes that access your DB have to be on the same device.
This means you can't have the DB file on a smb or any type of network
share.