CODEWITHSUNDEEP
unicodefirebird
R.O.K.
R.O.K.

Reputation: 71

How can I upgrade Firebird for the Unicode Data?

I'm using Firebird 2.5.1 with normal Varchar... then i'd like to upgrade all data using Unicode (to access Delphi XE2, DBExpress) I searched but not good help.. So could you advice me how to upgrade my data to unicode firebird? thanks a lot... and have a nice day.

Upvotes: 0

Views: 3917

Answers (3)

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5481

You could use following approach:

  1. Scan every table in a database for string fields
  2. For every string field create additional one using unicode character set
  3. Copy data between fields
  4. Drop original field
  5. Rename temporary field

Below is an example of a code for data conversion:

EXECUTE BLOCK
AS
  DECLARE VARIABLE fn CHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE rn CHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE cl INTEGER;
BEGIN
  FOR
    SELECT 
      r.rdb$field_name, 
      r.rdb$relation_name, 
      f.rdb$character_length
    FROM 
      rdb$relation_fields r JOIN rdb$fields f
        ON f.rdb$field_name = r.rdb$field_source
    WHERE 
      f.rdb$character_set_id >= 10 
        AND f.rdb$character_length > 0
        AND f.rdb$field_sub_type IS NULL
    INTO :fn, :rn, :cl
  DO BEGIN
    EXECUTE STATEMENT 'ALTER TABLE "' || :rn ||
      '" ADD unique_temp_field_name VARCHAR(' || :cl ||
      ') CHARACTER SET UNICODE_FSS'
    WITH AUTONOMOUS TRANSACTION;

    EXECUTE STATEMENT 'UPDATE "' || :rn ||
      '" SET unique_temp_field_name = "' || :fn || '"'
    WITH AUTONOMOUS TRANSACTION;

    EXECUTE STATEMENT 'ALTER TABLE "' || :rn || '" DROP "' || :fn || '"'
    WITH AUTONOMOUS TRANSACTION;

    EXECUTE STATEMENT 'ALTER TABLE "' || :rn || 
      '" ALTER unique_temp_field_name TO ' ||
      '"' || :fn || '"'
    WITH AUTONOMOUS TRANSACTION;
  END
END

For real world application this code should be modified in order to:

  1. Transfer fields constraints and defaults
  2. Drop and then restore PK, FK, indices which use field being converted
  3. Drop and the recreate any trigger or stored procedure which depends on a field being converted

Upvotes: 0

Hugues Van Landeghem
Hugues Van Landeghem

Reputation: 6808

You can take a look at IBExpert migration or use tools like fbclone

Upvotes: 1

Michael
Michael

Reputation: 958

In Firebird you have to specify a character-set for every varchar-field. So there is no "normal" varchar. If you don't specify it explicitly in your CREATE TABLE-statement Firebird uses the default character-set of your database.
There are a few available character-sets in Firebird. Probably you meant ASCII or an ISO-Charset with "normal". Beside these chacsets Firebirds supports UNICODE_FSS or UTF8. It is possible to change the character-set of the fields in your DB.
First you have to find out which id your prefered character-set uses. To do that you can look in the table RDB$CHARACTER_SETS
After that you need to find out which domain is used by the column you want to change. If you don't specify a custom-domain in your CREATE TABLE-statement Firebird generates one for every column.
Than could you change the used character-set for this domain in the system-table.

update RDB$FIELDS T1
  set T1.RDB$CHARACTER_SET_ID = 4
  where RDB$FIELD_NAME = (select RDB$FIELD_SOURCE 
                            from RDB$RELATION_FIELDS T2 
                            where T2.RDB$RELATION_NAME = 'NEW_TABLE' 
                              and T2.RDB$FIELD_NAME = 'NEW_FIELD')

Alternativly you could look for all varchar-domains in the system-tables and change it with one single-statement.

Upvotes: 0

Related Questions