June 27, 2019 at 4:15 pm #21247
While working on my web application under OS10, I did some tests and ran into an issue.
The app is using a SQLite database and is supposed to have several users at the same time, updating, creating or deleting datas, sharing the same db. I read that SQLite doesn’t allow that, unless you convert the database connection to WAL mode. How can I do that?
Coralie.June 27, 2019 at 4:26 pm #21248June 27, 2019 at 5:49 pm #21249
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.June 27, 2019 at 11:37 pm #21252
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?June 28, 2019 at 12:10 pm #21253
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!
Coralie.June 28, 2019 at 2:33 pm #21263
Great! I think you’ll enjoy working in PostgreSQL.
You must be logged in to reply to this topic.