AMDP procedures

Veröffentlicht am 24. Januar 2022 von

Jörg Brandeis

With AMDP procedures, ABAP can easily and elegantly outsource more complex requirements in the SAP HANA database. An excerpt from the 2nd edition of my book "SQLScript for SAP HANA", published by Rheinwerkverlag, SAP Press.

AMDP procedures

From the perspective of an ABAP developer, an AMDP procedure is an instance method of an ABAP class implemented in the SQLScript programming language. This trick solves several problems:

• The SQL procedure is called as a method call of an ABAP class. Thus, the procedure call is perfectly integrated into ABAP.
• The database procedure is transported via the ABAP class. This means that there are not several transport mechanisms involved that have to be kept synchronized.
• It is not necessary for the developer to have direct access to the SAP HANA database with the appropriate authorizations while devoloping. It is sufficient to work with the ADTs with the usual ABAP developer authorizations.

In the following sections, the terms AMDP and AMDP procedure are used interchangeably. If it is not explicitly mentioned, the information does not refer to AMDP functions.

Creating AMDP procedures

IF_AMDP_MARKER_HDB

An AMDP method can only be created in a global class that implements the IF_AMDP_MARKER_HDB marker interface. The interface itself has no methods, it is only used to mark the AMDP classes.

An AMDP class can include both normal ABAP methods and AMDP methods. Whether a method is implemented as AMDP is not specified in the declaration of the method. The following listing shows an example.

CLASS zcl_amdp_demo DEFINITION
  PUBLIC
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    TYPES gty_tt_countries TYPE TABLE OF t005t.
    METHODS get_countries
      IMPORTING
        VALUE(iv_langu)   TYPE  langu
      CHANGING
        VALUE(ct_country) TYPE  gty_tt_countries 
        VALUE(cv_subrc)   TYPE sy-subrc.
ENDCLASS.

CLASS zcl_amdp_demo IMPLEMENTATION.
  METHOD get_countries
     BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
     USING t005t.

    ct_country = select * 
                 FROM t005t
                 WHERE spras = :iv_langu;
                 
    SELECT CASE 
              WHEN COUNT(*) > 0 
              THEN 0
              ELSE 4 
              END AS subrc
          INTO cv_subrc
          FROM :ct_country;
  ENDMETHOD.
ENDCLASS.

Example of a simple AMDP method

Method signature restrictions

An SQLScript procedure is later generated from the source code of an AMDP method. However, these database procedures are more restrictive in terms of parameters than is the case with ABAP methods. Accordingly, these restrictions must of course also apply to AMDP methods;

• First of all, all parameters must be fully typed. Generic data types such as TYPE TABLE or REF TO DATA are not allowed.
• Since SQLScript does not know any structures, only scalar data types and table types may be used as parameters. The table types in turn may only use scalar data types in their row structure.
• All parameters must be passed as value parameters (call by value); the use of reference parameters (call by reference) is not possible. This is obvious considering the fact that the application server runs on a different system than the database. Accordingly, there is no common memory area referencing both systems.
• Only IMPORTING-, EXPORTING- and CHANGING-parameters are allowed in AMDP procedures. It is not possible to use RETURNING parameters.
• Only AMDP exception classes can be declared in the method signature. These are the sub-classes of CX_AMDP_ERROR. When declared, the AMDP method caller can handle these exceptions. However, if they are not declared, these errors will result in a dump.

Implementation of an AMDP procedure

The language in which AMDP procedures are developed is SQLScript. This is communicated to the system by the addition BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.

OPTIONS READ-ONLY

If an AMDP implementation accesses data in read-only mode, the addition OPTIONS READ-ONLY can optionally be specified.

USING

The optional addition USING can be used to tell an AMDP implementation to use the corresponding tables, views, AMDP functions or AMDP procedures from the standard database schema of the NetWeaver system. This allows you to use this name without always explicitly specifying the respective database schema in the SQLScript source code. After the keyword USING, the enumaration of the objects is showing up only separated by whitespace (blank). It should be noted that this is still ABAP code. If, for example, you specify a table generated by BW from the namespace /BIC/ or /BI0/, this is done without quotation marks, e.g.:

...USING /BI0/PPLANT.

However, when you access this table using SQLScript code, you must use the special notation because the slash is not allowed in the simple notation:

SELECT ... FROM "/BI0/PPLANT".

USING for AMDP objects

The use of AMDP procedures and functions is declared in the USING clause using the name of the associated ABAP method with the notation KLASSE=>METHODE. Listing 1.2 shows an example of the keywords in the METHOD clause of the method implementation. Everything after the dot in line 5 up to the keyword ENDMETHOD is interpreted as SQLScript.

  METHOD get_countries
     BY DATABASE PROCEDURE FOR HDB 
     LANGUAGE SQLSCRIPT
     OPTIONS READ-ONLY
    USING t005t.

    <SQLScript-Code>
  ENDMETHOD.

Key words used in the implementation of an AMDP method

Note: A general concept for a specific database

Basically, all statements and declarations related to AMDP are designed in such a way that AMDPs can be implemented on different database systems and in different languages. Accordingly, when implementing AMDP methods, there are also the mandatory FOR <Datenbanksystem> and LANGUAGE <Abfragesprache>.

However, until today (release SAP NetWeaver 7.51), implementations are only possible for the SAP HANA database using the SQLScript language. The L language may only be used internally in SAP.

Looking at other database systems under SAP NetWeaver, we can see that each of the comes along with their own programming languages, such as Oracle's PL/SQL language. However, these cannot be used to implement AMDPs.