Why "Access is denied: ROW_NUMBER( ..."?

SonomaCharles · January 30, 2026, 7:42pm

I am by no means a SQL expert. I have the following Query:

SELECT ROW_NUMBER ( ) OVER ( ORDER BY “Sequence” ) AS “Row_Number” FROM “Clients”

The table Clients exists and the column Sequence exists in the table. Why am I getting

or more importantly how do I fix it?

HSQLDB Embedded.

Version: 25.8.4.2 (X86_64)
Build ID: 290daaa01b999472f0c7a3890eb6a550fd74c6df
CPU threads: 14; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

Charles

CRDF · January 30, 2026, 9:11pm

Does the embedded HSQLDB engine (version 1.8 I think) support Window Functions?

SonomaCharles · January 30, 2026, 9:21pm

I don’t know the answer and I don’t even know what you mean.

Can the SQL processor call out to a Windows function? I have no idea. ROW_NUMBER() is a native SQL function, right?

SonomaCharles · January 30, 2026, 9:25pm

I have no idea how to get HSQLDB information out of LO. I have the hslqdb.org Web site and SQL documentation, but I have no idea how to get any “what DB version is installed?” information out of LO.

SonomaCharles · January 30, 2026, 9:41pm

I clearly do not know what I am doing. I run SELECT DATABASE_VERSION() and get 1: Access is denied: DATABASE_VERSION in statement [SELECT DATABASE_VERSION (]

CRDF · January 30, 2026, 9:44pm

Yes: a Window Function.

Google something like
libreoffice base hsqldb embedded

SonomaCharles · January 30, 2026, 9:49pm

Ah, a Window function not a Windows function.

It is listed under System Functions in the HSQLDB documentation.

Charles

SonomaCharles · January 30, 2026, 9:53pm

SonomaCharles · January 30, 2026, 9:56pm

Geez. So I am using V#1.8 but working for doc from V#2.7. No wonder I am confused!

I see a caution against upgrading HSQLDB.

Is there a doc somewhere that would cover how to export an HSQLDB database to some other DB engine supported by LO, and if so, what DB to use?

Charles

CRDF · January 30, 2026, 10:18pm

@Villeroy can explain you how to set up an updated HSQLDB database.
HSQLDB is an excellent DB engine. But version shipped within LO is an obsolete one. They never upgraded it.
Besides: you should never use embedded for real “production” work.

I know how to create Firebird or MariaDB from scratch. But not how could you export this existing one.

CRDF · January 30, 2026, 10:35pm

From @Ratslinger code there:

Sub Versao(Evt As Object)
	Dim F As Object, Con As Object
	On Error GoTo Erro
	F = Evt.Source.Model.Parent ' Form from where routine is called
	Con = F.ActiveConnection
	print Con.getMetaData().getDatabaseProductVersion()
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Sub

VersaoHSQLDB

SonomaCharles · January 30, 2026, 10:50pm

Got it. The answer is always 1.8.

SonomaCharles · January 30, 2026, 10:58pm

Here are my problems:

I am extremely technical and have tons of programming experience but exactly zero with RDBMS, desktop databases or SQL.
I did not set this database up. I inherited it. See #3.
I have no “corporate resources.” I have no one I can go to and say “help.” The database belongs to a small nonprofit. I am a volunteer. I am by far the most technical person here. No one else here has probably every heard “RDBMS” or “SQL”.

So I am kind of at a disadvantage and “stuck.”

I just downloaded Firebird. It looks like a decent product. I will try to give it a go. I will see if I can’t by some chance export this one database to external Firebird.

The database in question is one single database. There is only one “real” table – not counting little read-only tables that drive some pull-down or map 1, 2, 3 to Monday, Tuesday, Wednesday. The table has 261 rows and 25 columns. It is critical to what we do.

There is one significant form and one significant report. The report is fairly complex.

Charles

CRDF · January 30, 2026, 11:57pm

Firebird is provided with Base.
Base >
Connect to an (non) existing Database >
Firebird External >
Create new > […]

SonomaCharles · January 31, 2026, 12:39am

@CRDF I am grateful for you help but I am too stupid to grasp what you are trying to say.

Are you saying 25 columns is too many? What are you suggesting I should do? I didn’t design this database, I inherited it.

Are you saying I should not have downloaded Firebird. Where would it be? How would I activate it?

?

CRDF · January 31, 2026, 12:49am

Just explained above…

But as you are considering to set up a new DB, I suggest to check the normalization. You may not have experience with Relational DBs but

, so it’s easy to grasp the “normal forms”.

RobertG · January 31, 2026, 7:42am

If you want to use ROW_NUMBER(): It isn’t available for internal HSQLDB. You could use it with internal Firebird database.
Internal Firebird is experimental. So it needs experimental features switched on for creating such a database…
.
For internal HSQLDB something like this would help:

SELECT "a".*,
 ( SELECT COUNT( "Sequence" ) FROM "Clients" WHERE "Sequence" <= "a"."Sequence" )
 AS "Row_Number"
FROM "Clients" AS "a"

For COUNT you will nee a field, which should never be NULL. So best would be the primary key of your table.

Villeroy · January 31, 2026, 9:10am

https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=86071

Install a macro and the zip archive of HSQL 2.4.1.
Put the lib/hsqldb.jar in subdirectory “driver”.
Run the installed macro.

2.4.1 was the last version able to upgrade HSQL 1.8.
You may replace hsqldb.jar with a later one and run the macro again in order to upgrade from 2.4.1.

Villeroy · January 31, 2026, 11:12am

The most relevant part for HSQL 1.8: Chapter 9. SQL Syntax
Text tables in chapter 6: Chapter 6. Text Tables

The documentation of any later version is contained in the downloaded zip archive.

SonomaCharles · January 31, 2026, 3:42pm

@Villeroy This is SUPER helpful. Thank you. Let me try to parse this in detail over the next couple of days.