SQLite and locked databases

[eshjim]

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.UpdateMode := 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]

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]

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.

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]

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][+][-]

1. procedure TDataModule1.DataModuleCreate(Sender: TObject);
2. var
3.   strDbName: String;
4. begin
5.   // First assemble the complete databasename including path
6.   strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'Flightlog.db';
7.   DbConnection.DatabaseName := strDbName;
8.  
9.   // Open database
10.   DbConnection.Connected := True;
11.   TransactionDb.Active := True;
12.  
13.   // Open all tables
14.   qryFoutMeldingen.Active := True;
15.   qryInstellingen.Active := True;
16.   qryLogboek.Active := True;
17.   qryRegistraties.Active := True;
18.   qryStartMethoden.Active := True;
19.   qryStatistieken.Active := True;
20.   qryStatistiekTypen.Active := True;
21.   qryTerreinen.Active := True;
22.   qryTotalen.Active := True;
23.   qryTotalenVluchten.Active := True;
24.   qryVluchten.Active := True;
25. end;
26.  

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]

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]

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.