Skip to Content

Cannot create scalar function with SUBSTR_REGEXPR

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?
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Oct 20, 2016 at 01:51 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Oct 20, 2016 at 03:53 AM

    Ok, SQL Script is not a superset of SQL, which also means that not all SQL functions will just work in SQL Script statements (like the assignment you used).

    SELECT however is a supported command and it provides the SQL context which in turn gives access to all SQL functions.

    The warning really is just that: a warning about possible unacknowledged side effects (worse performance and higher resource consumption in this case).

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 20, 2016 at 01:04 PM

    Hi Lars,

    Thanks for taking the time to respond and to share your insights.

    I understand the point you are making. I guess I would expect a more descriptive error message.

    It's also quite different from what I am used to of procedural extensions in other RDBMS-es - there, calling built-in scalars inside procedural code is typically transparent. And in HANA too, for example, SUBSTR gives none of these issues. To me, SUBSTR_REGEXPR is more or less a fancy SUBSTR, It does not strike me as intuitive that it would behave so differently.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 20, 2016 at 01:08 PM

    I agree. It's not intuitive.

    Add comment
    10|10000 characters needed characters exceeded