B4J Tutorial [WebApp] Concurrent access to SQLite databases

Discussion in 'B4J Tutorials' started by Erel, Apr 14, 2014.

  1. Erel

    Erel Administrator Staff Member Licensed User

    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:
    SQLite.ExecNonQuery("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.basic4ppc.com/b4j/help/jsql.html#sql_begintransaction
  2. luke2012

    luke2012 Well-Known Member Licensed User

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

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

    Erel Administrator Staff Member Licensed User

    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.
  4. ivan.tellez

    ivan.tellez Active Member Licensed User

    Hi Erel, according to https://www.sqlite.org/wal.html,
    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?

  5. Erel

    Erel Administrator Staff Member Licensed User

    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).
  6. ivan.tellez

    ivan.tellez Active Member Licensed User

    Ok, thanks.

    JUAN CARLOSORDOÑEZ Member Licensed User

    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?

  8. Erel

    Erel Administrator Staff Member Licensed User

    Please start a new thread for this question.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice