Skip to Content

using table type in hdbprocedure in XS Advanced

hi,

i tried different versions now to use a table type in a procedure in an XSA project on HANA2 and somehow i always get error messages like:

HDI: 8201003 Database error 411: : invalid table type: Cannot find table type OPTNET_HDI_DB_1.OPTNET.db.cds::calculation.results.TT_R_MODEL_IN: line 2 col 13 (at pos 58)

The table type is defined in a CDS-file as

type TT_R_MODEL_IN {
CALC_GROUP : calculation.CALC_GROUP;
AGE : Decimal(34,0);
RATE : DecimalFloat;
LENGTH_CUMULATED : Decimal(18,2);
};

i am quite sure i am using the namespace and context right as there is also an entity in this context and i can access this.

the procedure has been stripped of everything and now loooks like:

PROCEDURE "OPTNET.db.prc::r_model" ( 
IN dataset integer,
OUT results "OPTNET.db.cds::calculation.results.TT_R_MODEL_OUT"
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
AS
BEGIN

END

i did have success using the tabletype in a .hdtabletype file. but in the documentation a CDS file is used.. and i did expect .hdtabletype is deprecated?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 10 at 07:28 PM

    Types in CDS no longer create an actual Table Type in the DB. They only create a psudo type which can only be used from within CDS. If you also want to generate the DB Table Type you need to use the syntax TABLE TYPE not just TYPE. This gives you the control if you want to actually have a TABLE TYPE generated or not. See examples here:

    https://github.com/SAP/com.sap.openSAP.hana5.example/blob/hana2_sps03/core_db/src/data/Procedures.hdbcds

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 16 at 04:18 AM
    -1

    I am assuming that your XSA project is OPTNET and hdbmodule is db and then you stored the stored procedure in a folder prc .

    which is why your sp is PROCEDURE"OPTNET.db.prc::r_model"

    so similarly you have to qualify your table type as

    type "OPTNET.db.prc::TT_R_MODEL_IN" {
    CALC_GROUP : calculation.CALC_GROUP;
    AGE : Decimal(34,0);
    RATE : DecimalFloat;
    LENGTH_CUMULATED : Decimal(18,2);};

    Note : i am assuming that you have placed your .hdbtabletype file in the prc folder too

    Add comment
    10|10000 characters needed characters exceeded

    • got it , and I dont have the answer , but I am facing another problem , I have defined a stored procedure with table type in the classical schema , and have given execute permission to the role / created service etc . but I cant seem to use the sp as a synonym in the hdi container . have you done this scenario by any chance