1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Z T Minhas
  • 37
  • 6
  • To rule out the obvious: You **are** using LibreOffice 6.2 or newer. You **did** select "Firebird" when creating the new database? (HSQLDB is still the default selection) –  Aug 05 '20 at 11:52
  • What version and OS? It worked for me, LO 6.4.3.2 (x64) on Windows. Created new Base file with all default settings (Firebird Embedded was the default). Went to Tools -> SQL, marked Show output of "select" statements, and pasted your command into the window. Result is `3.0.0,` – Jim K Aug 05 '20 at 11:54

1 Answers1

0

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • I will check. Thank you. – Z T Minhas Aug 05 '20 at 16:03
  • I checked it. It works perfectly. Embedded FireBird version from the sql and your suggestion is 3.0.0 – Z T Minhas Aug 05 '20 at 18:09
  • `but RDB$GET_CONTEXT ... can't be quoted in Firebird` O'RLY??? `SELECT "RDB$GET_CONTEXT"('SYSTEM', 'ENGINE_VERSION') AS "VERSION" FROM "RDB$DATABASE"` => '2.1.7' - my blind guess is that you tried to quote not only the term (function name), but the parameters (calling expression, in C worldview) too, and the calling of a function is indeed syntax construct. But FB has lot of "not really a function", starting with `IIF` and `DECODE` :-) – Arioch 'The Aug 10 '20 at 13:48
  • @Arioch'The Try again in Firebird 3 or higher; the way RDB$GET_CONTEXT is implemented changed in Firebird 3, and now quoting it no longer works. In Firebird 2.5.9 it was a UDF, and it no longer is in Firebird 3. – Mark Rotteveel Aug 10 '20 at 13:53
  • frankly, that seems a regression to me, would i be using FB3 i'd reported it. Leaky implementation details, at best. However ,you did not say it is impossible in FB3 but in FB in general – Arioch 'The Aug 10 '20 at 13:57
  • `SELECT "IIF"(2=2, 10, 100) FROM "RDB$DATABASE"` indeed fails on FB 2... Never been bitten by that, but... looks like inconsistency, from user perspective... But guess no one was hit hard by that, so no one bothers – Arioch 'The Aug 10 '20 at 13:58
  • @Arioch'The internal functions like `IIF` are not functions in the sense of user-defined functions, they are syntactic constructs defined by keywords, so they aren't subject to the rules for quoting object names. RDB$GET_CONTEXT and RDB$SET_CONTEXT where reimplemented as internal functions instead of being a UDF. – Mark Rotteveel Aug 10 '20 at 14:00
  • @MarkRotteveel this explanation seems to me a textbook case of leaking implementaiton details and maybe even leaky abstracitons. Though i agree it probably is very minor nuisance, in practice. – Arioch 'The Aug 10 '20 at 14:02