Firebird SQL 3.0.4 content encoding issue

[Yaron 53]

I am having a really weird issue with Firebird SQL 3.0.4,

 

I have two servers, one local which I use for development and one production, both are running the exact same version of Firebird, using the same "firebird.conf" configuration file.

 

On my production server, if I enter Hebrew text (e.g. "בדיקה"), I get "?????" in the DB. However, it works just fine on my development server.

On my development server, if I enter German umlauts (e.g. "äöü"), I get "???" in the DB. However, it works just fine on my production server.

 

Things I tried:
1. I verified the text sent to the DB is valid (appears correctly in UTF8 encoded log files).
2. I file-compared "firebird.conf" on both production and development server and they are identical.

3. I tried using this Firebird SQL command to reset the collation "alter character set utf8 set default collation unicode_ci_ai;".

4. Adding 'CharacterSet=utf8' to my connection params (full connection code below).

5. If I use DBeaver to view the database structure, I see all the "varchar" columns have a "charset" set to "NONE".

 

    dbMain := TFDConnection.Create(nil);
    with dbMain.Params do
    Begin
      Add('DriverID=FB');
      Add('Server=localhost');
      Add('Database=c:\DB\myDB.FDB');
      Add('User_Name=SYSDBA');
      Add('CharacterSet=utf8');
      Add('Password=my_password');
    end;
    dbMain.Open;

Any ideas?

Edited September 18, 2019 by Yaron

[Yaron 53]

I think I figured it out, when I created the DB using DBeaver, it simply created all columns with a CHARSET of "NONE".

 

Now I'm trying to figure out a way to automate the conversion from NONE to UTF8 as there doesn't seem to be any straight-forward way of making it happen.

[Yaron 53]

Just to conclude, after scouring the internet to find an elegant solution, I eventually had to brute-force it by creating new columns using this command :

ALTER TABLE [TableName] ADD [NewColumnName] VARCHAR(100) CHARACTER SET UTF8;

 

Then I used DBeaver's "Copy" & "Advanced Paste" to copy over the string data from the old columns to the new columns.

 

I had to do this for approximately 10 tables with a combined total of around 60 columns.  The whole process took about 1.5-2 hours.

[Jacek Laskowski 57]

Try DBWorkbech