Metadata Text Conversion

Repairing Your Metadata Text

Firebird versions 2.0.x had two problems related to character sets and metadata extraction:

When creating or altering objects, text associated with metadata was not transliterated from the client character set to the system (UNICODE_FSS) character set of these BLOB columns. Instead, raw bytes were stored there.

The types of text affected were PSQL sources, descriptions, text associated with constraints and defaults, and so on.

Note

Even in the current version (2.1.x) the problem can still occur if CREATE or ALTER operations are performed with the connection character set as NONE or UNICODE_FSS and you are using non-UNICODE_FSS data.
In reads from text BLOBs, transliteration from the BLOB character set to the client character set was not being performed.

Repairing Your Metadata Text

If your metadata text was created with non-ASCII encoding, you need to repair your database in order to read the metadata correctly after upgrading it to v.2.1.

Important

The procedure involves multiple passes through the database, using scripts. It is strongly recommended that you disconnect and reconnect before each pass.

The database should already have been converted to ODS11.1 by way of a gbak backup and restore.

Before doing anything, make a copy of the database.

In the examples that follow, the string $fbroot$ represents the path to your Firebird installation root directory, e.g. /opt/firebird.

Create the procedures in the database

[1] isql /path/to/your/database.fdb
[2] SQL> input '$fbroot$/misc/upgrade/metadata/metadata_charset_create.sql';

Check your database

[1] isql /path/to/your/database.fdb
[2] SQL> select * from rdb$check_metadata;

The rdb$check_metadata procedure will return all objects that are touched by it.

If no exception is raised, your metadata is OK and you can go to the section “Remove the upgrade procedures”.
Otherwise, the first bad object is the last one listed before the exception.

Fixing the metadata

To fix the metadata, you need to know in what character set the objects were created. The upgrade script will work correctly only if all your metadata was created using the same character set.

[1] isql /path/to/your/database.fdb
[2] SQL> input '$fbroot$/misc/upgrade/metatdata/metadata_charset_create.sql';
[3] SQL> select * from rdb$fix_metadata('WIN1252');  -- replace WIN1252 by your charset
[4] SQL> commit;

The rdb$fix_metadata procedure will return the same data as rdb$check_metadata, but it will change the metadata texts.

Important

It should be run once!

After this, you can remove the upgrade procedures.

Remove the upgrade procedures

[1] isql /path/to/your/database.fdb
[2] SQL> input '$fbroot$/misc/upgrade/metadata/metadata_charset_drop.sql';