Is it possible that once a database connection is initialized B4J doesn't recognize if there are changes to said database?
It has 0% to do with B4J and 100% to do with UCanAccess's JDBC driver.
1) Unlike SQLite's JDBC driver, with UCanAccess, you can open the same database multiple times (done via B4J's SQL's Initialize method). Internally, UCanAccess will return you the previously created connection object. In that object, UCanAccess keeps track of the number of active connections that a DB has. Each Initialize will increment this counter, each Close will decrement that counter. If this counter reaches 0 due to an invoked Close method, UCanAccess will shut down the underlying DB and the next call to Initialize will then create a new connection object and re-open the underlying database. If this counter does not reach 0, the database will not ever be reloading (via a Close statement)
2) Even if the counter in point #1 above reaches 0, out of the box, UCanAccess does not immediately close the the underlying DB, but waits for 2 minutes before doing so. If within two minutes the DB is opened again, UCanAccess will just re-use the still open connection (incrementing the active connections counter). The amount of time that UCanAccess waits for can be controlled with two configuration options:
inactivityTimeout (since UcanAccess 0.1.1): it is used only with the default memory mode (memory=true). If there are no active connections for the inactivityTimeout period (in minutes) HSQLDB will be temporarily shut down and any filesystem resources will be released. It will be restarted at the next connection. Setting inactivityTimeout=0 can be used for avoiding this behaviour. Default = 2 minutes.
immediatelyReleaseResources (replaces singleConnection which has been deprecated since UCanAccess 3.0.6): it is used for for ETL jobs, scheduled tasks, or "one-shot" use of UCanAccess with only one connection open. All resources (memory and filesystem) will be released at the closing of the connection. Default=false.
Note: inactivityTimeout is not applicable when setting memory=false. At that point, Close
never really hardly ever closes the database (see next statement).
Of these two configuration options, immediatelyReleaseResources is the more interesting one. With this option, a final Close (where active connections = 0) will close the DB immediately (update: even with memory=false). If active connections <> 0, then the DB will remain open, even with this option set.
Solution #1:
a) Use
one SQL object to open the database via Initialize. This can be done by declaring a Global variable in Main and initializing it before starter the server service. Do not initialize another SQL object for the database used anywhere else.
b) Modify the JDBC URL to include the immediatelyReleaseResources=True option. Using the example provided in post #1, the modified version would be
sql.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://${path};immediatelyReleaseResources=True"$)
c) Introduce code into the project that
1) Stops all clients from reading/writing the database
2) Calls Close on the current database
3) Replaces the current database
4) Opens the new database -> step a)
5) Inform clients that the database is available again
Solution #2:
a) Keep opening the DB multiple times (Note: no need for setting mmediatelyReleaseResources=True)
b) Introduce code into the project that
1) Stops all clients from reading/writing the database
2) Closes the database via the provided unloadDB method of the UcanaccessConnection class
Dim sql As SQL
sql.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://${Main.DBPath}"$)
Dim jo As JavaObject = sql
jo = jo.GetFieldJO("connection")
jo.RunMethod("unloadDB", Null)
3) Replaces the current database
4) Inform clients that the database is available again
Notes:
1) I'm not an UCanAccess expert, so another more elegant solution may be out there. Point in case: UCanAccess supposedly is able to tell when an underlying change occurred from outside sources and then reload the database as necessary. I've not been able to reproduce this (in this limited, read data only case). Renaming the database (effectively deleting it) did not stop the application from reading information from the database (albeit it was the same information, same tables over and over again). I expected the application to crash once the database file was renamed. No such "luck".
2) Solution #1 would be preferable, since UCanAccess's ability to open the same local database may be unique. This way, the code could be adapted to other similar databases, such as SQLite. The unloadDB method call is not a JDBC standard. It's an extension of UCanAccess's implementation of the JDBC Connection object (SQLite has not such method in its connector).
Resources:
UCanAccess Getting Starte page:
http://ucanaccess.sourceforge.net/site.html#examples
UCanAccess source code:
https://sourceforge.net/projects/ucanaccess/files/
SQLite source code:
https://github.com/xerial/sqlite-jdbc
