Skip to Content
2

Cannot create scalar function with SUBSTR_REGEXPR

Oct 19, 2016 at 08:51 AM

407

avatar image

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?
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Rich Heilman
Oct 20, 2016 at 01:51 PM
1

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thank you! Much obliged.

0

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.

1

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

1
Lars Breddemann
Oct 20, 2016 at 03:53 AM
2

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).

Share
10 |10000 characters needed characters left characters exceeded
Roland Bouman Oct 20, 2016 at 01:04 PM
1

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.

Share
10 |10000 characters needed characters left characters exceeded
Lars Breddemann
Oct 20, 2016 at 01:08 PM
1

I agree. It's not intuitive.

Share
10 |10000 characters needed characters left characters exceeded