I'd like to understand how to proper convert data from a binary blob to a text blob with encoding

[nickolasdeluca]

Recently I caught a "wrong practice" in our database, which was using binary blobs (sub type 0) to store texts.

Even though there is not actually a problem with doing that, binary blobs won't store enconding and that can cause issues, specially when you're working with third party libraries, like node-firebird.

Our database is WIN1252 encoded, a legacy practice from more than 10 years ago.

I started converting those binary blobs to text blobs with the proper encoding, created another field, updated the data from one to the other, renamed the old and then renamed the new field as the old one, should've been easy right?

Nope. Now, whenever we perform queries on that field using LIKE or CONTAINING, we get the following exception:

Cannot transliterate character between character sets.

I've come to understand that this is because some of the data stored in that field is considered UTF8 and some UTF8 characters can't be translated to WIN1252.

Just to sanity check, I created a new table, added a field using the same domain as the field I described earlier, added some records manually and performed the LIKE/CONTAINING queries, worked like a charm.

What I want to know is how to properly transfer my data from one field to the other encoding it to WIN1252 so that I can perform those type of queries again.

(We managed to get around that issue casting the new field to the same domain as the old one when performing the LIKE/CONTAINING queries, thankfully it started working again)

I don't know if this will help, but here's the DDL for both domains:

The new BLOB Text field

CREATE DOMAIN D_BLOB AS
BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET WIN1252
COLLATE WIN1252;

The old BLOB Binary field

CREATE DOMAIN D_MEMO AS
BLOB SUB_TYPE 0 SEGMENT SIZE 80;

[mrotteveel]

Also cross-posted on firebird-support: https://groups.google.com/g/firebird-support/c/J21RlER7lfg

[PedroCostinha]

Hi! I found that this issue was already resolved in the Firebird Support mailing list.

According to your follow-up, the problem wasn't simply the conversion from BLOB SUB_TYPE 0 to BLOB SUB_TYPE 1.

Some of the existing data had been corrupted because a database connection using RUSSIAN_CHARSET was used against a WIN1252 database. This affected strings in triggers and stored procedures, which eventually introduced incorrectly encoded data.

As Pavel Cisar suggested in the mailing list, a safer migration approach is to:

1. Read the original binary BLOB data without modifying it.
2. Validate the actual character encoding of the stored bytes.
3. Identify problematic records and repair the corrupted data.
4. Convert the validated text to the target WIN1252 encoding.
5. Store the corrected values in the new BLOB SUB_TYPE TEXT column.
6. Ensure all database connections use the appropriate character set to prevent future corruption.

Simply casting the BLOB or changing its subtype does not repair incorrectly encoded data.

For anyone encountering the same issue, here's the original discussion containing the resolution:

https://groups.google.com/g/firebird-support/c/J21RlER7lfg

Credits to Nickolas Deluca for identifying the root cause and Pavel Cisar for the data validation recommendations.

Hope this helps others facing similar BLOB conversion issues!