Write to SQLite database

[Milsa]

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]

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]

Thank you.

This helps me with second application. Second application can not write to database since this edit:

Code: Pascal  [Select][+][-]

1.   if not Config.WriteMode then
2.     DBConnection.OpenFlags := [sofReadOnly];
3.  

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]

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]

Thank you.

This helps me with second application. Second application can not write to database since this edit:

Code: Pascal  [Select][+][-]

1.   if not Config.WriteMode then
2.     DBConnection.OpenFlags := [sofReadOnly];
3.  

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]

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

https://forum.lazarus.freepascal.org/index.php/topic,53254.msg394000.html#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]

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.