Skip to Content
0

using table type in hdbprocedure in XS Advanced

Mar 15 at 10:10 PM

68

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Thomas Jung
Apr 10 at 07:28 PM
1

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

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

thanks,

that was exactly the Information i was missing!

0
Rajarshi Muhuri 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

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

good morning,

sorry, but this is not the answer. the code you see is part of a .cds-file as the syntax shows. the cds file is well placed and has the context definition. as stated, i am able to use an entity which is in the same context.

using hdbtabletype i could actually create the table type and use it in a procedure. but there i do not know how to handle the CALC_GROUP which is also a type definition which is used in some table types and some entities.

0

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

0