Subject How to change the encoding of BLOB content in database and other problems with BLOB text data (IBX, Delphi)
Author
Post date 2013-12-22T20:06:09Z
I am using Firebird 2.1.x with Delphi 2009 - Delphi XE IBX components, database has UTF8 encoding. And I would like to update the content of BLOB fields: change the encoding of the characters from the default single-byte encoding to UTF16 encoding and do it in database using some kind of UDF or stored procedure. Are such procedures available for Firebird 2.1?

This can seem the strange requirement but it arise from the following problem:when new BLOB field is automatically created on TIBDataSet (e.g. by Add Fields), its default data type is TMemoField. I have to change it manually to TWideMemoField and only then the text data can be displayed correctly in the program. Delphi string datatype (starting from Delphi 2009) is UTF16 based and that means that one character is encoded in two bytes and this is clearly visible when the BLOB content is viewed by some tool (IBExpert, Flamerobin etc.) which display both bytes of the single original character as separate 1-byte characters.

I am not happy about this but the main requirement is to store data from Delphi program to database and read back into Delphi program correctly and that can be done with 2byte characters without problems, so I am stick with such kind of solution.

And therefore - there are times when Firebird level conversion (to and from) from BLOB 2-byte characters to UTF8 varchar characters is required. E.g. when third party data are imported into BLOB fields they can be single-byte characters. Conversion to UTF8 characters is required (aparently) when UTF16 BLOB contens needs to be used in search conditions or some part of BLOB content should be assigned to UTF8 varchar fields and so on.

So - is there Firebird UDF or procedur to change the encoding of varhcar of BLOB fields?

I know - generally this can be the problem of IBX components and no the Firebird itself but generally we are really happy with IBX apart from this BLOB issue.

I have also tried to change the BLOB subtype from text to raw but that does not helped me. I am required to store BLOB text content into UTF16 encoding and do conversion from time to time.

Thanks,
Jonatan