How to convert character set from ISO8859_1 to UTF8 in Firebird?

Question

I have a database in Firebird 2.5 filled with data. I need to change the character set from UTF-8 to ISO8859_1, I tried:

alter database default character set ISO8859_1 collation ES_ES

But it doesn't work. How can I convert the character set?

Answer 1

Changing the default character set only affects columns created (without an explicit character set) after the change. Existing columns are unaffected as the character set is a property of each individual column. This means that you have to alter all (relevant) columns.

There are several problems

1. Firebird doesn't change the data at alter time, but instead creates a new format version, and converting on the fly from the old format to the new format at select time.
   This has a performance impact, but can also result in string conversion errors at runtime (eg because an UTF-8 character doesn't exist in ISO-8859-1).
2. Changing the character set only works correctly if the column currently has a real character set (ie other than NONE or OCTETS). Conversion from NONE or OCTETS to another character set may result in string conversion errors, or garbage because the content may not match your expectations, or maybe invalid bytes in the target character set.
3. Some versions - iirc - don't actually change the character set at all

To address these problems you can do:

1. Export the DDL of your database, change character set definitions, create a new database and use a data pump tool (like FBCopy) to copy the data from the old to the new
2. or, for each column you want to change:
   • Create a new column
   • UPDATE table SET newcolumn = oldcolumn (or if oldcolumn is NONE or OCTETS: UPDATE table SET newcolumn = cast(cast(oldcolumn as VARCHAR(...) CHARACTER SET assumedcharset) as VARCHAR(...) CHARACTER SET targetcharset)
   • Drop old column
   • Rename the new column
   • Fix any other dependency problems (indexes, triggers, foreign keys, etc)

This second option is only preferable if you want to change a small database (in terms of tables, columns and dependencies) or you need to perform odd conversions, otherwise I'd strongly suggest to use the data pumping solution.

Answer 2

ALTER CHARACTER SET ISO8859_1
SET default COLLATION ES_ES;

Script-Execute this to your active Database that you want to change!