How does one use user-defined stored java functions with embedded hsql?

[vaquero]

I’m using libreoffice 7.3.4.2 on macOS 11.6.7 (Big Sur). I have created a database in Base using the embedded HSQL DB. Tables, queries, and forms are all working wonderfully.

I need to create some user-defined stored functions in java that will be called via SQL statements. Unfortunately, no matter what I’ve tried, I cannot get Base/embedded HSQL to execute the functions I have written in java. I have set the classpath within Base to include the JAR file in which my functions are located. Every time I try to call one of my functions using the “Execute SQL Statement” tool, I get the following error:

Access is denied: Myfuncs.trivial in statement [CALL “Myfuncs.trivial”(]

where “Myfuncs.trivial” is the name of the class and static function I am attempting to call.

I am able to call functions that are built in to java (e.g. java.lang.Math.sqrt), but I cannot call any functions that are in my JAR. Note that I can call my functions without any problems from other java code when my JAR is in java’s classpath, so I know that they actually work.

How does one get Base’s embedded HSQL DB to call user-defined java functions?

I’ve boiled things down to a very trivial test case:

CALL “Myfuncs.trivial”(2.0);

should call the following function which is in my JAR:

public final class Myfuncs {
public static double trivial( double d ) {
return java.lang.Math.sqrt( d );
}
}

For reference, the following works:

CALL “java.lang.Math.sqrt”(2.0);

However, trying to call Myfuncs.trivial always gives the “Access is denied” error.

Any help would be greatly appreciated.

[Ratslinger]

Hello,
this is likely more to do with the database than with LibreOffice Base. Base is just the front end to the database.
.
A big issue here is the embedded database is HSQLDB v1.8 (very old) and looking at that documentation I do not see (quick look) reference to user functions.
.
However in v2.3.4 and later versions (chapter numbers may vary in later versions) there is talk in Chapter 8. SQL-Invoked Routines and Chapter 10. Built In Functions. That versions documentation can be found here → https://usermanual.wiki/Pdf/guide.1007271718.pdf

[vaquero]

Hi Ratslinger,

Thank you for the quick reply.

Yes, the embedded version of HSQL in Base is quite old. However, in chapter 9 of the documentation for HSQL 1.8 (the version embedded in Base), it explicitly states that java-based stored procedures are supported and gives (somewhat brief) instructions about how to do it (see the section titled “Stored Procedures / Functions”). Those are the instructions that I have followed.

As I mentioned in my original post, I can successfully call various functions that are built in to Java, but can’t seem to call any functions provided by any JARs not shipped with Java (my own or third-party). I think there is a key piece missing in the brief instructions in HSQL 1.8’s documentation or something I’ve missed in LibreOffice to allow the functionality.

[Hrbrgr]

How to install Java (JRE/JDK) so that LibreOffice could use it?

https://www.java.com/de/download/manual.jsp

[Wanderer]

@Hrbrgr as hsql1.8 is obviously already running java is installed and working. See:

@vaquero

Tables, queries, and forms are all working wonderfully

.
The question here is about “stored procedures” like getting a self written java routine to work with the embedded database (hsql1.8)

[Ratslinger]

@vaquero
.
Had not sen that section buried in the manual. Have no immediate answer.
.
Possibly search the HSQL discussion board and/or ask there. HyperSQL Database Engine (HSQLDB) Discussion

[RobertG]

@vaquero: Could you upload your *.jar-file here for testing?

[Ratslinger]

@vaquero
While looking for something else, ran across this in the HSQL discussion board:

You can write a user-defined function in Java and link it in HSQLDB with CREATE FUNCTION to perform any timestamp conversion that you need.

from this post → HyperSQL Database Engine (HSQLDB) / Discussion / Help: TIMESTAMP and AT LOCAL behavior
.
Do not find CREATE FUNCTION in v1.8.x but in v 2.3.4 docs (Ch 4 under Routine Creation) is this:

An example is given below for a function defined as a Java method, then redefined as an SQL function.

CREATE FUNCTION zero_pad(x BIGINT, digits INT, maxsize INT)
RETURNS CHAR VARYING(100)
SPECIFIC zero_pad_01
NO SQL DETERMINISTIC
LANGUAGE JAVA
EXTERNAL NAME 'CLASSPATH:org.hsqldb.lib.StringUtil.toZeroPaddedString';

ALTER SPECIFIC ROUTINE zero_pad_01
LANGUAGE SQL
BEGIN ATOMIC
DECLARE str VARCHAR(128);
SET str = CAST(x AS VARCHAR(128));
SET str = SUBSTRING('0000000000000' FROM 1 FOR digits - CHAR_LENGTH(str)) + str;
return str;
END

Thought it may be relevant.

[vaquero]

@RobertG: Here is a JAR you can test with. It contains a single function which should be callable as:

CALL “Myfuncs.trivial”(2.0);

All the function does is return the square root of the number passed to the function (in this case, the square root of 2.0).

Due to the upload filtering, I had to name the file mylib.pdf. You will need to rename it to mylib.jar.

mylib.pdf (678 Bytes)

[Wanderer]

@Ratslinger

Thought it may be relevant.

I guess this is one of the crucial points.

• Adding a jar to the class-path makes this available to LibreOffice
• To be checked: Can HSQLDB also “see” all items in additional jars or is HSQLDB isolated.
• We have to tell hsqldb where to find the additional routines, therefore the EXTERNAL-reference in the code.

No clue how to do this with the embedded 1.8

[iplaw67]

Not entirely certain that it is possible on LO for MacOS if the simple classpath declaration doesn’t work.