Long texts in SAP HANA

[Former Member]

Hi,

Any idea on how do we store/retrieve long texts(SAP Script) in HANA?

Thanks.

[mauriciolauffer]

I have the same problem. If you solved it, please share here  

[lbreddemann]

Hi James,

what texts do you want to store or retrieve?

Can you provide an example of what you want to do?

Technically all long text descriptions in a NetWeaver system are stored as character data in different tables. These can be easily copied/replicated into your HANA instance.

But is this what you were asking about?

- Lars

[Former Member]

Hi Lars,

I am talking about texts that are, for example, more than 500 characters long, and typically retrieved/saved in ABAP via READ_TEXT, SAVE_TEXT. I am aware that these are stored in some tables, but not sure what exact tables we should read, and if this correct way of doing it.

Thanks,

JB*

[lbreddemann]

Hey JB,

I had a quick look into the function module and found that three tables are involved here:

STXH - text headers

STXL - text lines (for SAPScript texts)

STXB - text lines (for non-SAPScript texts)

Unfortunately, the access to the data is rather complex and does use the ABAP table cluster interface.

That means, that just as for pool/cluster tables, the text information are stored in a encoded binary format that cannot be processed directly by SQL.

This format can also be compressed, making it even more difficult to access the data from SQL.

Sorry, but I don't know of any way to directly use this data on DB level.

You may need to export the data to a plain table so that it could be read from there.

- Lars

[stefan_koehler]

Hi Lars,

> That means, that just as for pool/cluster tables, the text information are stored in a encoded binary format that cannot be processed directly by SQL.

I am not quite sure how the data of an ABAP pool/cluster tables is stored on HANA database level, but i needed to reconstruct that kind of data several times on database level (with LogMiner on Oracle of course) in the past.

You are usually able to reconstruct the raw VARDATA with help of the SAP table definition and the character set. It is even more easier, if the data is still available in the SAP table pool.

Regards

Stefan

[lbreddemann]

HI Stefan,

the way Pool/Cluster tables are stored on SAP HANA is not different in any way to any other DBMS we support. Reconstructing the data is of course possible (the DBSL does it, so any SQL client could), but the actual compression and encoding scheme is not officially documented afaik.

And my impression (==> not knowing it for sure) is that the texts data is encoded into a VARDATA column in the same compressed format.

Hmm... maybe "...binary format that cannot be processed directly by SQL...." was misleading here. I meant that the data from the binary columns need to be decoded by the client application after retrieval.

Cheers, Lars

[stefan_koehler]

Hi Lars,

thanks for your reply. Hmm … maybe not officially documented by SAP, but we (the clients, partners or consultants) have brains as well

Good to know that it is the same procedure on HANA as well.

For all the guys out there who are interested into encoding that data  - please check the attached PDF document published by Dr. Thimo Bastuck and Claudia Hüffer. Unfortunately it is written in german, but i think you get the encoding procedure from it very well, even if you don't understand german.

Regards

Stefan

P.S.: You need to rename the attached document into *.pdf again, because of the "Attach Files" function on SCN does not allow PDFs

SAP_Oracle_Recovery_Fallbeispiele.txt.zip

[lbreddemann]

Stefan Koehler wrote:
Hi Lars,
thanks for your reply. Hmm … maybe not officially documented by SAP, but we (the clients, partners or consultants) have brains as well 

Never questioned that... if this worked/works for you, that's a nice thing to know.

It's just that I'm under the impression that at least sometimes there's more to it than concatenating the fields. Especially when compression comes into play, I don't know how to handle this.

But maybe this is something for a curious partner consultant to find out

cheers, Lars

[mauriciolauffer]

Stefan, very thanks!

Using your PDF I could understood a bit more how Long Texts are saved into the table STXL. The text is converted to Hexadecimal, then to Binary (BLOB/RAW). I know that is possible to do more steps like zip text, in my case I didn't need it.

I tried to do the inverse way to extract the text from HANA, but it wasn't possible. I converted from RAW to Hexadecimal with the function rawtohex() and after I didn't find any function to convert from Hex to Text again. To test this convertion, I did this last step using a website to convert from Hex to ASCII, just to see if the result would be the expected text.

I used this website http://www.dolcevie.com/js/converter.html and I got the right text.

I didn't know if is possible to do it just in HANA, I mean, I saw a convertion like that using HANA XS, but nothing using HANA DB.

If somebody has some idea, please share

[mauriciolauffer]

I know, it's an old thread, but it still can help somebody

There is a blog about this subject which explains how to solve the problem. Check it out guys.