Created attachment 131778 [details]
example database over /tmp/bug_20170307a/example.odb

@Terrence : when I click on "Save", I get an indefinite hang requiring force kill on MacOS :

Version: 5.4.0.0.alpha0+
Build ID: 6de4ecbe1372c0242f406d45cf999969616f87dc
Threads CPU : 2; Version de l'OS :Mac OS X 10.12.3; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group


which unfortunately is bug 105062, sot I can not test your scenario on Mac.

Tamás: can you look at this?

In my bug description, I reversed expected and actual results.  That's
what I get for pasting sections of my prepared description into the
bug submission assistant.

I apologize for the confusion.

I confirm this in
版本：5.4.3.2
Build ID：92a7159f7e4af62137622921e809f8546db437e5
CPU 线程：2; 操作系统：Linux 4.13; UI 渲染：默认; VCL: gtk2; 
区域语言：zh-CN (zh_CN.UTF-8); Calc: group

I also noted that, after data entry, the save toolbar icon is grayed-out. When you close the data entry window, there is no warning alerting you to save data. When you close and reopen the data entry window, the records you just entered is there, but the records are gone when you close the odb file and reopen. That is to say, the data is not inserted successfully into the firebird database.

To reproduce this, you need to enable experimental features in Tools -> Options.
You need to create the dir /tmp/bug_20170307a/ and copy the fdb file (firebird database) in attachment 131743 [details] to this dir (the path is important because the ODB file connects to this database). 
Then download the ODB file in attachment 131778 [details] and open this with Base, and try to enter some records in the table.

checked it with:
Version: 6.1.0.0.alpha1+
Build ID: 47dc3115f12ff16dc326b6edd12c46e6a6ef1843
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-05-17_00:32:17
Locale: en-US (en_US.UTF-8); Calc: group

Still a bug.

Also checked that issuing a sql 'commit' command in the SQL Window does write the data, it does, and the 'needs saving' visual queue is reset for the ODB file which closes without asking to save again.

Also want to mention another scenario and how it does work properly even with this bug, IMO

Using this same example file I did the following:

1 Created a query using the designer, saved the querydef into the ODB. This falgs the file as 'modified' (visual clue is red).

2 Opened the authors table.

3 added two new records and closed the table.

4 Opened the SQL window and issued the 'commit' command. 

At this point the 'modified' flag for the ODB file is still set (visual clue is still red) and that is correct. Closing the file now I am prompted to save.

5. I answered 'save without saving'.

6 open the file, open the authors table

The two new records are saved.

sorry for the typos.

should of been: closed the file 'without saving it'

Opened again and the two records are present, the querydef 'Query1' is gone.

I need to fix (retract) the last two comments

I ran that scenario and a few variants more and the results are more complicated than that one scenario - sorry for the distraction here. I'll try to make something coherent out of these results and post an email to the ML.

Thank you, Drew, for your attention.

In other bug reports, IIRC, I have seen some questioning of whether
data changes should be saved upon close-without-saving.  I think we
are caught between two strong and mutually contradictory expectations:

(1) A user thinking of data entry into a database will expect changes
    to become permanent at the end of each transaction, for some
    meaning of transaction.  If Base is linked to an external
    database, this is how we work.  Right?

(2) A user thinking of maintaining an office document will expect
    close-without-save to discard the whole session.

I see no way to reconcile these expectations.  Comments welcome.

Created attachment 142217 [details]
ScreenShot_processOpenFiles

Some more information here:

What I did was in Libo6.1 Alpha 1 (build from the 18th) on 64 bit Linux.

First I opened a firebird external file ODB (106463_example.odb).

I added a table, Table1. Closed the file.

Open an HSQLdb embedded file (Students_1.odb).

Connected to the datastore by listing the tables, but no changes to anything otherwise, closed the file.

Opened a Writer file (getting started chapter). Then closed it.

The screen shot shows the system monitor view of what files the soffice process still has open, and that the LibreOffice UI shows no open files.

NOTE that both odb files are still listed as is a collection of ancillary files from the respective SDBC drivers. The writer files is not still open.

I am experiencing the same problem, with an external Firebird database.

The problem seems to me that unless a modification to the inner structure of the database is performed (tables, constraints, etc), data modification is not committed and gets lost when the odb file is closed.

In other words, if data is added or modified in a table, this modifications are visible until the odb file is open, but when the file is closed, no request to save is made, the transaction is rolled back and the data changes lost. 

If instead some inner modification is performed before closing the odb file (like adding a new table), the database is immediately committed. Incidentally, when closing the odb file, it will be asked to save the data but the answer seems to have no impact: the modification both to inner structure and data are already commited, no matter the answer...

(In reply to Terrence Enger from comment #11)
> Thank you, Drew, for your attention.
> 
> In other bug reports, IIRC, I have seen some questioning of whether
> data changes should be saved upon close-without-saving.  I think we
> are caught between two strong and mutually contradictory expectations:
> 
> (1) A user thinking of data entry into a database will expect changes
>     to become permanent at the end of each transaction, for some
>     meaning of transaction.  If Base is linked to an external
>     database, this is how we work.  Right?
> 
> (2) A user thinking of maintaining an office document will expect
>     close-without-save to discard the whole session.
> 
> I see no way to reconcile these expectations.  Comments welcome.

I am not expert of the way Firebird databases work, but it seems like the data changes are not permanent AT ALL, unless some kind of action (e.g. inserting a new table) is performed. One could debate on which expectation is the more appropriate, but this looks to me more like a bug.

*** Bug 122967 has been marked as a duplicate of this bug. ***

(In reply to Robert Großkopf from comment #15)
> *** Bug 122967 has been marked as a duplicate of this bug. ***

So - as a work around (scripting vs core code fix) for this there are three approaches that I see as fairly straightforward.

1 - catch the close file event and check if it is an ODB with firebird_file sdbc and issue a commit against the connection prior to releasing it. (though I have not tested if that even is called before the connection is closed I believe it is) 
or
2 - catch the sub_component close event inside the ODB and issue a commit. (I prefer this one)
or
3 - catch the file save event for individual ODB files by updating the properties of the physical files and issue the commit. (easiest script to write and it works)

Maybe this merits a post on the ask.lo service with example script for those.

(In reply to Drew Jensen from comment #16)
> (In reply to Robert Großkopf from comment #15)
> > *** Bug 122967 has been marked as a duplicate of this bug. ***
> 
> So - as a work around (scripting vs core code fix) for this there are three
> approaches that I see as fairly straightforward.
> 

> 3 - catch the file save event for individual ODB files by updating the
> properties of the physical files and issue the commit. (easiest script to
> write and it works)
> 
> Maybe this merits a post on the ask.lo service with example script for those.

So just to run it by folks - here is the simplest single line macro I could come up with:

First add a library to the ODB, if you don't already have one, and add this sub procedure.

Sub comitWhenSave()

	thisdatabasedocument.datasource.getconnection("","").commit()
	
End Sub

Then open the Customize dialog and assign that macro to the Save Document Event for the ODB file.

That's it, data will be written to disk whenever you do a File Save. (but not a File Save-As ;)

(In reply to Terrence Enger from comment #11)

> (1) A user thinking of data entry into a database will expect changes
>     to become permanent at the end of each transaction, for some
>     meaning of transaction.  If Base is linked to an external
>     database, this is how we work.  Right?

Yes, that's how it works when Base acts as a front-end to an external database. IMO, in a form or "table data entry view", each time one
 - switches from one record to another
 - closes the form
 - clicks the "save record" button in the "form navigation" toolbar
that's a commit (end of a transaction).

Most DBMSs have a mode where they "autocommit" at the end of each statement, and it is the default mode; it seems firebird does not, so IMO the SDBC driver should take care of issuing a commit at the end of each call to any of execute, executeQuery, executeUpdate in Statement.cxx and PreparedStatement.cxx

> (2) A user thinking of maintaining an office document will expect
>     close-without-save to discard the whole session.

IMO, no, that's not how Base works as front-end to an external database.

When connecting to an external database, saving the ODB file and saving (committing) the data to the database is not linked, not one way nor the other. One can save the ODB file without committing data, and one can commit data without saving the ODB file.

It is only with a database embedded in the ODB file that "saving" the data requires saving the ODB!!!

(In reply to Lionel Elie Mamane from comment #18)

> Most DBMSs have a mode where they "autocommit" at the end of each statement,
> and it is the default mode; it seems firebird does not, so IMO the SDBC
> driver should take care of issuing a commit at the end of each call to any
> of execute, executeQuery, executeUpdate in Statement.cxx and
> PreparedStatement.cxx

Searching a bit more around made me found the "isc_tpb_autocommit", which seems to do what we want and may be easier than issuing a commit as an additional SQL command. At least for DML statements, that seems to be meant to work "out of the box".

For DDL statements, I'm not sure. Needs testing.

DML statement = statements that change *data* like UPDATE, INSERT, DELETE as opposed to DDL statements that change data structure (CREATE TABLE, ALTER TABLE, CREATE INDEX, etc).

https://www.firebirdsql.org/manual/isql-set.html says that in isql, to autocommit DDL statements, one needs to issue one-time at connection "SET AUTODDL ON". I'm not sure if that makes isql issue a "commit" statement to the firebird core or if it sets an option in the firebird core.

OTOH, http://tracker.firebirdsql.org/browse/CORE-3825 seems to say they fixed a but around how the isc_tpb_autocommit option interacts with DDL commands, so maybe it works for DDL commands, too. Needs testing.

It would probably be useful to look at how the JDBC driver does it.

(In reply to Lionel Elie Mamane from comment #18)
> (In reply to Terrence Enger from comment #11)
> 
> > (1) A user thinking of data entry into a database will expect changes
> >     to become permanent at the end of each transaction, for some
> >     meaning of transaction.  If Base is linked to an external
> >     database, this is how we work.  Right?
> 
> Yes, that's how it works when Base acts as a front-end to an external
> database. IMO, in a form or "table data entry view", each time one
>  - switches from one record to another
>  - closes the form
>  - clicks the "save record" button in the "form navigation" toolbar
> that's a commit (end of a transaction).
> 
> Most DBMSs have a mode where they "autocommit" at the end of each statement,
> and it is the default mode; it seems firebird does not, so IMO the SDBC
> driver should take care of issuing a commit at the end of each call to any
> of execute, executeQuery, executeUpdate in Statement.cxx and
> PreparedStatement.cxx

Firebird most certainly does support Autocommit and by default. It must be turned off if you don't want it and the Firebird File SDBC does just that and turns it off during the connection process. I can't tell you the code line but it is set to false after the connection is finished initialization.

(In reply to Drew Jensen from comment #20)

> Firebird most certainly does support Autocommit and by default.

It seems to me it is definitely not the default at the C API level; one needs to pass isc_tpb_autocommit as an option. Now, other Firebird UIs or drivers probably set this option by default :)

> It must be turned off if you don't want it and the Firebird File
> SDBC does just that and turns it off during the connection process.

Reading the Firebird SDBC code, I discover it already has all the plumbing to deal with that, but in Driver.cxx:

Reference< XConnection > SAL_CALL FirebirdDriver::connect(...)
{
    (...)
    if (url == "sdbc:embedded:firebird")
        pCon->setAutoCommit(true);
    (...)
}

Our documentation https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XConnection.html#aa6e7212d2813c2b19d99dd1dc9e0ba11 clearly says:

  By default, new connections are in auto-commit mode.

*But* in Connection.cxx:

Connection::Connection()
(...)
    , m_bIsAutoCommit(false)


So, in my opinion this needs to be changed to "true", and the corresponding lines in Driver.cxx remover as not necessary anymore.

(In reply to Lionel Elie Mamane from comment #21)
> (In reply to Drew Jensen from comment #20)
> 
> > Firebird most certainly does support Autocommit and by default.
> 
> It seems to me it is definitely not the default at the C API level; one
> needs to pass isc_tpb_autocommit as an option. Now, other Firebird UIs or
> drivers probably set this option by default :)


Arrghhh - yes, with the C connector it is whatever you want it to be - facts are such pesky things :)

Thank you for looking at the core code for the setting here.

(In reply to Drew Jensen from comment #17)
> So just to run it by folks - here is the simplest single line macro I could
> come up with:
> 
> First add a library to the ODB, if you don't already have one, and add this
> sub procedure.
> 
> Sub comitWhenSave()
> 
> 	thisdatabasedocument.datasource.getconnection("","").commit()
> 	
> End Sub

Or enable autocommit when the ODB file is opened:

Sub enableAutoCommit()
	Dim DBDocUI as Object
	DBDocUI = ThisDatabaseDocument.currentController
	if not DBDocUI.isConnected then
		DBDocUI.connect
	end if
	DBDocUI.ActiveConnection.setAutoCommit(true)
End Sub

And execute that macro when the file is opened, before making any changes (customise it to the odb's "Open Document" event).

Not tested, but should work. Not necessary anymore after https://gerrit.libreoffice.org/67015 is applied.

(In reply to Lionel Elie Mamane from comment #23)
> (In reply to Drew Jensen from comment #17)
> > So just to run it by folks - here is the simplest single line macro I could
> > come up with:
> > 
> > First add a library to the ODB, if you don't already have one, and add this
> > sub procedure.
> > 
> > Sub comitWhenSave()
> > 
> > 	thisdatabasedocument.datasource.getconnection("","").commit()
> > 	
> > End Sub
> 
> Or enable autocommit when the ODB file is opened:
> 
> Sub enableAutoCommit()
> 	Dim DBDocUI as Object
> 	DBDocUI = ThisDatabaseDocument.currentController
> 	if not DBDocUI.isConnected then
> 		DBDocUI.connect
> 	end if
> 	DBDocUI.ActiveConnection.setAutoCommit(true)
> End Sub
> 
> And execute that macro when the file is opened, before making any changes
> (customise it to the odb's "Open Document" event).
> 
> Not tested, but should work. Not necessary anymore after
> https://gerrit.libreoffice.org/67015 is applied.

Just tried it (the macro) with 6.2 and it throws an error (same issue existed with the embedded HSQL engine) "This call not allowed when sharing connections".

IIRC With HSQL embedded this came up whenever you needed to do a multi-table commit, then the only way was to create a new connection, in a script, with the option set during the call to create the connection. Then use this new connection for the SQl statements needed instead of the default created by the Base runtime code.

Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/e9e1813a6ebe6e3015e04347b15a18aebc4e0fa7%5E%21

tdf#106463 set new firebird connections as autocommit by default

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

I'd be grateful if someone tested the fix. Thanks.

Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/9247233195996d82f3530fcef0310ccb6e1c2304%5E%21

tdf#106463 set new firebird connections as autocommit by default

It will be available in 6.2.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

Running a recent local build on debian-buster, I see the data saved as
expected.  I am setting status VERIFIED FIXED.

Thank you, Lionel.