[SOLVED] Problem running embedded firebird sql query in LibreOffice

Problem running embedded firebird sql query in LibreOffice

I am trying to run the following Firebird SQL query in LibreOffice, which has embedded Firebird:

SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION')  AS "VERSION" 
FROM "RDB$DATABASE";

I get the message Syntax error in SQL statement. Can anyone tell me what I am doing wrong? This works in FlameRobin, but not in LibreOffice.

Solution

The error "Syntax error in SQL statement" is a HSQLDB error. So, first, make sure your LibreOffice Base project is actually created as a Firebird Embedded project.

I was able to reproduce the error in LibreOffice Base 6.4.4.2 with a Firebird Embedded project. It looks like LibreOffice first tries to parse the query using HSQLDB (probably to be able to transform HSQLDB syntax to Firebird syntax), and only then forwards it to Firebird.

The cause of the error is the $ in RDB$GET_CONTEXT which is not a valid character in an unquoted object name in the HSQLDB SQL syntax, while it is valid in the Firebird SQL syntax. Normally, double quoting the specific object name would solve this, but RDB$GET_CONTEXT is not actually a function, but a syntax construct, so it can't be quoted in Firebird.

To be able to execute this query, you need to enable the 'Run SQL command directly' option in the SQL View, either under Edit > 'Run SQL command directly', or using the 'Run SQL command directly' button in the toolbar (database icon with >_).