Stored Procedure need an Array as output

[draschke]

Hi Experts,

I need to use a procedure, with an array as output.

How would you do this?

PROCEDURE "TBASE_PUBLIC"."development...procedures::procGetLabordateForPatient"  (

    IN patientID integer,

    out rs_labordatum "TBASE_PUBLIC"."development...data::TBASE_TT.Procedures.ttLabordate"

)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  --DEFAULT SCHEMA <default_schema_name>

  READS SQL DATA AS

BEGIN

    rs_labordatum =

        SELECT 

            L."PatientID",

            L."Datum"

        FROM "TBASE_PUBLIC"."development...data::TBASE.cds.Labor" L

        WHERE

                L."PatientID" = :patientID

        ORDER BY L."Datum" DESC

    ;

END

[pfefferf]

Hello Dirk,

what is the question here? An output parameter can have a primitive or table typed parameter. Type "array" (as you can use it in the SQLScript logic) cannot be used for out parameters. Arrays have to be unnested in tables.

But what I can see from your procedure logic you are already returning a table typed parameter. Is that not sufficient for your?

Regards,

Florian

[draschke]

It doesn't seem to be sufficient, but I'm not sure.

I've a problem to get this data displayed in an sapui5 control.

The object has to be an array and that's why I'm asking, if there is an possibility to get an array-object.

[pfefferf]

That is not an issue of the procedure. How do you expose the data?

[draschke]

In this case we use hana xsjs, which we use as an wrapper to get the data from the procedure. Its our first time, where we try to use xsjs.

And because I read something about array_agg, I thought we could do it in the procedure.

[pfefferf]

In the XSJS you are responsible how your response is returned to be able to consume it in an JSON Model. Why are you not using an XSODATA service? What you are doing within your procedure and with your XSJS can be done in a more efficient way.

[draschke]

Okay, I've to do it by myself.

We have an application with a lot of tables.

A lot of the display logic we are doing with odata.

OData is a little bit magic in our opinion, its not so tangible. Sometimes it's so easy that we want believe it.

We have a lot of requirements where we're not sure, how to do it with odata at this moment.

This is why we try to find out what else we can do with xsjs.

With release SPS12 we want to move to xs advanced and we thought it would be better to use more native javascript, so we can cut and paste a lot of things. (so we can use the real node.js power)

If you think different, let me know!

BR

Dirk