Get Example source ABAP code based on a different SAP table
VX_EXA_ONLY
SAP HANA, Call Database Procedure This example demonstrates how a database procedure in the SAP HANA database can be called.
ABAP_SOURCE_CODE DEMO CL_DEMO_CALL_HANA_DB_PROC
ABAP_DESCRIPTION Take a database procedure called /1BCAMDP/DEMO_ADBC_GET_FLIGHTS in the ABAP database schema: begin FLIGHTS = select * from SFLIGHT as S where exists ( select MANDT, CARRID, CONNID from :CONNECTIONS as C where C.MANDT = S.MANDT and C.CARRID = S.CARRID and C.CONNID = S.CONNID ); end The procedure has a tabular input parameter CONNECTIONS with the three-column HANA table type DEMO_ADBC_CONNECTIONS_TYPE and an output parameter FLIGHTS with the type SFLIGHT defined in the ABAP Dictionary. The HANA table type has three character-like columns, MANDT with length 3, CARRID with length 3, and CONNID with length 4. The task is to call this procedure from ABAP while passing an internal table to the input parameter and copying the output parameter to an internal table. To do this, the example class calls various methods of the class CL_DEMO_CALL_HANA_DB_PROCEDURE. The table to be passed connection_tab is filled in the instance constructor of the class in accordance with user input. Furthermore, the database procedure, the HANA table type, and a database procedure proxy , which is usually static, are created here temporarily.
Comparison Implementation Using AB_SQL The method OSQL shows how the implementation of the database procedure can also be expressed in AB_SQL . If the function is required only in ABAP, an implementation in SQLScript is unnecessary in this case and should be avoided in accordance with the relevant programming guideline. In our example, the database procedure is a replacement for a globally available procedure that is also to be called in ABAP. The result of the method OSQL is compared with the results of the various procedure calls to determine whether they are correct. METH CL_DEMO_CALL_HANA_DB_PROCEDURE=>OSQL
Call with Native SQL Using ADBC The method ADBC uses Native SQL and ADBC to call the database procedure. To pass the internal table connection_tab to the database procedure, a temporary table DEMO_ADBC_CONNECTIONS with the required type DEMO_ADBC_CONNECTIONS_TYPE is created on the database and provided with the values of the internal table using insert. The database procedure is then called with the temporary table as an input parameter and the result is assigned to an ADBC result set. To read from the result set, a standard table flights_std is required as an auxiliary table, since the return value flights is a sorted table that cannot be used for ADBC. The temporary table DEMO_ADBC_CONNECTIONS is removed again after the database procedure is called. METH CL_DEMO_CALL_HANA_DB_PROCEDURE=>ADBC
Call Using Database Procedure Proxy The method CDBP calls the database procedure with the statement CALL DATABASE PROCEDURE using the assigned database procedure proxy <(>DEMO_ADBC_GET_FLIGHTS_PROXY<)>. The only thing to note here is that input parameters and output parameters of the database procedure have different row types than the internal tables that are to be assigned to them. In both cases, the column CONNID has the type NVARCHAR(4) on the database, but the type n with length 4 in ABAP. For this reason, the internal table connection_tab is first assigned to a temporary table connections with the correct row type before the procedure is called. No auxiliary table is required for the result flights, since the row type is described using an ABAP Dictionary type. An appropriate mapping can be performed for this type using the mapping table params. As an alternative to an auxiliary table for connection_tab, its data type could also be declared in the ABAP Dictionary and an appropriate mapping performed. The statement CALL DATABASE PROCEDURE is not possible on a SAP HANA Cloud database. METH CL_DEMO_CALL_HANA_DB_PROCEDURE=>CDBP
Call Using AMDP The method AMDP calls the AMDP method AMDP_METH, in which a call of the database procedure is implemented in SQLScript. In this case, all type-dependent actions required are performed by the AMDP framework. METH CL_DEMO_CALL_HANA_DB_PROCEDURE=>AMDP METH CL_DEMO_CALL_HANA_DB_PROCEDURE=>AMDP_METH
