Ask Your Question
1

How do I display the HSQLDB Embedded version?

asked 2017-01-05 19:55:54 +0200

EasyTrieve gravatar image

updated 2020-09-20 14:21:14 +0200

Alex Kemp gravatar image

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

image description

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.)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2017-01-05 21:14:30 +0200

Ratslinger gravatar image

updated 2021-01-21 22:44:03 +0200

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
edit flag offensive delete link more

Comments

1

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

Ratslinger gravatar imageRatslinger ( 2017-01-05 21:18:34 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-01-05 23:16:24 +0200 )edit

@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.)

EasyTrieve gravatar imageEasyTrieve ( 2017-01-06 04:06:35 +0200 )edit

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?

Phrance QueTara gravatar imagePhrance QueTara ( 2021-01-09 00:40:26 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2021-01-09 01:33:09 +0200 )edit
0

answered 2017-01-06 00:08:03 +0200

peterwt gravatar image

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.

edit flag offensive delete link more

Comments

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

EasyTrieve gravatar imageEasyTrieve ( 2017-01-06 04:08:33 +0200 )edit

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

peterwt gravatar imagepeterwt ( 2017-01-06 18:09:15 +0200 )edit
0

answered 2021-05-12 11:57:51 +0200

lukas.eder gravatar image

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'
edit flag offensive delete link more

Comments

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
Ratslinger gravatar imageRatslinger ( 2021-05-12 16:15:08 +0200 )edit

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

lukas.eder gravatar imagelukas.eder ( 2021-05-12 16:28:35 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2021-05-12 16:32:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-01-05 19:55:54 +0200

Seen: 2,539 times

Last updated: May 12