B4J Tutorial [WebApp] Concurrent access to SQLite databases

SQLite databases are very easy to use as they don't require any additional software or configuration.

SQLite support for concurrent access is not comparable to server based databases such as MySQL and others.
However they can still be perfect for small / medium solutions, especially if there aren't many writings (ExecNonQuery).

B4J v2.00 adds support for concurrent access to SQLite databases. You need to follow these instructions:

- Use a single SQL object that is shared by all classes. Don't use a ConnectionPool with SQLite database.
- When you create the database you need to set the journal mode to wal:

sql.ExecQuerySingleResult("PRAGMA journal_mode = wal")

You only need to set the journal mode to wal once.
Read more about wal mode: https://www.sqlite.org/wal.html

This mode allows multiple readers and a single writer to access the database at the same time. B4J SQL library (when initialized with InitializeSQLite) will serialize writers access.

- When writing to the database make sure to use transactions and always close the transaction: http://www.b4x.com/b4j/help/jsql.html#sql_begintransaction

 

[luke2012]

Very interesting ! Great work ! I'll try soon this solution.

P.S.
@Erel The BeginTransaction and TransactionSuccessful statements are only mandatory for this solution or also with a standard App SQLite database (local db) ?

 

[Erel]

Explicitly creating transaction is not required in both server apps and desktop apps as SQLite creates a transaction automatically if you didn't started one.
However if you intend to execute multiple non-query commands then an explicit transaction significantly improves the performance.

 

[ivan.tellez]

Hi Erel, according to https://www.sqlite.org/wal.html,

All processes using a database must be on the same host computer; WAL does not work over a network filesystem.

So, With B4J you can have have Concurrent access to SQLite databases as long as they are from the same PC?

I this is true, is there a solution to use SQLite by multiple APPs in a LAN?

Thanks

 

[Erel]

The solution discussed here is for server apps, where the server is the only process who accesses the database. The server itself can serve many concurrent clients.
The database file should be a local file. For a network database I recommend you to use a different solution (such as MySQL).

 

[ivan.tellez]

Ok, thanks.

 

[JUAN CARLOSORDOÑEZ]

I made an application to make ordering food in a restaurant. I Use sqlite database and works great but now I need the database not take it from the tablet but from the pc. How do I get the DBFileDir be routed to pc?

How can i conect my Tablet to my pc from my App?

Thanks!!!

 

[Erel]

Please start a new thread for this question.