[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

Lionel Elie Mamane  changed:

   What|Removed |Added

 Status|NEW |ASSIGNED
   Assignee|libreoffice-b...@lists.free |lio...@mamane.lu
   |desktop.org |

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #21 from Lionel Elie Mamane  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #20 from Drew Jensen  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #19 from Lionel Elie Mamane  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #18 from Lionel Elie Mamane  ---
(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!!!

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #17 from Drew Jensen  ---
(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 ;)

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #16 from Drew Jensen  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

Robert Großkopf  changed:

   What|Removed |Added

 CC||erichdermuel...@posteo.de

--- Comment #15 from Robert Großkopf  ---
*** Bug 122967 has been marked as a duplicate of this bug. ***

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #14 from Francesco  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #13 from Francesco  ---
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...

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #12 from Drew Jensen  ---
Created attachment 142217
  --> https://bugs.documentfoundation.org/attachment.cgi?id=142217=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

Terrence Enger  changed:

   What|Removed |Added

 CC||drewjensen.in...@gmail.com

--- Comment #11 from Terrence Enger  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #10 from Drew Jensen  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #9 from Drew Jensen  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #8 from Drew Jensen  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

--- Comment #7 from Drew Jensen  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

Thomas Lendo  changed:

   What|Removed |Added

 Blocks||51780


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=51780
[Bug 51780] [META] Default to Firebird not HSQLDB in Base (for _new_ files)
-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 106463] Data Records Not Saved to External Firebird Database File

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=106463

Kevin Suo  changed:

   What|Removed |Added

Summary|missing commit to external  |Data Records Not Saved to
   |firebird database file  |External Firebird Database
   ||File

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs