Bug 147383

Summary:	Split DataBase HSQLBD 2.6.1 in LO 7 series Query editing issues
Product:	LibreOffice	Reporter:	Dreamquartz <basd>
Component:	Base	Assignee:	Not Assigned <libreoffice-bugs>
Status:	UNCONFIRMED ---
Severity:	normal	CC:	robert
Priority:	medium
Version:	7.0 all versions
Hardware:	All
OS:	Linux (All)
Whiteboard:
Crash report or crash signature:		Regression By:

Description Dreamquartz 2022-02-11 18:14:27 UTC

On page 46 of BG64-BaseGuide.pdf and clearly repeated on page 207 of BG72-BaseGuide.pdf, there is a notation method of field names between double quotes, like “sportage” and “Loan”.
The User can assume that this is a valid method to be used for writing queries over time and can still be used ‘today’ (as of the date of describing this issue).

Now it is obvious that the manuals are specific for the internal version of HSQLDB (1.8), but up to LO 6.4 this way of writing code was also available to the User in SQL View, even when using the split database setup for HSQLDB 2.6.1.

After LO 6.4 something changed as such that 2 things are not available anymore to the User in SQL View:

Using the double quote notation
Using a ‘Tab’ as an indent

Comment 1 Robert Großkopf 2022-02-12 10:01:36 UTC

Bug for using "tab" is fixed: Bug 147325.

I you are using an external database it will be part of the driver to accept masking by double-quotes. When having a look at http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sgc_standards double-quotes should work together with HSQLDB 2.0

Please write down how you connect to the external database.

Comment 2 Dreamquartz 2022-02-13 02:07:33 UTC

@Robert,

LO 6.4.7.2 does accept the double quotes, however.
Using hsqldb.jar as part of the Bundle-Version: 2.6.1.

I only use the 'jar' and it is placed in the same folder as where the split database is stored.
Access via FreeHSQLDB-0.3.7.oxt from Villeroy.

Comment 3 Robert Großkopf 2022-02-13 07:33:03 UTC

So I tested external HSQLDB with hsqldb.jar from HSQLDB 2.6.1 

There are queries in the database I had created earlier. Queries will work. I could create new queries - all fields will be shown double-quoted when switching to direct SQL mode.

Could be the special behavior you have detected is a behavior
- of your special version of LO (Ubuntu or did you install the packages directly from LibreOffice?)
- of the extension you are using.
Don't know where a extension should help here. Is only a JavaDriverClassPath, which must be set special for the datbase you are using as external databes. Whitout this every internal HSQLDB will be opened by the external driver.

Tested with 
Version: 7.3.0.3 / LibreOffice Community
Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3
CPU threads: 6; OS: Linux 5.3; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

on OpenSUSE 15.3

Comment 4 Dreamquartz 2022-02-13 08:10:28 UTC

(In reply to Robert Großkopf from comment #3)
> So I tested external HSQLDB with hsqldb.jar from HSQLDB 2.6.1 
> 
> There are queries in the database I had created earlier. Queries will work.
> I could create new queries - all fields will be shown double-quoted when
> switching to direct SQL mode.
> 
> Could be the special behavior you have detected is a behavior
> - of your special version of LO (Ubuntu or did you install the packages
> directly from LibreOffice?)
> - of the extension you are using.
> Don't know where a extension should help here. Is only a
> JavaDriverClassPath, which must be set special for the datbase you are using
> as external databes. Whitout this every internal HSQLDB will be opened by
> the external driver.
> 
> Tested with 
> Version: 7.3.0.3 / LibreOffice Community
> Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3
> CPU threads: 6; OS: Linux 5.3; UI render: default; VCL: kf5 (cairo+xcb)
> Locale: de-DE (de_DE.UTF-8); UI: de-DE
> Calc: threaded
> 
> on OpenSUSE 15.3

Using LO directly from libreoffice.org on Ubuntu 20.04 LTS directly from Ubuntu.com
The extension by Villeroy helps me to easily copy a DataBase to a new folder for development purposes, and link to the original jar, to make sure that there are no problemss.
The extension also provides a simple solution to extract all forms, to make them available to users.