Charset lost during conversion from BLOB to VARCHAR

[Stefan Batory]

Hi!

I try to convert text data stored in BLOB field into VARCHAR to display this text in dbgrid component. I'am doing it this way:

select cast(  MYBLOBFIELD as VARCHAR(4096) ) as MYTEXTFIELD from MYTABLE

As a result I receive the text but national characters are replaced by dot.

Can anyone help me to resolve this problem, please?

[Dimitry Sibiryakov]

27.05.2021 14:55, Stefan Batory wrote:
> As a result I receive the text but national characters are replaced by dot.

What is the BLOB's subtype?

--
WBR, SD.

[Mark Rotteveel]

What is the type of blob, is it a BLOB SUB_TYPE BINARY, or a BLOB
SUB_TYPE TEXT? In any case, the actual problems with characters being
replaced by a dot sounds like a problem with your component (or a
mismatch between character sets). Try casting explicitly casting with
the actual character set of the test data. For example if the data is
UTF8, use:

cast( MYBLOBFIELD as VARCHAR(4096) character set UTF8 )

Also make sure your component actual correctly shows the desired
characters. In some cases, your actual connection character set may also
be of influence.

Mark

[Martijn Tonies (Upscene Productions)]

Hello Stefan,

 

Have you tried

 

cast(myblobfield as varchar(4096) character set utf8)

 

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/89a5db89-8aac-4918-af02-ea80e78dff07n%40googlegroups.com.

[Stefan Batory]

Hello!

BLOB SUBTYPE is BINARY

I tried cast(myblobfield as varchar(4096) character set utf8)

Result is the same. I see national chars in TMemo component when I load raw data but I do not see it in TDBGrid or in result of PHP script where to display binary data I have to convert it to text first.

When I keep the same data in another field of type VARCHAR(4096) than data is displayed correctly in both components. But sometimes the text is very long and VARCHAR type is not enough to keep it.

BR, Stefan

[Svein Erling Tysvær]

Hi Stefan!

When storing things as BLOB SUBTYPE BINARY, I think Firebird assumes you to take full control of the content and possible transformations yourself (note that I don't know much about this part of Firebird, so what I write here could be wrong). Do you know what character set(s) was used when things were put into this field? Maybe you have to write cast(myblobfield as varchar(4096) character set ISO8859_1) or some other character set when utf8 doesn't work?

HTH,

Set

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/bc231889-a53f-4c50-8fe1-8d8ed4e5fc14n%40googlegroups.com.