SqLite multiple user

[Alex Clay]

Hi,
Per the SQLite docs on WAL mode, you need to set:

PRAGMA journal_mode=WAL;

PRAGMA looks to be a command you issue via SQL: https://www.sqlite.org/pragma.html
So try this:

1. Connect to SQLLite in Omnis
2. Issue PRAGMA journal_mode=WAL; as an execute in your database

Alex

[Coralie]

Thank you Alex,
It seems to have done something but it made me lost the connection to the database.
Technically, when there are two users and one of them wants to update/insert something, I get the $nativeerrortext as “database is locked” and if one of them is not logged in, the transaction does work. From there and what I have read, the WAL should indeed fix that.
Coralie.

[Alex Clay]

It may be more of a drastic change, but have you considered moving to a backend that’s built for concurrent access? PostgreSQL is an excellent, open-source and free option, and we use it to manage thousands of concurrent clients across our servers.
I’m not sure why running the PRAGMA command resets the connection. The Omnis docs indicate the SQLite connection runs with the PERSIST journal mode: https://developer.omnis.net/onlinedocs/Programming/09serv.html#troubleshooting
The page also suggests the mode can be changed to OFF, so I would expect you could set it to WAL. Perhaps email Omnis support to ask about this?

[Coralie]

I spent several hours trying to figure out how to set up correctly the WAL mode. I have tried some different PRAGMA set ups, as explained in the doc.
From what I understood and tried without success, there is no way to have multiple writers and readers concurrently, without having to filter user’s authorizations throughout the code.
I am therefore going to switch my database system to PostgreSQL.
Thank you very much Alex!
Regards,
Coralie.

[Alex Clay]

Great! I think you’ll enjoy working in PostgreSQL.