Skip to Content
author's profile photo Former Member
Former Member

Consume Stored Procedures In HANA ODATA Services

Hi All,

I have created a stored procedure with 4 input and 4 output parameters as below,

PROCEDURE "BPS_SCHEMA"."AB_BPS::Final_SP"

( in in_flag varchar(1),

in in_carrid nvarchar(3),

in in_connid nvarchar(4),

in in_fldate nvarchar(8),

out out_spfli "BPS_SCHEMA"."SPFLI_TT",

out out_sflight "BPS_SCHEMA"."SFLIGHT_TT",

out out_sbook "BPS_SCHEMA"."SBOOK_TT",

out out_error_message nvarchar(500) )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA "BPS_SCHEMA"

READS SQL DATA AS

BEGIN

My Query ....

END;

Tried calling the above procedure in a Calc. View and use it in my service.

But, I am getting some error like "No measure defined in a reporting enabled view"

when I tried to activate the view .. Can we add tables as output parameters in views?

Could you please suggest me some way to achieve this?

Thanks in advance,

Arun.

Capture.PNG (6.1 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 26, 2015 at 03:46 PM

    Hi Flourian,

    Multiple table data display issue was fixed by using views with multiple entities as you said.. Thanks for the clarification ..

    Now I have created a procedure with parameters to perform CRUD operations and used it in a service as below,

    service

    {

    "ABS_SCHEMA"."SBOOK" as "CRUD"

    keys generate local "FLAG"

    create using "ABS_Hana_Ui5::ABS_Hana_Ui5";

    }

    https://j1tbca70991a.us1.hana.ondemand.com/BPS_Hana_Ui5/Hana_Ui5.xsodata - URL generated.

    Now I am unable to update record to SBOOK table with this URL in browser .. I have tried as below ..

    https://j1tbca70991a.us1.hana.ondemand.com/BPS_Hana_Ui5/Hana_Ui5.xsodata/CRUD(FLAG='C',CARRID='AA',CONNID='0017')/Results

    But, Update is not being done. Could anyone please let me the correct way to test this ..


    Hana_Ui5.txt.zip (1.2 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Florian Pfeffer

      Hello Florian,

      Thanks for all the help .. I really appreciate it.

      I tried as you said but I am getting some other token instead of x-csrf-token as below,

      Still facing that 403 Forbidden error .. I hope the link given will help me, will go through and update on that ..

      Thanks & Regards,

      Arun.

      Capture2.PNG (42.5 kB)
  • Posted on Nov 20, 2015 at 06:15 PM

    Hello Arun,

    the error message itself that no result column of the vew is defined as a measure. At least one measure is necessary in case the data category of the view is set to "CUBE" (check the view properties for that). No measure is necessary in case the data category is set to blank. But than you cannot consume the view in reporting tools, but the view can be used as e.g. reuse view in other views.

    But the main error in your case is how you call the procedure. You say VAR_OUT = CALL ... . That is not possible because your procedure has no "returning" parameter (which is also not possible) but four output parameters. The scripted calculation view has exactly one result parameter VAR_OUT. For that parameter you have to define the result columns (if not already done). You can call your procedure but you have to supply table variables for the four output parameters. But you cannot return the four output parameter of the procedures via the view. The view must have exactly one result. Either you can determine one result from your table variables then or you have to re-think your logic.

    Best Regards,

    Florian

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2015 at 04:45 AM

    Hi Florian,

    Thanks for the clarification.

    1. So, we can call a procedure in a view if it has only one output parameter and we cannot create a view with multiple output parameters... Correct?


    2. As per my requirement, based on the input parameter IN_FLAG, I need to populate 3 different tables data. So, I cannot change my logic .. Is it possible to call a procedure directly in a ODATA service? If possible could you please let me know how to call(i.e, syntax) my procedure which has 4 in/out parameters within a ODATA Service.?

    Thanks in advance,

    Arun.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Arun.

      1) Your scripted calculation view can just have one result parameter. Think about the situation in which you make a query (SELECT) on the view. How the query should handle several result sets? That is not possible.

      Within the scripted calc. view you can call procedures which have several output parameters. But the output parameters than have more the character of an intermediate result before getting the final result returned by the view (which can only be one).

      2) For a GET operation of an OData service you cannot handle the call of a procedure with several output parameters directly or via a scripted view, as there must be one result set. Not knowing your case, maybe it is an option for you to define an OData service with several entity sets. Each entity set refers to a scripted calculation view and each scripted calculation view returns one of your result parameters.

      Best Regards,

      Florian

  • author's profile photo Former Member
    Former Member
    Posted on Dec 02, 2015 at 03:28 PM

    Hi Florian,

    Thanks a lot! for your help ..

    I had few authorisation issues I guess, just wanted to share the blog for others reference

    which helped me to resolve my issue ..

    Introduction to HANA XS application development (Part 5): BlogProject exposing data with OData services and server-side JavaScript, and performing CRUD operations from ui

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.