[Solved] BASE, SELECT syntax with the REGEXP_COUNT()

[gam01hr]

Hello, I am new to the Base and experimenting with REGEXP functions. Some of them work as expected but some returns warning only.
Please could you have a look at [example 1] below? Thank you.
I use:
Libre Office version 7.4.3.2 (x64) on Windows
#HSQL Database Engine 2.3.2, this shall support REGEXP according this [link 2]
I created 'Split' HSQL database 2.X according this [link 1]
There is one table only "tblPlant". Command is tested directly from menu, Base -> Tools -> Execute SQL Command

Problem:

Code: Select all

[example 1]
select REGEXP_COUNT("Name",'^.*old') from "tblPlant"
Returns:
1: user lacks privilege or object not found: REGEXP_COUNT

on the other hand REGEXP_MATCHES works as expected

Code: Select all

[example 2]
select REGEXP_MATCHES("Name",'^.*old') from "tblPlant"
Returns:
TRUE,
FALSE,
TRUE,
FALSE,
FALSE,
FALSE,
FALSE,

Code: Select all

[example 3]
select "Name" from "tblPlant"
Harold,
Gwen,
Harold,
Benny,
Diane,
Gwen,
Gwen,

Similar problem recreated with another built-in function:

Code: Select all

[example 4]
select JSON_ARRAYAGG("Name") from "tblPlant"
Returns:
5: user lacks privilege or object not found: JSON_ARRAYAGG

I feel, I miss something in syntax or functions might not be implemented in version 2.3.2. I know about the LIKE sql syntax but I use REGEXP to learn its basics usage for future more complex records analysis.

[link 1] https://forum.openoffice.org/en/forum/v ... hp?t=61183
[link 2] https://forum.openoffice.org/en/forum/v ... p?p=294598

[Sliderule]

Since, you are using the HSQL version . . . 2.3.2 . . . that was released 2014-02-12 . . . and, yes it is very old. I suggest, you look at the documentation for that release to see the available functions.

HSQL Version 2.3.2 includes the following REGEXP functions:

1. REGEXP_MATCHES
2. REGEXP_REPLACE
3. REGEXP_SUBSTRING
4. REGEXP_SUBSTRING_ARRAY

HSQL Version 2.7.1 as of the date I am writing this ( 2022-12-20 ) includes the following REGEXP functions:

1. REGEXP_COUNT
2. REGEXP_INSTR
3. REGEXP_LIKE
4. REGEXP_MATCHES
5. REGEXP_REPLACE
6. REGEXP_SUBSTR
7. REGEXP_SUBSTRING
8. REGEXP_SUBSTRING_ARRAY

JSON array functions has been available since HSQL Version 2.7.0, as well as various ARRAY processing functions.

HSQL Version 2.7.1 as of the date I am writing this ( 2022-12-20 ) includes the following JSON functions:

• JSON_ARRAY
• JSON_ARRAYAGG
• JSON_OBJECT
• JSON_OBJECTAGG

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[gam01hr]

Thank you very much for clarification. Thanks to this forum I was able to do upgrade to HSQLDB 2.7.1
Procedure for my PC (windows7/64 bit)
(1) HSQLDB 2.7.1 needed more recent java version than I had, therefore I uninstalled older java and installed the "jdk-11.0.17_windows-x64_bin.exe" in my case
Tested it from the command line:
/>java -version
java version "11.0.17" 2022-10-18 LTS
Java(TM) SE Runtime Environment 18.9 (build 11.0.17+10-LTS-269)
Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.17+10-LTS-269, mixed mode)

(2) Go to [link 1]
https://hsqldb.org/
downloaded the Version 2.7.1 of HyperSQL (October 2022)
and I installed it according to their How To

(3) Go to [link 2]
https://forum.openoffice.org/en/forum/v ... p?p=270844
downloaded "Split_HSQLDB_2.3.2_Wizard_v3d.odb"
(4) Renamed "Split_HSQLDB_2.3.2_Wizard_v3d.odb" to "Split_HSQLDB_2.3.2_Wizard_v3d.zip"
- decompressed
- copied two files from
C:\hsqldb-2.7.1\hsqldb\lib\hsqldb.jar
C:\hsqldb-2.7.1\hsqldb\lib\sqltool.jar
to Split_HSQLDB_2.3.2_Wizard_v3d/driver to replace the existing jar files.

- compressed back to .zip and rename back to "Split_HSQLDB_2.3.2_Wizard_v3d.odb"
(5) Now I can open it in Base as "split database" and able to use the latest build-in functions.