The HANA SQL REPLACE function provides inconsistent result when invoked from ABAP using AMDP

[Doug_Munford]

I am using HANA SQL REPLACE function in a HANA stored procedure created through AMDP and it appears to behave differently when the function is invoked from the SAP NetWeaver 7.5 system as an AMDP class written in ADT Eclipse versus directly executing the same code in HANA. The with argument of a single space is treated as an empty string but only when invoked from ABAP.

The Background:

I was using AMDP functionality from ABAP as an exercise in moving things down to HANA and it involved manipulation a STRING object and the requirement is bigger than an SSTRING at 1333 and so couldn’t be done in CDS (but the procedure can be wrapped in CDS). At the moment it is being called through an ABAP RFC by Business Objects Data Federator and while that will change, it is a transformation journey too far for this iteration.

The STRING object contains Rich Text from a Rich Text Editor in HTML and the HTML needed transformation from the editor to the report generator (which is BO 4.34 Crystal Reports Enterprise). We needed to change a bunch of things including bold and italic:

• <em> to <i>
• <strong> to <b>

We also tried to tidy up the HTML. This is a bit of a prototype, and a bit of experimenting on my behalf for ways to solve a problem. And what I have encountered may be a bug or it may be intended behaviour, but I would like to change it.

The Problem (Simplified to its core)

I am using OSQL REPLACE function and it appears to behave differently when the function is called from the SAP NetWeaver 7.5 system through AMDP versus directly in HANA

1. Prototype (written in HANA studio): HANA Studio SQL and the function behaves as expected
2. Implementation: ABAP call through AMDP -> HANA and my transformation REPLACE statement does not behave the same
3. Investigate: Go back to Hana Studio: CALL the AMDP created stored procedure and it is OK.

At the heart of the problem is the simple REPLACE statement with a space in the with argument

The code is a lot more complicated and involves thousands of records and strings of 300-12000 characters … but I have reduced it to just the piece that behaves errantly.

REPLACE(REPLACE(REPLACE(<field>, 'em>','i>'),'<i> </i>', ' '),'</i> <i>',' ')

This changes the <em> begin and close tags to <i> and it removes needless italic spaces or un-italicised spaces left around by the rich text editor.

String in (An HTML 4.01 compliant fragment)

<p>Hello to <em>My</em> <em>Friends</em> <em>in the Community</em>,<em> </em>I wish you well</p>

String out: (In Bold highlighting the differences where the spaces are lost)

(HANA SQL): <p>Hello to <i>My Friends in the Community</i>, I wish you well</p>

ABAP result via AMDP: <p>Hello to <i>MyFriendsin the Community</i>,I wish you well</p>

1. HANA Studio SQL

DO BEGIN
DECLARE mystring string 
  = '<p>Hello to <em>My</em> <em>Friends</em> <em>in the Community</em>, I wish you well.<p>' ;
SELECT mystring as "before",
REPLACE(REPLACE(REPLACE(mystring, 'em>','i>'),'<i> </i>',' '),'</i> <i>',' ') as "after" from dummy;
END

Result:

2.AMDP Procedure from ABAP

class ycl_space_issue_sample definition
  public
  final
  create public .
  public section.
    interfaces if_amdp_marker_hdb .

    methods amdp_example
      importing
        value(i_before) type string
      exporting
        value(e_after) type string.
  protected section.
  private section.
endclass.

class ycl_space_issue_sample implementation.
  method amdp_example
  by database procedure for hdb
           language sqlscript options read-only.

    declare defaultstring string =
      '<p>Hello to <em>My</em> <em>Friends</em> <em>in the Community</em>, I wish you well.<p>' ;

    if length( i_before ) > 0 then
        defaultstring = i_before;
    end if;

    select
      replace(replace(replace(defaultstring, 'em>','i>'),'<i> </i>',' ')
	,'</i> <i>',' ') as "after"
      into e_after from dummy;
   endmethod.
endclass.

Result: (MyFriendsin shows the loss of space)

3. Back to HANA Studio to call the AMDP Procedure

DO BEGIN
DECLARE I_STRING string 
  = '<p>Hello to <em>My</em> <em>Friends</em> <em>in the Community</em>, I wish you well.<p>';
DECLARE E_STRING string;
CALL "YCL_SPACE_ISSUE_SAMPLE=>AMDP_EXAMPLE" (:I_STRING, :E_STRING );
SELECT I_STRING as "before", E_STRING as "after" from dummy;
END<br>

Result: perfect again

So can anyone tell me why this is so. and is it a bug or a feature. Can I change the behaviour of the invocation. I can work around it but will be doing more in AMDP and I am interested if others have found issues like this as well.Versions:

• ABAP System: SAP NetWeaver 7.50 (BW on HANA) recent SP21, Kernel 7.53
• HANA 2.0 SP5

regards Doug.

[Doug_Munford]

There is a simple workaround to not use the REPLACE with the replace clause with a single space character instead use a REPLACE_REGEXPR but I am after a FIX or a reason. This replacement code in the AMDP routine is stable and produces same result as in SQL

select 
  REPLACE_REGEXPR('<i>( )</i>' IN REPLACE_REGEXPR('</i>( )<i>' IN 
  REPLACE(defaultstring, 'em>','i>') WITH '\1' OCCURRENCE ALL) WITH '\1' OCCURRENCE ALL) 
  as "after" into e_after from dummy;

[Joerg_Brandeis · Accepted solution]

Hi Doug,

thank you for the detailed description of the problem. I would be happy if everyone would take this much time to ask the community for support.

The problem is the different behaviour of ABAP and SQLScript when handling character literals with a space at the end. This inludes the single space. You can find some details in my blog about this topic:

https://www.brandeis.de/en/blog/abapvarcharmode-blanks-in-sqlscript/

I think it is the ABAPVARCHARMODE that can explain this strange behaviour. The CHAR(32) Function instead of a single space literal can create a consistent behaviour for the procedure.

Regards,

Jörg

[Doug_Munford · Accepted solution]

Thank you. Taking taking both answers Jorg has provided and incorporating them, I get a consistent result between the two and can fix the issue

Setting SQL to run like ABAP I can see the same issue then fix it with swapping the literal space for CHAR (32)

DO BEGIN
DECLARE mystring string 
  = '<p>Hello to <em>My</em> <em>Friends</em> <em>in the Community</em>, I wish you well.<p>' ;
SET 'ABAPVARCHARMODE' = 'TRUE';
SELECT mystring as "before",
REPLACE(REPLACE(REPLACE(mystring, 'em>','i>'),'<i> </i>',' '),'</i> <i>',' ') as "after" from dummy;
END

And fixing becomes:

REPLACE(REPLACE(REPLACE(mystring, 'em>','i>'),'<i> </i>',CHAR (32)),'</i> <i>',CHAR (32)) as "after" from dummy;<br>