Subject Re: [firebird-support] Changing database charset
Author Alexandre Benson Smith
Fabiano Bonin wrote:
> I manage several complex and different databases, and pump all them is
> not an option for me now, so i need to investigate the mess with
> system tables approach... :-)
> I'm testing with a table named CAD1, which has a column named NOME. I
> need to change the charset to ISO8859_1 and the collation to PT_BR.
> Can i consider a 'safe' procedure to run the script below in single
> user mode, and then backup/restore the database before using it?
> -- change charset and collation of the column domain
> update rdb$fields a set
> a.rdb$character_set_id = 21,
> a.rdb$collation_id = 16
> where
> a.rdb$field_name = (
> select
> b.rdb$field_source
> from
> rdb$relation_fields b
> where
> b.rdb$relation_name = 'CAD1'
> and b.rdb$field_name = 'NOME' );
> -- change charset of the column
> update rdb$relation_fields a set
> a.rdb$collation_id = 16
> where
> a.rdb$relation_name = 'CAD1'
> and a.rdb$field_name = 'NOME';
> Regards,
> Fabiano.


I have the same issue when started to use Firebird PT_BR (a non official
version that supports the current PT_BR collation on previos FB versions).

What I did was extract the metadata, change it (when you use domains
it's really a small number of places that needs to be changed), create
an empty database and pump the data over.

I think is possibile to use the system tables approach you show.

But I will tell you to be double sure that you don't try it on a
database you care about.

I think that you could get a lot of "arithmethic overflow or string
truncation or cannot transliterate between charsets" and the like, or
even changed chars dependent on your previous charset.

I think the best way will be to try it :-)

Make sure you have some way to compare the data to know that you have
what you expected.

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil