[Solved] What about hsqldb 2.3 and LO 4.1.2.3?

Discuss the database features

[Solved] What about hsqldb 2.3 and LO 4.1.2.3?

Postby dreamquartz » Fri Oct 11, 2013 5:29 am

Found out that there is a new hsqldb 2.3, I wanted to try it with LO 4.1.2.3.
Set it up as indicated?
Only did link to hsqldb.jar
It turns out not working quite well together.
Created db with hsqldb 1.8 and trying to open it in 2.3, but can open it once, and after saving it, it goes wrong.

Any ideas?
Attachments
Screenshot-1.jpg
Last edited by dreamquartz on Sat Oct 12, 2013 5:04 pm, edited 1 time in total.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 726
Joined: Mon May 30, 2011 4:02 am

Re: What about hsqldb 2.3 and LO 4.1.2.3?

Postby RoryOF » Fri Oct 11, 2013 7:52 am

If you are in the middle of a project, as we understand from your other post(s), do not change fundamental components. There is an old proverb advising that one should not change horses midstream, as one will fall into the water.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30439
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: What about hsqldb 2.3 and LO 4.1.2.3?

Postby dreamquartz » Sat Oct 12, 2013 2:30 am

RoryOF wrote:If you are in the middle of a project, as we understand from your other post(s), do not change fundamental components. There is an old proverb advising that one should not change horses midstream, as one will fall into the water.

I totally agree with you.
However........
When creating a new db it is the same thing. :(

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 726
Joined: Mon May 30, 2011 4:02 am

Re: What about hsqldb 2.3 and LO 4.1.2.3?

Postby DACM » Sat Oct 12, 2013 7:12 am

Image

That error is repeatable...and avoidable. It occurs when the 'global' Class Path has been setup manually to HSQLDB 2.3.x or newer (hsqldb.jar), upon re-opening an 'embedded' database file.

We have traditionally advised users to setup the class path to hsqldb.jar manually within *Office, when using HSQLDB 2.x with Base. Until recently, this action disabled support for legacy 'embedded database' files. With the advent of HSQLDB 2.3, Base mistakenly opens 'embedded database' files despite the alternative class path. This causes an automatic upgrade of the database to HSQLDB 2.3. The upgraded 'embedded database' file will often work for the first session. But subsequent sessions trigger the error you've posted above, rendering the file unusable. You can remove the global class path setup, and even downgrade the file manually, but this involves some tedious, expert-level, file-surgery.

Perhaps ironically, we've stumbled upon a solution. The user-community developed macro-enhanced Base templates just a few months before the debut of HSQLDB 2.3. These templates rely on a discrete session class path, as opposed to the traditional global class path setup process. A 'session' class path is setup automatically by the macro and stored with the Base .odb front-end file, so there's no need for a 'global' class path. In fact, a 'global' class path becomes a liability here as well, because it will override a 'session' class path, potentially causing compatibility issues or inadvertent upgrade. Thus, we now advise 'split HSQL database' users to remove their 'global' class path to hsqldb.jar, thereby restoring Base to it's original state. The latest template simply adds a dedicated copy of hsqldb.jar (currently HSQLDB 2.3) to each split-database folder (a small, 2MB, internally-licensed file). Since there's no global class path setup, we can now run legacy 'embedded databases' along-side 'split HSQL databases' for the first time. And we can run these upgraded databases (Base + HSQLDB 2.3) on any Windows, Mac or Linux computer running *Office with macros enabled.

So my advice is to adopt a macro-enhanced Base+HSQL template for all your single-user, relational-database needs with Base. That way you can avoid all the issues associated with manual class path setup in *Office. Since a split-database can be run in server-mode as well -- such as to provide concurrent read/write access among multiple users -- we can talk you through disabling the macro and modifying the 'data source URL' in the template for server-mode access, on an as needed basis.
Last edited by DACM on Mon Jan 26, 2015 12:00 am, edited 4 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: SOLVED What about hsqldb 2.3 and LO 4.1.2.3?

Postby dreamquartz » Sat Oct 12, 2013 5:05 pm

Thanks DACM for the explanation.
Much appreciated.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 726
Joined: Mon May 30, 2011 4:02 am

Re: SOLVED What about hsqldb 2.3 and LO 4.1.2.3?

Postby Sliderule » Sat Oct 12, 2013 7:26 pm

dreamquartz:

Just as an FYI ( For Your Information ) . . . IF you are using HSQL 2.3.0 or HSQL 2.3.1 as your database back-end, there is a function . . . DATEADD that I would strongly suggest you use, rather than the 'convoluted' technique I offered for you at the link below ( it was required when using the 'embedded' HSQL version 1.8.0 ) :

http://forum.openoffice.org/en/forum/viewtopic.php?f=61&t=64731

The SQL ( Structured Query Language ) could be written as:

Code: Select all   Expand viewCollapse view
SELECT
   "SALESMAN"."Salesman",
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month",
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price"

FROM "PO ITEMS",
     "PO INVOICE",
     "SALESMAN"

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO"
  AND YEAR("PO INVOICE"."Date") = YEAR(DATEADD('month', -1, CURRENT_DATE))
  AND MONTH("PO INVOICE"."Date") = MONTH(DATEADD('month', -1, CURRENT_DATE))

GROUP BY "SALESMAN"."Salesman",
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Explanation:
  1. By using DATEADD . . . with the parameter 'month' and -1 , it will return a value for the prior month of CURRENT_DATE ( the computer system date ) .

  2. According to HSQL documentation:
    HSQL 2.3.1 Documentation: http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_datetime_arithmetic wrote:
    DATEADD

    DATEADD ( <field>, <numeric value expr>, <datetime value expr> )

    <field> ::= 'yy' | 'year' | 'mm' | 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' | 'millisecond'

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest