How to change characterset of an existing database for Interbase

Question

We have a database and our client is written with Delphi 2007. The latest Interbase 2009 is used. The database is old, I believe it was created around 2002 and is now over 25 GB. Recently I discovered that in IBConsole

upper('åäö')

generate 'åäö'. It should be 'ÅÄÖ'. This is swedish characters that are missing in the English alphabet.

I searched about the subject and found this link. The important part is:

How Can I Change the Default Character Set of an Existing Database? There is no supported way to do this. You should recreate the database from metadata.

This blog was written 2004-06-29 by Craig Stuntz, My hope is that Interbase has evolved since that and there is a way to change the characterset for Interbase 2009 with little effort. If not possible can it be done from a gbk backup file ? The last option is to create a completely new database with the right characterset and somehow pump data from the old one.

So I have 2 questions.

1. What is the easiest way to change characterset ?
2. What characterset should I choose to have upper('åäö') = 'ÅÄÖ'

EDIT: As Craig said there is no real way except copy the data that are aware the charset. So I choose another way.

vName := AnsiUpperCase(Nametosearch);
MakeCharLowercase(vName, 'åäö');
// Then use vName when search in database.

procedure TDuplicateDeptForm.MakeCharLowercase(var aName: String; aCharSet: String);
var
  vIndex, i: Integer;
  vChar: String;
begin
  for i := 1 to Length(aCharSet) do
  begin
    vChar := AnsiUpperCase(aCharSet[i]);
    repeat
      vIndex := AnsiPos(vChar, aName);
      if vIndex > 0 then
        aName[vIndex] := AnsiLowerCase(vChar)[1];
    until vIndex = 0;
  end;
end;

This simply convert back Swedish characters in this case back to lowercase as this is the result I get from Interbase. Maybe not the nicest solution but I think it works.

Answer 1

There is a free command line tool utility named FBClone: It is able to recreate your database changing the character set. The project use UIB components, so it is able to use Interbase or Firebird. http://code.google.com/p/fbclone/