CODEWITHSUNDEEP
sqlhanacharacter-set
Laghorn
Laghorn

Reputation: 43

HANA: Unknown Characters in Database column of datatype BLOB

I need help on how to resolve characters of unknown type from a database field into a readable format, because I need to overwrite this value on database level with another valid value (in the exact format the application stores it in) to automate system copy acitvities.

I have a proprietary application that also allows users to configure it in via the frontend. This configuration data gets stored in a table and the values of a configuration property are stored in a column of type "BLOB". For the here desired value, I provide a valid URL in the application frontend (like http://myserver:8080). However, what gets stored in the database is not readable (some square characters). I tried all sorts of conversion functions of HANA (HEX, binary), simple, and in a cascaded way (e.g. first to binary, then to varchar) to make it readable. Also, I tried it the other way around and make the value that I want to insert appear in the correct format (conversion to BLOL over hex or binary) but this does not work either. I copied the value to clipboard and compared it to all sorts of character set tables (although I am not sure if this can work at all).

My conversion tries look somewhat like this:

SELECT TO_ALPHANUM('') FROM DUMMY;

while the brackets would contain the characters in question. I cant even print them here.

How can one approach this and maybe find out the character set that is used by this application? I would be grateful for some more ideas.

Upvotes: 0

Views: 1908

Answers (2)

Lars Br.
Lars Br.

Reputation: 10396

What you have in your BLOB column is a series of bytes. As you mentioned, these bytes have been written by an application that uses an unknown character set.

In order to interpret those bytes correctly, you need to know the character set as this is literally the mapping of bytes to characters or character identifiers (e.g. code points in UTF).

Now, HANA doesn't come with a whole lot of options to work on LOB data in the first place and for C(haracter)LOB data most manipulations implicitly perform a conversion to a string data type.

So, what I would recommend is to write a custom application that is able to read out the BLOB bytes and perform the conversion in that custom app. Once successfully converted into a string you can store the data in a new NVCLOB field that keeps it in UTF-8 encoding.

You will have to know the character set in the first place, though. No way around that.

Upvotes: 1

hbourchi
hbourchi

Reputation: 309

I assume you are on Oracle. You can convert BLOB to CLOB as described here.

http://www.dba-oracle.com/t_convert_blob_to_clob_script.htm

In case of your example try this query:

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(<your_blob_value)) from dual;

Obviously this only works for values below 32767 characters.

Upvotes: 0

Related Questions