openoffice.org - Looking for OpenOffice base SQL reference

Question

(I posted this elsewhere, but this may be a better forum.)

I recently bought a new laptop and am trying to avoid MS Office. I've loaded OpenOffice 4.1.8, but am having issues writing query statements in OO base. Supposedly, OO base uses the HyperSql database engine (don't know what version, perhaps 2.5.1?), but am having difficulty with writing some simple SELECT statements. (I have extensive experience with both MS Access, Ingres, and a little PostgreSQL).

Took me quite awhile to determine that

SELECT lastname + ', ' + firstname from Accounts

wasn't 'good enough'. I had to specify:

SELECT lastname + (', ') + firstname from Accounts

only because I finally muddled through the Backus-Naur form at http://www.hsqldb.org/doc/2.0/guide (2.5.1) site to discover it.

That site also led me astray, saying (in the BNF) that || was the string concatenation operator, but the OO base said 'invalid use of vertical bars', implying that hsqldb 2.5.1 reference wasn't quite the reference I'm looking for. (perhaps I need an older version reference?)

In addition, a number of functions don't seem to be available, CONCAT and IFNULL/ISNULL is amongst them. This seems to imply a missing library or two.

So, really, I have two questions: Can anyone point me to an applicable BNF for the OO base (4.1.8) query language? And, perhaps tell me if there's some way I can determine whether I'm missing a library or two of functions that it uses?

Answer 1

Apache OpenOffice has always embedded HSQLDB 1.8, and that is probably still true in AOO 4.1.8. To verify your version, check out https://wiki.openoffice.org/wiki/HSQLDB:Tips_and_Tricks#How_to_know_the_version_of_the_embedded_HSQLDB.3F. Documentation is at http://www.hsqldb.org/doc/1.8/guide/guide.html. The engine and SQL syntax is much more limited than HSQLDB 2 and higher.

For something newer, consider LibreOffice Base with Firebird embedded. Or even better, use Base with PostgreSQL in a split database setup (works with both LO and AOO). Split database setups are powerful although they are no longer contained in a single file, which can be less convenient.