Hello,
This is most likely to be answered by filing a bug report.
Reading records, as in your Query statement, is no problem. However, when writing records the data needs to be committed - applied to the database. With HSQLDB embedded (and other DB connections) this is done automatically when the .odb is closed. Firebird embedded is the exception. For some reason it was chosen that the user must actually save the .odb for the changes to be saved (committed).
Have not found a method to do this from Calc with the Base file opened or closed.
Here is the unusual part. If you have a Base file connected to a Firebird File (this is simply the Firebird DB external to the .odb) then updates to the DB work from Calc even with the Base file closed. There was a fix applied through tdf#106463 and may be a reason this works from Calc even though the actual problem was not related.
Edit 2019-10-29:
First, from comment question, you can create a connection without registering the database. Instead of using the registered name:
dbNAME = "REGISTERED_DB_NAME"
Db = Context.getByName(dbNAME)
use the .odb URL(this is Linux files):
dbNAME = "file:///home/YOUR_DIRECTORY/YOUR_BASE_FILE.odb"
Db = Context.getByName(dbNAME)
Have also done some further testing using current & older LO versions.
To be clear, Firebird embedded is the problem. Using a current LO v6.3.2.2, HSQLDB embedded is updated from basic code with the Base file opened or closed and using registered or non-registered DB.
Firebird embedded does need the Base file to be opened and saved before the Calc file is closed. It need not be open during the entire process. This is again due to committing the data. Issuing a commit
command from Calc SQL creates an error I have not been able to resolve as of yet. Issuing a commit
command from Base Tools->SQL
has no effect either.
It is important to note, when using this macro code from Calc, if the affected Base file is opened and closed before the Calc file, the connection is fully broken in Calc. Only recovery thus far is to close and re-open the Calc file.
I bring up this commit issue because of the aforementioned connection to using a Firebird file. Reload LO v6.0.2.1 which was before the bug fix noted above. With the Firebird file, could issue a commit command and the data was saved. Going back to the current version, there is no need to issue the command as the data is saved automatically. There is also no need to save the Base file as with the embedded version. Data is automatically saved.
To me this processing is inconsistent and should be reported as a BUG!
Edit 2019-11-11:
There is a workaround to the problem of Firebird embedded needing to be open to update it. This is noted in tdf#128607
After updating with SQL and before closing the connection, issue a flush
command (Conn
is the connection):
Conn.getParent().flush