RLANG in repository procedure

[Former Member]

Hi,

Is R supported in a repository procedure?

I did try it out, but it complains about the result view not being supported.

Thanks,

Steven.

[lbreddemann · Accepted solution]

Could you please post your code and the exact error message text?

Also: what revision are you working with?

- Lars

[Former Member · Accepted solution]

We are on SP05 revision 48

I get this error message

Repository: Encountered an error in repository runtime extension;error: UDE:Procedure -> resultviewresult view not supported; please create a table function, will no generate result view _SYS_BIC.

create type TT_IN as table(S nvarchar(3), M nchar(3))

create type TT_OUT as table (KEY varchar(10), VAL decimal);

CREATE PROCEDURE CALC_R (

          IN input TT__IN,

          OUT result TT_OUT)

          LANGUAGE RLANG

AS

BEGIN

----

.... blar blar crazy R code

.....

  names(t.cor) <- c('KEY','VAL')

  result <- t.cor

END;

-

[lbreddemann · Accepted solution]

Hi Steven,

I just created a super-dummy R Lang procedure both via SQL and via the modeller and never got this error message.

What exactly is it that you are assigning to the result table there?

- Lars

[Former Member · Accepted solution]

Hi,

That's not how I created the procedure.  But your right, if I got through the modeler looks like all my problems are solved!!

I was following the documented examples and creating a procedure as follows:

--> from project browser ---> select your package --> right click --> new file--> enter something like PROC_STUFF.procedure

Then you get a different editor.  That basically rejects everything you do.

Now I am confused because there appears to be 2 different ways to add procedures to a package and they are both completely different.

HUH? which one to use?  (ok, thats obvious the one that works 🙂  

Why they different?

Anyway.. thanks!

[lbreddemann · Accepted solution]

There are two different ways, because basically we have two different development environments backed by a repository in HANA.

There's the one you implicitly use when you use the modeler.

That one has been with us since the beginning of HANA.

Now with SPS5 the xs development required to have a much broader approach to what goes into the repository.

Although there's some overlap of functionality the xs environment is not meant to replace the modeler (for now). So all the classic modeling still should be done in the modeler environment.

- Lars

[Former Member · Accepted solution]

That's good to know, thanks for clearing that up.

Now all the problems I get are in the new xs development environment on SP05 and commit work from there (I never used the modeller), did you ever try that out?

thanks,

Steven.

[Former Member · Accepted solution]

Hi Steven, hi Lars,

I have exactly the same problem, trying to implement and activate a RLANG procedure through the new SQL Editor. (Syntax highlighting was disabled, whereas I could save the procedure).....

My question would be further @Lars: you mentioned you could manage to create the RLANG procedure  in the Modeler perspective? I only see a greyed out dropdown box Language.

Creating and droping RLANG procedures is not very nice all the time... How would one develop reusable RLANG procedures?

Thanks & best regards

Alex

[Former Member · Accepted solution]

Hi,

We gave up using the repository and the new editor for store procedures as there were too many limitations like the one you just encountered.  The whole process is not very productive in general - it was just taking too long to put the objects in the repository and we ran out of time.  I am hoping that will improve in future versions as the concept as a whole is pretty good. 

I want to go back and try again with the RLANG in the next days.  So I report back if I get it working.  I'm thinking the table types might be the problem, perhaps I try working with real-tables as input and output parameters, or maybe its a rights issue?  I test it again soon.

S.

[henrique_pinto · Accepted solution]

Ccing so he is aware of the procedure editor issues.

[lbreddemann · Accepted solution]

Hi Alexander,

sorry for the long pause - had been pretty busy recently.

The reason for the disabled language selection box in the HANA studio "New SQL procedure"-dialogue is that it's required to set a DB parameter to allow any other language than SQLscript.

= Configuration

  Name                | Default     | System

  indexserver.ini     |             |

    repository        |             |

      sqlscript_mode  | default     | UNSECURE

Unfortunately it's a bit difficult to find this setting in the developer guide (http://help.sap.com/hana/hana_dev_en.pdf#page=175)... (but it's there :-))

- Lars

[Former Member · Accepted solution]

Hi Lars,

oh yes thank you!

Best regards

Alex

[Former Member · Accepted solution]

Hmmm - seems docu change, now I see somethuing about UNSECURE mode on page 190:

• Select the language in which you are writing the procedure.
• Note: You can choose to create procedures in Read Write mode and make use of L- Lang and R-lang languages only if you have done the repository configuration for the field SqlScriptMode. Two values for SqlScriptMode feild exist, DEFAULT, and UNSECURE. By default DEFAULT is assigned which means Read Only mode with non-modifiable access mode and SQL Script as language. To change the configuration, go to administration console -> Configuration tab -> indexserver.ini -> repository -> SqlScriptMode, and assign the required value.

Anyway - I tried that -> even retstarted the whole HDB (twice - 1st from HDB studio, 2nd time restarted whole server and reopened also the HANA Studio) -> when I go to Indexserver.ini -> I do have UNSECURE option configured for the SqlScriptMode ...

I wasn't able to make it running for already created procedure -> but then I created new procedure and it was working fine ... but sometimes the editor complains about errors which do not exists in R ... nevermind ...

[Former Member · Accepted solution]

I tested to deploy an RLANG based procedure through the repository / project file approach. However, I found a way how that the compiler does not claim about LANGUAGE RLANG, by setting the appropriate indexserver.ini parameter.

Now I have to provide at least one output parameter that’s ok, I also provide input parameter ordered, as I wish to call them later in the wrapper, like:

in var1,
in var2,
in var3,
out var4 

The strange thing: the generated procedure in the _SYS_BIC schema does not create the same parameter order, like above, instead I’m getting this:

in var1,
in var2,
out var4, 
in var3

in the ‘Parameters’ tab. For this reason I cannot call the procedure. Is this a kind of bug, or is there a way to get it running?

Thanks & regards
Alex

[lbreddemann · Accepted solution]

Hi Alex,

that's at least not what I would expect.

Would you mind posting the source code for your procedure?

As a work around you may try to use named parameters ( var1 => 'x', var2 => 123 ...)

- Lars

[Former Member]

Hi Lars,

named parameters seem to eleminate the order issue as problem cause...

I thought that the order problem caused this message:

SAP DBTech JDBC: [2048]: column store error:  [2048] _SYS_BIC.ag-dev.open-sap.models/R_WRAPPER: line 83 col 2 (at pos 3308): GenericFailure exception: column store error: search table error:  [34082] Execution of R script failed.;

But I noticed now, that after the paramter change of > indexserver.ini -> repository -> sqlscript_mode to UNSECURE this failure appears.

(The standard exsample for instace:

Could not execute 'CALL DIVIDE_SPAMDATA()' in 273 ms 781 µs .

SAP DBTech JDBC: [2048]: column store error:  [2048] SYSTEM.DIVIDE_SPAMDATA: line 3 col 1 (at pos 44): GenericFailure exception: column store error: search table error:  [34082] Execution of R script failed.; )

Is there some dependency in configuration?

Thanks and regards

Alex