How to get the servername\hostname in Firebird 2.5

Question

I use Firebird 2.5, and I want to retrieve the following values

Username:
I used SELECT rdb$get_context('SYSTEM', 'CURRENT_USER') FROM ...

Database name:
I used SELECT rdb$get_context('SYSTEM', 'DB_NAME') FROM ...

But for server name, I did not find any client API, do you know how can I retrieve the server name with a SELECT statement.

Answer 1

There is nothing built-in in Firebird to obtain the server host name (or host names, as a server can have multiple host names) through SQL.

The closest you can get is by requesting the isc_info_firebird_version information item through the isc_database_info API function. This returns version information that - if connecting through TCP/IP - includes a host name of the server.

But as your primary reason for this is to discern between environments in SQL, it might be better to look for a different solution. Some ideas:

Use an external table

You can create an external table to contain the environment information you need

In this example I just put in a short, fixed width name for the environment types, but you could include other information, just be aware the external table format is a binary format. When using CHAR it will look like a fixed width format, where values shorter than declared need to be padded with spaces.

You can follow these steps:

1. Configure ExternalFileAccess in firebird.conf (for this example, you'd need to set ExternalFileAccess = Restrict D:\data\DB\exttables).

2. You can then create a table as

   create table environment 
     external file 'D:\data\DB\exttables\environment.dat' (
     environment_type CHAR(3) CHARACTER SET ASCII NOT NULL 
   )
   

3. Next, create the file D:\data\DB\exttables\environment.dat and populate it with exactly three characters (eg TST for test, PRO for production, etc). You can also insert the value instead, the external table file will be created automatically. Inserting might be simpler if you want more columns, or data with varying length, etc. Just keep in mind it is binary, but using CHAR for all columns will make it look like text.

   Do this for each environment, and make sure the file is read-only to avoid accidental inserts.

After this is done, you can obtain the environment information using

select environment_type 
from environment

You can share the same file for multiple databases on the same host, and external files are - by default - not included in a gbak backup (they are only included if you apply the -convert backup option), so this would allow moving database between environments without dragging this information along.

Use an UDF or UDR

You can write an UDF (User-Defined Function) or UDR (User Defined Routine) in a suitable programming language to return the information you want and define this function in your database. Firebird can then call this function from SQL.

UDFs are considered deprecated, and you should use UDRs - introduced in Firebird 3 - instead if possible.

I have never written an UDF or UDR myself, so I can't describe it in detail.