There are some asks for enhancement which couldn't be solved, because the only part of a query, which is been saved in the *.odb-file, is the name and the pure SQL-code. This is saved in «content.xml»: <db:queries> <db:query db:name="Verkauf" db:command="SELECT * FROM "Verkauf""/> </db:queries> No more informations were saved anywhere in the *.odb-file. So you - couldn't add comments to the SQL-code and save them - couldn't format the width or something eles of a column and save it - couldn't set a column invisible and save it All this seems to work, but when saving the query, close it an open it again it isn't there any more.
This is an enhancement request that goes back to early OOo2 days, but was never implemented. Confirming.
Adding self to CC if not already on
*** Bug 90360 has been marked as a duplicate of this bug. ***
*** Bug 116567 has been marked as a duplicate of this bug. ***
Created attachment 141314 [details] test db with querydef containing comment
So I tried this with Libo6.1 Alpha (daily builds). It looks to me the problem is that the falg for a query definition requiring 'Run Direct SQL' is not being saved and that is the problem. To see what I mean grab the test db I attached. Open the query definition with 'Edit SQL view' You will see that the comment I added was saved. Try to run the query and it fails. Now click on 'Run SQL Command Directly' Run the query again and it passes. Save the query. The 'Run SQL Command Directly' flag is not sticking with the QueryDef.
Checked this with the latest 6.1 daily build. The sql direct flag is being stored properly and when that is set all the formatting and commenting is retained. I would say this works.
The one area that is not possible is the ability to set the column widths, or anything to do with the grid layout on screen when you run the query and have that saved. IIRC that is created at runtime so there is nothing to save there - that would be a major enhancement IMO. However all of that functionality is already supplied in a Form using a table grid control (three clicks is all you need to generate that from a querydef) - in the table grid control you can rearrange the column order, adjust the column widths, hide columns or even add columns. Set any of those at design time and it is retained.
(In reply to Drew Jensen from comment #7) > Checked this with the latest 6.1 daily build. > > The sql direct flag is being stored properly and when that is set all the > formatting and commenting is retained. > > I would say this works. Isn't a bug about code, which will be stored with direct SQL. It's about formatting, comments ... If you save a Base-file you will save all the formatting of the tables, also the hiding of columns, for example. This does work for tables, but doesen't work for queries. If you type code with comments its will work in GUI. You could input data in the query. If you close and reopen such a edited query the comments are lost, the breaks are lost ... You could change to direct SQL, but there is no possibility to - input data in query - also to format columns - also to hide columns.
(In reply to Robert Großkopf from comment #9) > (In reply to Drew Jensen from comment #7) > > Checked this with the latest 6.1 daily build. > > > > The sql direct flag is being stored properly and when that is set all the > > formatting and commenting is retained. > > > > I would say this works. > > Isn't a bug about code, which will be stored with direct SQL. It's about > formatting, comments ... > If you save a Base-file you will save all the formatting of the tables, also > the hiding of columns, for example. This does work for tables, but doesen't > work for queries. > If you type code with comments its will work in GUI. You could input data in > the query. If you close and reopen such a edited query the comments are > lost, the breaks are lost ... > You could change to direct SQL, but there is no possibility to > - input data in query > - also to format columns > - also to hide columns. Right, I understand now. So, this would be something that should be part of an ODF specification update, right? Granted if LibreOffice just did it as a LO extended flavor of ODF 1.2 it's doubtful too many folks would howl but standards are standards so maybe they should (softly). No idea how one moves to do that, to request an ODF specification like that. I suppose it might open up some thought as what could be done with that kind of data when a query is used as the data source to other objects in the ODF spec, such as dataforms, perhaps treat the settings as a default (template) for consumer controls attached to the dataform.
*** Bug 144701 has been marked as a duplicate of this bug. ***
(In reply to Drew Jensen from comment #10) Formatted queries, external databases joins, and macros are the extent of a database for most analysts who have no need for forms or reports. Export to a wordprocessor or spreadsheet to tart up data. So another decade before a basic usability feature in MS-Access V1 is considered? You have to be kidding! Writer and calc are always enhanced to provide 'standardisation' with MS-Office. Base is bad enough now, at least fork a fix for 'testing', run with /enhancements option if need be.
Forgot to add, it is essential for adhoc joins to views.
Possible workaround: BG64-BaseGuide.pdf, Chapter 5 Queries More rapid access to queries using table views, Tip: ...the formatting of columns in a view is retained when the database is closed, unlike columns in a query. (I didn't realise views were implemented.)
View column formatting is not retained in Base V7.2, despite the guide. Users could run a macro could manage this functionality without adding to ODF specification: * save column attributes to system table on close query * apply attributes from system table on open query Functionality models would probably be calc, writer tables or MS-Access. ========== Version: 7.2.0.3 (x64) / LibreOffice Community Build ID: 2a7ea282da28d665a7dc086360567b4aea27bf08 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-AU (en_AU); UI: en-GB Calc: threaded
(In reply to Drew Jensen from comment #10) > So, this would be something that should be part of an ODF specification > update, right? *Where* does ODF Spec says query formatting can't be saved? Queries are displayed in a table view and the same display attributes can be applied - see spec: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/OpenDocument-v1.3-os-part3-schema.html#element-db_query 12.28 -> 12.35 -> 19.80 db:style-name (In reply to Robert Großkopf from comment #9) > If you type code with comments... > If you close and reopen such an edited query the comments are lost Why? *Where* does it say characters in a window saved to a file should filter out comments? Especially for Direct SQL - Base editor should not change it. (In reply to flywire from comment #15) > View column formatting is <s>not</s> retained in Base V7.2, <s>despite the guide.</s>
I don't know much about programming except for basic things (in multiple languages though) but is there anything I can do in order to help implement this feature?