[Solved] What about hsqldb 2.3 and LO 4.1.2.3?

[dreamquartz]

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?

[RoryOF]

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.

[dreamquartz]

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

[DACM]

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.

[dreamquartz]

Thanks DACM for the explanation.
Much appreciated.

Dream

[Sliderule]

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/vi ... 61&t=64731

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

Code: Select all

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:

   
   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