International Language Support (INTL)

Firebird allows character sets and collations to be declared in any character field or variable declaration. The default character set can also be specified at database create time, to cause every CHAR/VARCHAR declaration that does not specifically include a CHARACTER SET clause to use this default.

At attachment time you normally specify the character set that the client is to use to read strings. If no "client" (or "connection") character set is specified, character set NONE is assumed.

Two special character sets, NONE and OCTETS, can be used in declarations. However, OCTETS cannot be used as a connection character set. The two sets are similar, except that the space character of NONE is ASCII 0x20, whereas the space character OCTETS is 0x00. NONE and OCTETS are “special” in the sense that they follow different rules from those applicable to other character sets regarding conversions.

Enhancements that the new system brings include:

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set dos850);
  SQL> insert into t values ('a');
  SQL> insert into t values ('e');
  SQL> insert into t values ('á');
  SQL> insert into t values ('é');
  SQL>
  SQL> select c, upper(c) from t;

  C      UPPER
  ====== ======
       a      A
       e      E
       á      á
       é      é
        

  C      UPPER
  ====== ======
       a      A
       e      E
       á      Á
       é      É
        

  select blob_column from table
    where blob_column collate unicode = 'foo';
          

Character sets and collations are installed using a manifest file.

The manifest file should be put in the $rootdir/intl with a .conf extension. It is used to locate character sets and collations in the libraries. If a character set/collation is declared more than once, it is not loaded and the error is reported in the log.

The file /intl/fbintl.conf is an example of a manifest file. The following snippet is an excerpt from /intl/fbintl.conf:

   <intl_module fbintl>
       filename    $(this)/fbintl
   </intl_module>

   <charset ISO8859_1>
       intl_module    fbintl
       collation    ISO8859_1
       collation    DA_DA
       collation    DE_DE
       collation    EN_UK
       collation    EN_US
       collation    ES_ES
       collation    PT_BR
       collation    PT_PT
   </charset>

   <charset WIN1250>
       intl_module    fbintl
       collation    WIN1250
       collation    PXW_CSY
       collation    PXW_HUN
       collation    PXW_HUNDC
   </charset>
      

Two character sets introduced in Firebird 2 will be of particular interest if you have struggled with the shortcomings of UNICODE_FSS in past versions.

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set utf8);
  SQL> insert into t values ('a');
  SQL> insert into t values ('A');
  SQL> insert into t values ('á');
  SQL> insert into t values ('b');
  SQL> insert into t values ('B');
  SQL> select * from t order by c collate ucs_basic;

  C
  ======
  A
  B
  a
  b
  á

  SQL> select * from t order by c collate unicode;

  C
  ======
  a
  A
  á
  b
  B
        

The 2.1 release sees further capabilities implemented for

All non-wide and ASCII-based character sets present in ICU can be used by Firebird 2.1. To reduce the size of the distribution kit, we customize ICU to include only essential character sets and any for which there was a specific feature request.

If the character set you need is not included, you can replace the ICU libraries with another complete module, found at our site or already installed in your operating system.

<charset         NAME>
    intl_module     fbintl
    collation       NAME [REAL-NAME]
</charset>
          

<charset         GB>
    intl_module     fbintl
    collation       GB GB18030
</charset>
            

execute procedure sp_register_character_set ('GB', 4);
            

CREATE COLLATION <name>
    FOR <charset>
    [ FROM <base> | FROM EXTERNAL ('<name>') ]
    [ NO PAD | PAD SPACE ]
    [ CASE SENSITIVE | CASE INSENSITIVE ]
    [ ACCENT SENSITIVE | ACCENT INSENSITIVE ]
    [ '<specific-attributes>' ]
            

/* 1 */
CREATE COLLATION UNICODE_ENUS_CI
    FOR UTF8
    FROM UNICODE
    CASE INSENSITIVE
    'LOCALE=en_US';
/* 2 */
CREATE COLLATION NEW_COLLATION
    FOR WIN1252
    PAD SPACE;

/* NEW_COLLATION should be declared in .conf file
   in the $root/intl directory */
          

The UNICODE collations (case sensitive and case insensitive) can be applied to any character set that is present in fbintl. They are already registered in fbintl.conf, but you need to register them in the databases, with the desired associations and attributes.

Naming Conventions

The naming convention you should use is charset_collation. For example,

create collation win1252_unicode
  for win1252;

create collation win1252_unicode_ci
  for win1252
   from win1252_unicode
   case insensitive;