Determine Firebird Version with SQL (Version < 2.1)

Question

I have an application that handles databases on various locations, and I want to build in a check that these databases are opened using Firebird 2.5 or later. We have recently migrated from Firebird 2.0 to 2.5 and we have a lot of databases out there that respond to

select rdb$get_context('SYSTEM','ENGINE_VERSION') as "version" from RDB$DATABASE

with 'SQL error code = -804 Function unknown RDB$GET_CONTEXT. I guess because they were built with Firebird 2.0 - a rebuild to 2.5 fixed it.

Is there a way of detecting which firebird service is in use that can be applied to databases older than 2.1?

According to the documentation it seems that the answer is "no". You may check also the ODS if may help. — fantaghirocco
What do you mean with "rebuild"? A backup and restore upgrades the database to the latest ODS version, which makes the newer functionality available. As to determining the Firebird version, if your Delphi client exposes the service API, or the info-request API, then you can query for the Firebird version and the database ODS version (eg the native Firebird 2.5 ODS is 11.2; if you have an earlier ODS then not all functionality is available). — Mark Rotteveel
I don't quite follow: rdb$get_context() was introduced in 2.0. Was that actually an FB 1.5 database? — pilcrow
@Mark Rotteveel yes, I meant a backup+restore using the firebird gfix tool, upgrading the database to the current fb version — Bjarke Moholt

pilcrow pilcrow · Accepted Answer · 2015-07-24T16:08:34

Can your client or connecting API tell you? E.g., isql's SHOW VERSION or DBD::Firebird's ib_database_info?

If not, just figure it out the hard way, probing for functionality from most recent to most ancient. Note that ENGINE_VERSION support was introduced in 2.1, so there's not that much to check:

SELECT rdb$get_context('SYSTEM','ENGINE_VERSION')... -- ENGINE_VERSION >= 2.1
SELECT * FROM (SELECT ...) fb20;                     -- derived table -> FB 2.0
SELECT "fb15" FROM rdb$database WITH LOCK;           -- LOCK -> FB 1.5
SELECT FIRST ...                                     -- FIRST -> FB 1.0
else abort()                                         -- prehistoric?

Determine Firebird Version with SQL (Version < 2.1)

1 Answers