Recent

Author Topic: Write to SQLite database  (Read 3803 times)

Milsa

  • Sr. Member
  • ****
  • Posts: 253
Write to SQLite database
« on: March 19, 2021, 12:29:19 pm »
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.
I work with Lazarus 2.0.10, FPC 3.2.0, SVN 63526
This information is actual to: 1st Aug 2020

Zvoni

  • Hero Member
  • *****
  • Posts: 627
Re: Write to SQLite database
« Reply #1 on: March 19, 2021, 02:33:51 pm »
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
« Last Edit: March 19, 2021, 02:40:19 pm by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

Milsa

  • Sr. Member
  • ****
  • Posts: 253
Re: Write to SQLite database
« Reply #2 on: March 19, 2021, 02:58:03 pm »
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.
I work with Lazarus 2.0.10, FPC 3.2.0, SVN 63526
This information is actual to: 1st Aug 2020

cdbc

  • Sr. Member
  • ****
  • Posts: 361
    • http://www.cdbc.dk
Re: Write to SQLite database
« Reply #3 on: March 20, 2021, 02:46:36 pm »
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
If it ain't broke, don't fix it ;)

Zvoni

  • Hero Member
  • *****
  • Posts: 627
Re: Write to SQLite database
« Reply #4 on: March 20, 2021, 06:08:04 pm »
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.
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

Milsa

  • Sr. Member
  • ****
  • Posts: 253
Re: Write to SQLite database
« Reply #5 on: March 21, 2021, 12:53:56 am »
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.
I work with Lazarus 2.0.10, FPC 3.2.0, SVN 63526
This information is actual to: 1st Aug 2020

Milsa

  • Sr. Member
  • ****
  • Posts: 253
Re: Write to SQLite database
« Reply #6 on: May 19, 2021, 07:26:43 pm »
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.
I work with Lazarus 2.0.10, FPC 3.2.0, SVN 63526
This information is actual to: 1st Aug 2020

 

TinyPortal © 2005-2018