CODEWITHSUNDEEP
castingcharacter-encodingblobfirebird
Mikhail Sergievskiy
Mikhail Sergievskiy

Reputation: 13

Cannot display text data stored in blob sub_type 0 properly

There is a field in a table in a database with data type BLOB SUB_TYPE 0. It stores text data generally (the field contains 'payment description', so mostly there are values like 'sent successfully', '15$ to go' and so on in that column). I do not know for what purpose that field was designed as BLOB SUB_TYPE 0, but the database is provided 'as is' and I cannot affect this.

I need to display the contents of that field as text.

What do I do:

select 
cast(paym_desc as varchar (1000))
from payments

It works, but it works for Latin characters, digits and !@#$%^&*(){}[] signs (those contained in standard ASCII charset I suppose). When it comes to Cyrillic, or Euro signs or pound symbol etc., I get dots instead of symbols.

To troubleshoot the situation I have tried to re-code the column using the solution provided here: Firebird 2.5.2 change blob subtype

I've tried creating columns with blob sub_type 0 character set win1251 and blob sub_type 0 character set win1251 types and have copied my payment description there.

It is kind of working (no more dots!), but I (as expected) get different values in these columns and none contains what I need. For example, if I set my payment description to

ùúûĀąĈô£¥®҈ѾВАСЯвасяӃ€€€€

I get

ùúûĀąĈô£¥®҈ѾВАСЯвасяӃ€€€€ in win1251 column and ùúûĀąĈô£¥®҈ѾВРin win1252 column.

Could somebody please give an advise on how to display such values properly?

Upvotes: 1

Views: 2597

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109090

A blob sub_type 0 is a binary blob. Binary blobs don't have a notion of character sets. You need to use blob sub_type 1 (or blob sub_type text) if you want to include a character set.

Based on the conversions you show in your question, it looks like you are storing UTF-8 into the binary blob, and then retrieve it as WIN1251 or WIN1252.

Upvotes: 3

Related Questions