Firebird specials in table structure for varchar and UTF-8

Nov 15
2016

In my Visual Objects applications based on DBFs I have a routine that checks the structure of the DBF tables, compares them with the structure that is required and adjusts the structure.

I’m now working on a similar functionality for my Firebird based .NET applications. To retrieve the list of the currently existing user tables, I’m using such a statement:

select rdb$relation_name as tablename from rdb$relations
where rdb$view_blr is null and 
(rdb$system_flag is null or rdb$system_flag = 0)

and to retrieve the actual structure of such a table I use the following statement:

select rf.rdb$field_position as fieldpos, 
TRIM(rf.rdb$field_name) as fieldname, 
f.rdb$field_type as fieldtype, 
f.rdb$field_length as fieldlen,
f.rdb$field_scale as fielddec, 
(f.rdb$null_flag = 1) OR (rf.rdb$null_flag = 1) as canbenull 
from rdb$relation_fields rf 
JOIN rdb$fields f ON f.rdb$field_name = rf.rdb$field_source 
WHERE upper( rf.rdb$relation_name ) = upper( 'mytable' ) 
order by rf.rdb$field_position

To have no limitations with foreign languages, I have opted to use the UTF-8 character set for all varchar columns.

And now I was very surprised: the select statement returned 4 times the actual field width: for a field defined as varchar(20) the query returned 80 as field length.

My conclusion is very simple: since every UTF-8 character can take up to 4 bytes, Firebird reserves 4 bytes for every character.

I have now found the following StackOwerflow article about this:
http://stackoverflow.com/questions/822837/firebird-utf8-varchar-size

Firebird 3.0, Embedded server and the .NET provider

Nov 15
2016

For local data, the embedded server of Firebird is very welcome because it does not needs any configuration.

With Firebird 2.5, a special DLL (fbembed.dll) was needed, but Firebird 3.0 has added this functionality into the standard fbclient.dll.
So, if using the embedded server of Firebird 3.0 instead of the one of Firebird 2.5, you need to specify this in the connection string. It is the best to use the ClientLibrary property of the FBConnectionStringBuilder (namespace FirebirdSql.Data.FirebirdClient).

The following files need to be put in your application directory (please make attention to use the correct bitness – 64 bit files for a 64 bit application and 32 bit files for a 32 bit application):
fbclient.dll
ib_util.dll
plugins\engine12.dll

Normally, you should NOT need to put a firebird.conf file in you application directory.

References:
http://stackoverflow.com/questions/37444411/unable-to-connect-firebird-3-0-embedded-version
http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-engine.html

Firebird syntax differences to other SQL databases

Sep 30
2015

Insert statements

Until now, I have used insert statements in the form

insert into mytable field1 = 1, field2 = ‘hello world’

Firebird does not like this, it needs the form

insert into mytable ( field1, field2 ) values ( 1, ‘hello world’ )

Masking apostrophes
Also Firebird does not likes masking apostrophes with the backslash

‘Hello, I\’m Wolfgang’

but needs two apostrophes

‘Hello, I”m Wolfgang’

Autoincrement fields
Firebird unfortunately does not have autoincrement fields as MS SQL or MySQL, but needs a trigger and a generator (other databases call it sequence), as it needs also the Oracle database.

Database managment tools
Until now, used DBTools Manager Professional or Navicat Premium Essentials to manage my SQL databases. Unfortunately, DBTools Manager does not work very well with Firebird, and Navicat has no Firebird support. The FlameRobin is very “basic” and has also some problems working with it, so after searching for a low-cost and good-working alternative I have decided for IBExpert Desktop.

Firebird installation on Debian server

Sep 11
2015

Installing Firebird on Debian (Jessie in my case) gave a problem: after installing and starting the server I was not able to connect to the database server:

Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

using the sysdba password I entered in the installation dialog.

Unfortunately it seems the installation procedure has a bug, and does not accept the entered password, but attributes a random password. This random password can you find in the file

/etc/firebird/2.5/SYSDBA.password

The connect with this random password succeedes and you can change your password afterwards – but you must change it with both the Firebird administration tools and inside this file.

The password can be changed with the gsec tool:

gsec -user sysdba -password <password_from_SYSDBA.password>
modify sysdba -pw <your_new_password>
quit

And another speciality for Debian:

The isql tool is called isql-fb