Malformed string when inserting UTF8 database

Question

I use Delphi 2010, Firebird 2.5.2, IBExpress components.

Database charset is UTF8. In db-connection UTF8.

Database:

var 
  Database: TIBDatabase;
begin
  ...
  Database.params.Clear;
  Database.params.Add('user ''SYSDBA'' password ''masterkey'' ');
  Database.params.Add('page_size 16384');
  Database.params.Add('default character set UTF8');

Table:

CREATE TABLE NEW_TABLE (
    NEW_FIELD  VARCHAR(255)
);

connection code:

  Database.params.Clear;
  Database.params.Add('user_name=SYSDBA');
  Database.params.Add('password=masterke');
  Database.params.Add('sql_role_name=UTF8');
  Database.Open;

insert code:

var
  IBSQL: TIBSQL;
begin
  IBSQL := TIBSQL.Create(nil);
  try
    IBSQL.Database := db;
    IBSQL.Transaction := tr

    IBSQL.SQL.Text := 'insert into NEW_TABLE (NEW_FIELD) values (:param)';

    IBSQL.params[0].Value := 'Ãabc©'; // unsupported symbols :(

    if not IBSQL.Transaction.Active then
      IBSQL.Transaction.StartTransaction;

    IBSQL.ExecQuery; // "Malformed string" exception here

    if IBSQL.Transaction.Active then
      IBSQL.Transaction.Commit;
  finally
    FreeAndNil(IBSQL);
  end;
end;

I get "Malformed string" exception. How to insert this string?

Coppock

CREATE TABLE NEW_TABLE ( NEW_FIELD NVARCHAR(255) ); ? – Victoriavictorian

@bummi: no. firebirdsql.su/doku.php?id=tipy_dannyx&s[]=varchar Hmm.. i'd avoid using IBX with FB... Somethign is wrong, either database is not UTF-8 (maybe it is Unicode_FSS for example), or connection is not UTF-8, or bug in IBX for D2010 (who knows if it did supported UTF-8?). More source code is needed. – Sheldon

UTF8 should support these symbols just fine so first I would check the field's definition (using isql or frlamerobin or whatever tool you use), is it really UTF8. If it is, then my quess is that this is IBX being incompatible with FB, switch to some other component set. – Shakedown

@Shakedown he could create that field in NONE charset and later switch database to UTF8 and think he changed the table as well. I'd look on the field using IBExpert tool. Don't know if FlameRobin would show inherited default charset... – Sheldon

Offtopic: nice library to cut corners with FB+IBX: loginovprojects.ru/index.php?page=ibxfbutils#ibxfbutils – Sheldon

sorry, my mistake. I set ROLE instead lc_ctype – Coppock

Still, fb.InsertRecord(fdb, nil, 'TESTTABLE', ['ID', 'NAME', 'SUMMA'], [1, 'FIREBIRD - FOREVER', 100500]); looks easier than this whole snippet :-) – Sheldon

@Coppock You could also add that as an answer to your own question; might make it more visible than a comment – Baklava

Answer 1

Make sure that the charset of your field is UTF-8.

When creating a new field, you have two options:

1. Create a domain with the default charset and collate and set the field with the domain
2. Create the field without domain, setting the charset and collate manually.

In the image below , is displayed the field created with the domain using the IBExpert.

Meanie