Cannot create scalar function with SUBSTR_REGEXPR

[roland_bouman]

Hi all,

when I run this create function statement:

create function f_test_regexp(
  p_txt      NVARCHAR(5000)
, p_pattern  NVARCHAR(64)
)
returns found NVARCHAR(5000)
as
begin
    found := substr_regexpr(:p_pattern in :p_txt);
end;

I get this error message:

Started: 2016-10-19 10:25:34
Could not execute 'create function f_test_regexp( p_txt NVARCHAR(5000) , p_pattern NVARCHAR(64) ) returns found ...' in 64 ms 267 µs . 
SAP DBTech JDBC: [8]: invalid argument: exception: CompilationFailedException: No details
Program : 

#pragma function "\"NLRBVD\".\"F_TEST_REGEXP\""
export Void main(NullString "P_TXT" sP_TXT, NullString "P_PATTERN" sP_PATTERN, NullString "FOUND" & sFOUND)
{
    RSHeap heap = RSHeap();
    RSEnv env = RSEnv(heap);
    Int32 ucnt = 0;
    // scalar input parameter: P_TXT
    RSNString vP_TXT = RSNString( sP_TXT , env );
    // scalar input parameter: P_PATTERN
    RSNString vP_PATTERN = RSNString( sP_PATTERN , env );
    RSNString vFOUND = expr::null_<RSNString>(); // scalar output parameter
    RSNString vFOUND___2; // FOUND_2: intermediate def-variable
    #pragma location " line 8 col 5 (at pos 129)"
    {
        RSNString tFOUND = expr::substr_regex_<RSNString>(env,vP_TXT,vP_PATTERN,expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(0),expr::typecast_<RSNString>(expr::constructor_<RSString>("",0)));
    tFOUND.checkLengthLEAndThrow(5000z);
        vFOUND = tFOUND;
    }
    sFOUND = NullString(vFOUND);
    return;
}
line 16 ("NLRBVD"."F_TEST_REGEXP": line 8 col 5 (at pos 129)): Error: Function 'expr::substr_regex_<RSNString>(RSEnv, RSNString, RSNString, RSInteger, RSInteger, RSInteger, RSNString)' not found
line 16 ("NLRBVD"."F_TEST_REGEXP": line 8 col 5 (at pos 129)): RSNString tFOUND = expr::substr_regex_<RSNString>(env,vP_TXT,vP_PATTERN,expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(1),expr::typecast_<RSInteger>(0),expr::typecast_<RSNString>(expr::constructor_<RSString>("",0)));
                                                                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

But when I rewrite it like this:

create function f_test_regexp(
  p_txt      NVARCHAR(5000)
, p_pattern  NVARCHAR(64)
)
returns found NVARCHAR(5000)
as
begin
    select substr_regexpr(:p_pattern in :p_txt)<br>    into found<br>    from dummy;
end;

I only get a warning:

java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF

but the function seems to work fine.

So, how come? SAP/HANA, have you been drinking?

[RichHeilman · Accepted solution]

Development is aware of this particular issue and is working to implement a solution in an upcoming release.

Cheers,

Rich Heilman

SAP HANA Product Management

[roland_bouman · Accepted solution]

Thank you! Much obliged.

[roland_bouman · Accepted solution]

Hi Rich,

sorry to bother you again.

I'm assuming you and the dev team is already aware of it, but just to be clear: other %_REGEXPR functions suffer from the same issue. It'd be great if all of the REGEXPR functions would be transparently available in sqlscript.

BTW - I realize you might not have this info at this point, but if it is: can you perhaps give me an indication in which future version the fix will be available? If not, what method would you recommend for me to stay up to date and find out when this will be fixed?

Best regards,

Roland.

[RichHeilman · Accepted solution]

We do rollout blogs for every SPS around the time of the release. Features like this would be included in that rollout. I do not have any information as to when these particular issues will be resolved.

Cheers,

Rich Heilman