Description: I am currently working on a project editing various tabs in scalc, loading into a database to create mail merge and address label documents. I had, scalc, sbase, swriter( label ) all open. and eventually i saved & closed sbase, swriter and all programs and only scalc is open I click file > new > database > connect to spreadsheet >finish > and then save.. I choose the existing database ( which i closed) .. However i get an error saying it cannot be overwritten , pointing that file is open... While file is closed. So somewhere along the way , a lock on a file in soffice suite is not getting unlocked , unless all other open programs in soffice suite are closed. Version: 6.2.5.2 (x64) Build ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159 CPU threads: 4; OS: Windows 6.3; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: CL Steps to Reproduce: 1. Open excel / scalc 2. Create a database & save as some database with a name 3. Save & Close the database 4. Now make changes to excel/ scalc. 5. Save scalc. 6. You wish to update your database with the new data on the database u created. 7. Create new database > follow database steps and register database > but you want to overwrite this over the previous database. 8> Are you able to overwrite or unable to ( since database file appears locked ) and preventing overwrite. Of-course you can create as a new database, but that is not the point. The persistence of a closed file as a locked file is not an expected feature. >> Now attempt this by closing all the soffice tools, restart and try... It will work... No, Please Don't tell me user profile. I have deleted and re-initialized user profile umpteen times. So profile is NOT CORRUPTED. Actual Results: This is a redundant column. Its like repeating the things I said above in description and steps to reproduce. Please remove this redundant column ( " Actual Results ") Expected Results: A lock on a file that is NOT open in write mode must be immediately released. A read only file must never be locked. Reproducible: Always User Profile Reset: Yes Additional Info:
@Narayan : just a quick question to help me understand - why are you re-creating a new database (Step 7) once you have updated your Calc sheet ? If your original ODB file points to the Calc sheet that you have modified, it should automatically take up any changes you made in the Calc sheet ?
Tested in Version: 6.4.0.0.alpha0+ Build ID: 80386c73e172975572f265018333c6231a6b3b22 CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx; Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US Calc: threaded 1) Create a new Calc document 2) Enter field names in first row 3) Fill in subsequent rows with corresponding data 4) Save Calc sheet. Close file. 5) Create new database ODB file. 6) In DB creation wizard, connect to existing DB, choose Spreadsheet as datasource. 7) Follow wizard to end accepting defaults and save database file. 8) Close database file. 9) Re-open Calc file used as datasource. 10) Add more rows of data to Calc file. 11) Save edited Calc file and then close. 12) Re-open ODB file, click on Tables icon, then double click on table to display data. All newly added data is displayed along with initially entered data.
Perhaps I have misunderstood something in your explanation or else there is some other piece of information we are missing, but no repro for me, on macOS, at least.
(In reply to Alex Thurgood from comment #1) > @Narayan : just a quick question to help me understand - why are you > re-creating a new database (Step 7) once you have updated your Calc sheet ? > > If your original ODB file points to the Calc sheet that you have modified, > it should automatically take up any changes you made in the Calc sheet ? We are veering off into different issue here. But anyways let me lay it out 1) Refresh table button does not refresh data. 2) There is no auto refresh. Data is refreshed only when all SBASE open programs are closed and reopened, and opening the table shows the refreshed view of the saved data from excel. 3) WHen I open sbase and open the table, and then open the spread sheet to exit data>> the excel file opens as only read only. So why is an open view of the table in database , placing the spreadsheet in read only mode? I see that dbase is designed as write/commit consistent database, similar to oracle , unlike ms sql which is read consistent. Note: In past version of MS SQL Server, Uncomitted data in MS SQL table will be viewed by other users who read data from the table. Not sure thats the case in newer versions. However..here is an analogy.. But in both types of database, viewers of data, cannot put source table under lock or read only mode. So why do , when i open dbase > open table > then open scalc > Open spread sheet > puts spread sheet in read only mode >>> Like i said this is entirely different issue, <<<
I cannot edit source data < excel spread sheet > now , it shows read only in open dialogue, but when i checked file properties . read only is unchecked. This is bizzare
Created attachment 153273 [details] Source data is locked Now I cannot commit changes i made in source data and have to lose all changes or re-save as different file name. See attached file, violating all rules of consistency in file handling.
Spreadsheets as datasources are read only when opened via an ODB file. https://help.libreoffice.org/Common/Database_1 "The following database types are read-only types in LibreOffice Base. From within LibreOffice Base it is not possible to change the database structure or to edit, insert, and delete database records for these database types: Spreadsheet files Text files Address book data"
(In reply to NARAYAN from comment #4) > 2) There is no auto refresh. Data is refreshed only when all SBASE open > programs are closed and reopened, and opening the table shows the refreshed > view of the saved data from excel. Yes, this is how working with spreadsheets as table sources has always behaved within LibreOffice, and prior to that, with OpenOffice.org. > 3) WHen I open sbase and open the table, and then open the spread sheet to > exit data>> the excel file opens as only read only. So why is an open view > of the table in database , placing the spreadsheet in read only mode? See my other response - spreadsheet tables are read only. If your bug report centres around a request for enhancement that spreadsheet data should be writable from within, I believe that such a request is already present in the bugtracker, in which case, this current report would be a duplicate.
A possible alternative to the read-only problem with character-delimited text tables (e.g. CSV) is to load them into a hsqldb database using the TEXT TABLE support feature of this engine : http://hsqldb.org/doc/2.0/guide/texttables-chapt.html This is obviously more involved than simply pointing your ODB file to a spreadsheet file.
The disposal of the database connection context on shutdown (at least with registered databases and read-only datasources) is also, from what I recall, another behaviour inherited from OpenOffice.org and is currently as expected.
So to summarize : Spreadsheets can not be saved while a database file (ODB) lock that uses that spreadsheet as a read-only source is still loaded in memory. This lock is only released when LibreOffice is shut down. As I understand it, this is the expected behaviour of the code. It is inherited from OOo times and has never been changed (or at least not substantially). I sincerely doubt it ever will be for a number of reasons, one of which being the complicated nature of ensuring that mutexes behave within the OOo/LO code, especially with regard to Base.