Charset lost during conversion from BLOB to VARCHAR

14 views
Skip to first unread message

Stefan Batory

unread,
May 27, 2021, 9:38:21 AMMay 27
to firebird-support

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

unread,
May 27, 2021, 9:41:38 AMMay 27
to firebird...@googlegroups.com
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

unread,
May 27, 2021, 9:41:57 AMMay 27
to firebird...@googlegroups.com
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)

unread,
May 27, 2021, 1:03:49 PMMay 27
to firebird-support
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

unread,
Jun 1, 2021, 4:18:25 AMJun 1
to firebird-support
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

unread,
Jun 1, 2021, 5:25:09 AMJun 1
to firebird...@googlegroups.com
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

Reply all
Reply to author
Forward
0 new messages