B4J Question sqlite db read lock with multiple users

I would have a read of this http://www.sqlite.org/whentouse.html and maybe consider using an RDBMS like MySql etc.

Don't spend time looking at the Jackcess library either.

Even though Jet (commonly and incorrectly referred to as "Access") databases were designed to support concurrency control and transactions, Jackcess is rather crude and really only for use by a single "opener."

If all openers only read data you may have some success.

Worse yet the B4A/B4J Jackcess wrapper library uses an obsolete version of the Java Jackcess. That does several things incorrectly and has fewer features than current versions, and could result in data corruption. It works, and is ok as a desperation measure. But you'd probably only use it for importing Jet data into some other DBMS.

SQLite is great for what it is for, but it is no Jet. Sadly we don't have good Jet support so that will not address your issues. Even sadder, there probably aren't any other reliable file-based DBMSs that deal properly with concurrent access and transaction isolation. Considering how fantastic Jet really is, it sure gets beat up a lot by its critics.

So the extra resource consumption and management overhead of a client-server DBMS may be your only option.

@Hilton can you post the exact error message? Are you sure that all ResultSets are closed when you are finished with them?

Hi Erel,
Thank you that was the problem - scrupulous closing of resultsets.

For anyone else's benefit:

In the past when dealing with SQLite I always did my own referential and concurrency checking which I cheerfully set about doing with B4J. When I bumped into the locking problem, I decided to change the database to Postgres as an exercise and all worked well - in spite of not closing resultsets.

After your advice, I switched back to SQLite (which is my preference) and I had to copy resultsets to maps (to keep the data available for as long as I needed it) and then close the resultset immediately. Now the ri/concurrency checking and updating all work well.

I remember reading your examples including closing the resultset but it did not stick in the grey matter. Anyway, all is well that ends well.

Regards,
Hilton.