How do I display the HSQLDB Embedded version?

[EasyTrieve]

How do I figure out which HSQLDB Embedded version is running inside my LO?

It’s not in Help | About.

Also, is “HSQDB” HyperSQL or Heidi SQL???

I’m running the latest LO version 5.2.3.3

---

Here’s what I’ve learned so far…

== I suspect 5.2.3.3 is running an older version of HSQL because Tools | SQL… can’t find this HSQL function:

 SELECT ROW_NUMBER() FROM ...

== There is a way to upgrade HSQL by splitting off the database best described here.

== I looked at this related question, but it fails to return an answer for me. Does this mean I have V 1.8? When I try the suggested solutions with Tools | SQL... here’s what I get:

 SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES"

Executes successfully. But only returns something like this:

,INFORMATION_SCHEMA,SYSTEM_ALIASES,SYSTEM TABLE,the aliases defined within this database,MEMORY,true,
,INFORMATION_SCHEMA,SYSTEM_ALLTYPEINFO,SYSTEM TABLE,a description of all non user-defined data types known to this database and the level of support for them in various capacities,MEMORY,true,
,INFORMATION_SCHEMA,SYSTEM_AUTHORIZATIONS,SYSTEM TABLE,one row for each user and one row for each role ,MEMORY,true,
etc…

SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_TABLE_CONSTRAINTS"	

Executes successfully. But similarity only returns this:

,PUBLIC,SYS_FK_106,FOREIGN KEY,PUBLIC,Products,NO,NO,
,PUBLIC,SYS_FK_161,FOREIGN KEY,PUBLIC,Products,NO,NO,
,PUBLIC,SYS_FK_164,FOREIGN KEY,PUBLIC,MediaContacts,NO,NO,
,PUBLIC,SYS_FK_180,FOREIGN KEY,PUBLIC,Products,NO,NO,
,PUBLIC,SYS_PK_47,PRIMARY KEY,PUBLIC,Products,NO,NO,
,PUBLIC,SYS_PK_49,PRIMARY KEY,PUBLIC,Categories,NO,NO,
,PUBLIC,SYS_PK_51,PRIMARY KEY,PUBLIC,MediaContacts,NO,NO,
,PUBLIC,SYS_PK_53,PRIMARY KEY,PUBLIC,Companies,NO,NO,

SELECT * FROM "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS";

Fails to execute: “Table not found”

SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_CHECK_CONSTRAINTS";

Executes successfully. But no results returned. ???

---

(Due to a bug in Ask.LibreOffice.org where code inside of a bulleted list does not display correctly, I used == rather than * for line prefixes above.)

[Ratslinger]

If you’re using an embedded DB it’s version 1.8.0. That is all that is included in LO. AOO has a newer version. If you want to see for yourself, use the following macro:

Edited 2021-01-21:

Routine modified to display DB Product Name as well as version number. Have tested with a few different DB’s.

Sub dbInfo
 Dim databaseURLOrRegisteredName As String
  ' adjust this string to your needs. It needs to be the name of a registered database,'
  ' or a complete URL'
 databaseURLOrRegisteredName = "YOUR-REGISTERED-DB-NAME-OR-URL-HERE"
  Dim databaseContext As Object
 databaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
  Dim databaseDocument As Object
 databaseDocument = databaseContext.getByName( databaseURLOrRegisteredName )
  Dim connection As Object
 connection = databaseDocument.getConnection( "", "" )
 Dim sMessage as String
 sMessage = "Product Name:     " & connection.getMetaData().getDatabaseProductName() & chr(10) _
          & "Product Version: " & connection.getMetaData().getDatabaseProductVersion()
 MsgBox sMessage
 connection.close
End Sub

[Ratslinger]

Hyper. Never heard of Heidi DB! Also, ROW_NUMBER() did work in HSQL v2.X but not v1.8.

[Ratslinger]

Just looked it up - HeidiSQL is a front end to a DB similar to SQL Workbench/J or MySQL workbench. It is NOT a DB.

[EasyTrieve]

@Ratslinger, Thanks! So that’s really amazing to me: the newest LO, 5.2.3.3 still uses the old HSQL v: 1.8.0. (BTW, for others: leave the .odb off of your filename to get your registered name above.)

[PhranceQueTara]

This Sub also runs just fine in FireBird embedded & registered, LO 7.0.2.2, Windows10_64bit

When copy&pasting Ratzlinger’s [Sub hsqlVersion] into a module of a FireBird embedded & registered DB, it runs just fine, and pops up the following Msgbox:

+===================+

LibreOffice 7.0.2.2

product version: 3.0.0

+===================+

Question:

Is the displayed product version indeed the FireBird DB version, or some other number, since this is actually HSQL code?

Also, if one would use this code in a LO embedded FireBird DB, would it continue to run over time, or would corresponding libraries over time be phased out with the depreciation of HSQLDB from LO?

[Ratslinger]

Never tried on other DB’s (that is to say other than HSQLDB split & embedded). Firebird embedded is 3.0 and the code would not change IF HSQLDB embedded is deprecated.

Edit:

Also worked with PostgreSQL. Should work with others.

[peterwt]

I use HeidiSQL. It was previously known as MySQL-Front. It is a database management tool for MySQL, Microsoft SQL Server, and PostgreSQL databases. You can connect to local or remote servers.

[EasyTrieve]

@Peterwt, thanks! Briefly, how does it compare to LO as a front end?

[peterwt]

@EasyTrieve It is only a management tool similar to PHPMyAdmin. See here http://www.heidisql.com/ its free.

[lukas.eder]

You can detect the HSQLDB version using SQL using this query:

select character_value
from information_schema.sql_implementation_info
where implementation_info_name = 'DBMS VERSION'

[Ratslinger]

Hello,

Doesn’t work for me on HSQLDB embedded:

1: invalid schema name: information_schema in statement [SELECT "character_value" FROM "information_schema"."sql_implementation_info"] /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:175

[lukas.eder]

You should remove the quotes or if you can’t remove the quotes, then use upper-case table names

[Ratslinger]

You do not understand. That is the output error message by the system. SQL used:

select character_value
from information_schema.sql_implementation_info
where implementation_info_name = 'DBMS VERSION'

Edit:

Also note. This does work in a later version of HSQLDB but not the embedded version which is what the question is about.