Write to SQLite database
lazarus / 16-Databases

----------------------------------------------------------------------
Milsa · 2021-03-19 11:29

I have 2 running applications / connections with one SQLite database.

First connection can write to database and second can only read. This
is handled by the program. Application knows what access type is valid
for it.

I want to write to the database from first connection but I got error.

Does exists any option to really set read only from second connection?
First connection must to have full access.

The problem with the consistency of the database in the 2nd connection
after writing in the 1st connection is not important.

----------------------------------------------------------------------
Zvoni · 2021-03-19 13:33

TSQLite3Connection has a Method "SetOpenFlags" which takes as Argument
TSQLiteOpenFlags

And those are

TSQLiteOpenFlag = (
    sofReadOnly,
    sofReadWrite,
    sofCreate,
    sofNoMutex,
    sofFullMutex,
    sofSharedCache,
    sofPrivateCache,
    sofURI,
    sofMemory
  );
TSQLiteOpenFlags = set of TSQLiteOpenFlag;


btw: Have you activated WAL-Mode?

EDIT: SetOpenFlags is a private Method
You'd actually use the Public Property OpenFlags

----------------------------------------------------------------------
Milsa · 2021-03-19 13:58

Thank you.

This helps me with second application. Second application can not
write to database since this edit:
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   if not
Config.Wri]"]>Blockedde then    DBConnection.OpenFlags :=
[sofReadOnly]; But I still have problem with first application (R/W
access). It can not write if second application running. Second
application running in R/O mode.

WAL-mode:
I am using standard SQLite setting in Lazarus with transactions. Am I
using WAL-mode? I don't know.

----------------------------------------------------------------------
cdbc · 2021-03-20 13:46

Hi
You could try to use a 'Lock file', you create one before you access
the database and delete it afterwards.
Each application check for a lock file, if it's there, wait till it's
gone, then create a new and do your business and delete it again.
It's sort of a poor mans criticalsection, but at application level.
HTH
Regards Benny

----------------------------------------------------------------------
Zvoni · 2021-03-20 17:08

Quote from: Milsa on March 19, 2021, 02:58:03 pmThank you.

This helps me with second application. Second application can not
write to database since this edit:
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   if not
Config.Wri]"]>Blockedde then    DBConnection.OpenFlags :=
[sofReadOnly]; But I still have problem with first application (R/W
access). It can not write if second application running. Second
application running in R/O mode.

WAL-mode:
I am using standard SQLite setting in Lazarus with transactions. Am I
using WAL-mode? I don't know.
Look at 3)
https://sqlite.org/wal.html
It‘s a PRAGMA-call
You should be able to add it to the Params of your connection.

----------------------------------------------------------------------
Milsa · 2021-03-20 23:53

Quote from: cdbc on March 20, 2021, 02:46:36 pmHi
You could try to use a 'Lock file', you create one before you access
the database and delete it afterwards.
Each application check for a lock file, if it's there, wait till it's
gone, then create a new and do your business and delete it again.
It's sort of a poor mans criticalsection, but at application level.
HTH
Regards Benny
https://forum.lazarus.freepascal.org/index.php/topic,53254.msg394000.h
tml#msg394000

I am using it. But:

1. First application has full access and works.
2. Then I open second application with read only access.
3. It works (read only) but first application lost full access and
does not work correctlly.  <--- problem
4. Then I close second application. First application still does not
work.
5. I close first and open first again. It works. Go to step 2.

----------------------------------------------------------------------
Milsa · 2021-05-19 17:26

I have a sufficient solution:
When I start with write access, I copy the database to a new file, and
when I open with read-only aceess, the read-only file opens directly.
That's enough for my needs. Solved.

Thanks for help.