B4J Question [SOLVED] [Server][WebSockets] SQLite clarifications

[mindful]

Hello, I have a project in B4J with jServer and ABMaterial and right now I am using as a database MariaDB or MySQL. I would like to add SQLite so the users of the web app would not need to have or install a database server.

My web app has a couple websockets that run in different threads. I have seen the tutorial here: https://www.b4x.com/android/forum/threads/webapp-concurrent-access-to-sqlite-databases.39904/ and I will need to use WAL.

So my questions are:
1. Can I initialize more than one SQL object pointing to my database file ? Or do I need to setup a global sql object and use it in all of my project ?
2. Do I need to close the database connection after I finish my queries ? Or will I close just the result set and leave the connection open for later use?
3. KeyValueStore2 uses SQLite and i am using it in my project to keep some settings values, but I saw that I doesn't use WAL ? Do I need to enable it ? I have only one global object as KeyValueStore but I use it also in all of my websockets ...

 

[Erel]

See the short tutorial.

1. "Use a single SQL object that is shared by all classes. Don't use a ConnectionPool with SQLite database"
2. No. Don't close the SQL object.
3. Yes, you need to add the the pragma command so it will use WAL. Note that you need to recreate the database for this.

You can see that it is in WAL mode by checking the result of PRAGMA journal_mode or by checking the database files. There should be a db-wal and db-sdm files.

 

[keirS]

1. Use a global connection.
2. My inclination would be to close the connection but I haven't used SQLite in a server application.
3. I would enable WAL.

 

[keirS]

3. Yes, you need to add the the pragma command so it will use WAL. Note that you need to recreate the database for this.

You can see that it is in WAL mode by checking the result of PRAGMA journal_mode or by checking the database files. There should be a db-wal and db-sdm files.

Why do you need to recreate the DB? Nothing in the SQLite documentation about this being required.

 

[Erel]

You are correct. You can change the journal mode of an existing database.

Point #2 in your previous post is incorrect. You should not close the connection as there is no connection pool involved.

 

[mindful]

Thanks @Erel and @keirS

 

[keirS]

You are correct. You can change the journal mode of an existing database.

Point #2 in your previous post is incorrect. You should not close the connection as there is no connection pool involved.

OS dependent but on Windows Servers I have seen very strange effects when keeping files open all the time in a service. Even when explicitly flushing to disk.