Most ABAP developers have come across the need for native database access. For instance, they might need database features that are not in the scope of Open SQL or might want to optimize code for a particular database. Now with ABAP Managed Database Procedures (AMDP) they have the ability to push down more complex application logic to the database. In contrast to earlier solutions this can be done in a well-structured and concise way. In this article we introduce AMDP using the standard ABAP development environment. We explain the syntactical embedding and the interaction between the ABAP application server and the database engine, which helps developers to understand why and when certain syntax or run-time errors arise. We troubleshoot database procedures with familiar ABAP tools, such as ABAP Debugger and ABAP Runtime Error Analysis, and outline enhancement capabilities and the integration into the Business Add-In (BAdI) framework. First though, we contrast the previous options with the new functionality offered by AMDP.

Past and Present

We briefly recall the previously existing options for native database access from ABAP.

EXEC SQL

Since the “prehistoric” times of ABAP—or what many of today’s developers might call prehistoric—EXEC SQL has existed as a way of inserting a block of native database source code into ABAP. An EXEC SQL block is just a container for any database language with source code inside that hopefully is recognized and correctly executed by the underlying database. More or less ignored by the ABAP compiler, these code snippets are transported through the system landscape together with the ABAP code. At execution time they are sent to the database. This very basic approach does not offer much convenience with regards to things such as syntax checks or run-time error analysis. For a decent syntax check, developers need an additional development environment for the specific database. Otherwise, they have to use the good old trial-and-error method. In case of run-time errors they end up searching for SQL error messages.

ABAP Database Connectivity (ADBC)

Introduced as an alternative to EXEC SQL, ADBC is a completely dynamic approach. It is a class-based framework that offers re-use and modularization of database source code. Errors are reported as exceptions and are easier to handle. Data exchange between the Application Server ABAP (AS ABAP) and the database is more versatile than in EXEC SQL, where variable binding was restricted to flat structures and elementary fields. While ADBC is fast and handy for single statements, it becomes a bit clumsy for longer source code sections, which will surely be involved if applications push some of their code to the database. It still does not provide a syntax check for the embedded source code, which is essential for code quality.

CALL DATABASE PROCEDURE

To overcome the shortcomings of EXEC SQL and ABDC, in particular missing static checks, the CALL DATABASE PROCEDURE statement operates on source code that is not managed inside AS ABAP but by the (SAP HANA) database itself.  The connection between SAP HANA and the ABAP repository is established via a procedure proxy. As a result you trade the straightforward life cycle management for a better syntax check. Developers and administrators have to deal with the synchronization of ABAP and SAP HANA transports and set up the users for both ABAP and database development.

Can’t You Do Better?

Yes, you can do better. AMDP goes back to the concept of native database source code embedded into ABAP code as known from EXEC SQL, but this time with all the amenities taken for granted in ABAP, such as syntax highlighting, a decent syntax check, code completion, and source code navigation. Most importantly, the life cycle is handled in AS ABAP. This has turned out to be essential. The basic idea of AMDP is to use dedicated ABAP methods as containers for database source code. This means that the method implementation has syntax additions specifying the database system and language of the source code. At run time, developers expect the source code to be available and executable in the database without further ado. They need meaningful error messages if a call fails. Also, while you’re at it, you can add things such as debugging support and extensibility to your wish list. Let’s now have a look at how AMDP meet these requirements.

Writing an AMDP Method

In the following sections, we go through the life cycle of AMDP methods starting with our first implementation example, as shown in Figure 1. This method does not contain any ABAP, but the lines between METHOD and ENDMETHOD are native database source code. Unlike EXEC SQL, the method statement is enriched with syntax additions specifying the database (in this case HDB, i.e., SAP HANA) and the database language (here SQLSCRIPT). This is the basis for syntax highlighting and a detailed syntax check. The AMDP infrastructure is prepared to support different database platforms and different database languages. With the current focus on SAP HANA, today’s implementation is SAP HANA only, with SQLScript as the database language. Figure 2 shows the definition part of an AMDP class. At the top of the class definition you see an interface called IF_AMDP_MARKER_HDB. This interface is mandatory and labels the class as a class that can contain AMDP methods (for HDB). Such a class is called an AMDP class. It may contain ABAP methods, too. The interface helps you to easily find all AMDP classes in a system. The signature of an AMDP method does not differ from an ABAP method. In fact, at first glance a reader wouldn’t even know that a method is implemented as a database method. However, there are certain restrictions regarding the method parameters, as you will learn later. AMDP methods can occur in all flavors, as static or instance methods, and as public or private methods. The decision about what kind of method is used is a question of software design or a developer’s personal preference.

Data Transfer between ABAP and Database via AMDP Methods

As AMDP methods are embedded into ABAP applications they are surely meant to interact with ABAP data. On the other hand, database procedures operate on tables, views, or database entities in general. Inside the method body, developers need to be able to address these entities.

Parameters

AMDP methods can have EXPORTING, IMPORTING, and CHANGING parameters, but no RETURNING parameters. The latter can only be used in AMDP database functions, which are a variant of AMDP methods that we discuss later. As you’ve seen in the previous code snippet (i.e., Figure 2), the signature of an AMDP method looks like the signature of an ABAP method with parameters based on ABAP data types. Although the body is implemented in another language with a different type system, developers do not need to worry about the error-prone data conversion between the languages. The AMDP framework ensures the correct mapping to the corresponding database types. AMDP supports elementary ABAP types and internal table types with flat line types, which simplifies the parameter handling that has always been a bit cumbersome in EXEC SQL or ADBC. Internal tables must have flat line types because, for deep structures, no counterpart can be created on the database. Importing parameters may have default values that can be specified in the method signature. Here, too, AMDP converts the default values according to the database’s type system. One restriction regarding AMDP method parameters is the mandatory call by value. This is obvious as calling an AMDP method means leaving the ABAP stack. Similar to a Remote Function Call (RFC), the called side (here the database) has no access to ABAP data.

Using ABAP Data Dictionary Entities

Inside the body of an AMDP method, developers might want to refer to other database entities: tables and objects in the default database schema (referred to as SAPSID schema in the following) and tables in other schemas not owned by the current AS ABAP. Most database objects in the SAPSID schema are managed by the ABAP Data Dictionary. Thus, the AMDP infrastructure is able to check their existence, their table structures, and versions. It can detect possible syntax errors at a very early stage of the syntax check and even on any database type. You can take advantage of this knowledge and provide the best possible syntax check messages as well as source code navigation. Therefore, an AMDP method has a USING clause that lists those tables, views, or database objects that are used inside the method and reside in the SAPSID schema (Figure 3). A double-click on any object in the USING list navigates to the data definition (e.g., transaction SE11). Database objects of schemas other than the SAPSID schema are not managed in the ABAP Data Dictionary, so AMDP has to rely on their existence and correctness. They do not need to be specified in the USING clause, but have to be addressed with a schema prefix. Finally, AMDP methods may call other AMDP methods (Figure 4). As AMDP methods are themselves ABAP-managed entities they have to be listed in the USING clause by their full name.

Syntax Check

The AMDP infrastructure has a strong focus on meaningful syntax errors for AMDP methods. They are presented in the same way as ABAP syntax errors with a clearly marked source code position and long text information. This integration into the ABAP syntax check is one of the major benefits compared to previous solutions. Figure 5 shows the single steps of the AMDP syntax check in a schematic way.

General Syntax Checks

Some checks can be handled directly in the ABAP compiler and are independent of the underlying database system. These are errors in a method’s signature or in the syntax additions of the method statement (Figure 6). In other words, these are errors outside the native database source code—for example, wrong method parameters or missing objects in the using list. Figure 6 shows a typical error. Call-by-reference is used instead of call-by-value for an AMDP method parameter. Luckily, developers will be able to erase most of these errors very quickly by following the instructions of the short and long text.

Database-Specific Syntax Checks

Most syntax errors in AMDP methods surely occur in the native database source code. Detecting these errors is not possible for the ABAP compiler. It only understands ABAP and its sub-dialects. Therefore, this task is transferred to the database. For this reason, the AMDP framework tries to create a database procedure from the AMDP method body during the syntax check. If the creation fails, it wraps the database-specific error message into an ABAP syntax error including the correct determination of the error position. The error message itself remains more or less unchanged as ABAP cannot interpret it. The full error information can be seen in the view ABAP Problem Description (also known as long text). If a long text is available this view can be accessed via right-clicking the error message. The long text for the above syntax error is shown in Figure 7.

AMDP Class Activation

Once an AMDP method is syntactically correct, the AMDP class can be activated and there is nothing more for the ABAP developer to do. The AMDP framework manages all the work of transferring an AMDP method body into an executable database procedure. First, the classical ABAP load is created, which includes the AMDP method signature and the empty method container. This is done on any database. For the database specified in the method implementation, AMDP creates additional database artifacts before the method can really run:

• Parameter (table) types for data transfer
• Special views for dictionary tables to ensure a well-defined field order
• A procedure stub with the ABAP-load-version timestamp inside that links the database procedure with the general ABAP-version management
• The actual database procedure after replacing the ABAP parameters and objects in the USING list with their database equivalents

The report RSDBGEN_AMDP gives an overview of all database objects created in the context of an AMDP class, and also shows the counterparts of the AMDP methods that are the actual database procedures.

Executing an AMDP Method

In an earlier section you saw the signature of an AMDP method. It looks more or less like an ABAP method. It is therefore hardly surprising that executing an AMDP method is just a simple method call, written in ABAP with ABAP parameters. While a syntax check (for a correct method) and activation do not raise any errors on databases that the AMDP method is not designed for, there is no mercy at run time. Executing an AMDP method on the wrong database system has to fail and creates a run-time error. Figure 8 shows an AMDP method call protected by a check for the correct database system. Similar to the ABAP compiler, there is an AMDP extension in the ABAP run time. It manages the transition from the ABAP stack to the database stack as follows:

• It binds the method parameters to the actual database procedure parameters
• It copies the content of ABAP table parameters into the database
• It updates the version of the database procedure if the AMDP source code has changed
• It raises errors that cannot be detected at compile time (e.g., cyclic calls between AMDP classes and faulty or missing objects outside the SAPSID schema)
• It calls the actual database procedure
• It returns database results back into ABAP tables

In a perfect world, this would be the end of the AMDP story. However, as you all know, software errors are unavoidable, and just like ABAP methods, AMDP methods may fail.

Run-Time Errors

Most software developers are not really fond of run-time errors in their ABAP applications, but strange SQL error messages as the result of failing AMDP method calls are even more annoying. Let’s see how the AMDP framework helps here.

ABAP Run-time Errors

Just like syntax errors, AMDP run-time errors may arise in the part controlled by the ABAP compiler—that is, everything outside the method body. In this case, transaction ST22 shows a run-time error for the AMDP method call with all the information that developers are used to (Figure 9).

Database Run-Time Errors

Most run-time errors, however, occur deep down inside the database stack, perhaps with several nested AMDP methods involved. This is a real challenge for good old ST22 as it has to pin down the source of the error inside an environment—the database—that it does not control itself. For AMDP methods, we added a new section called Database Procedure (AMDP) to the sections in ST22, shown in Figure 10. It contains the complete error information sent by the underlying database in an easily readable format. It comes with a stack of involved database procedures, error positioning, and navigation into the AMDP source code. The AMDP_EXECUTION_FAILED run-time error in our example is an error that has its origin in the native database source code (here a negative value was passed for IM_NUM_ENTRIES). However, there are further sources for run-time errors.

AMDP Run-time Errors

As stated earlier, the AMDP framework executes certain checks at run time and can therefore raise run-time errors, too. They may result from the failed attempt of creating certain database artifacts needed for successful method execution, cyclic calls, or connection errors.

Programming Errors and Infrastructure Errors

Regardless of whether run-time errors are triggered by ABAP, AMDP, or the database, there are two different error categories, which are visible in the header of ST22. Classical programming errors such as AMDP_EXECUTION_FAILED and temporary inconsistencies, similar to LOAD_CLASS_VERSION_MISMATCH, can be solved by re-generation of the AMDP class in many cases.

Exceptions

Most AMDP-specific run-time errors can be caught as exceptions by enhancing the method signature. Developers can either catch errors of the superclass CX_AMDP_ERROR or one of its sub-classes, depending on the design of their application. Catching AMDP_EXECUTION_FAILED errors, raised by the native database source code, is useful indeed. The author of an AMDP method might have raised a native exception or signal intentionally, expecting the ABAP application to react to that error. Catching other errors, raised by the AMDP framework, might make sense or not, depending on what the calling ABAP application is supposed to do in the case of an error.

Debugging Support

The AMDP section of ST22 helps to find obvious AMDP_EXECUTION_FAILED programming errors. Catching exceptions prevents ABAP applications from run-time errors caused by failing AMDPs methods. But what about the nasty, hidden errors—for example, wrong or missing results from an AMDP method? Developers certainly need a debugger for more complex and nested AMDP methods, and as these methods are ABAP managed after all, it would be nice if the ABAP debugger could handle the AMDP stack as well. Again, we re-used the familiar ABAP tools and integrated the (SAP HANA) database debugger into the user interface of the ABAP debugger. Thus, ABAP developers can debug the complete stack of their applications, including AMDP methods, with one user and one tool. In the ABAP editor, line breakpoints can be set on any executable line of an AMDP method. They appear on the left side of the source code as shown in Figure 11. An ABAP application that uses this AMDP method automatically stops at the AMDP breakpoints. The display of variables shows the native database variables of an AMDP method, their current values, and their native data types (Figure 12). A double-click on LT_MY_FLIGHTS navigates to the table content display shown in Figure 13, which is one of the most important tools for tracing the correctness of data inside a database procedure. Note that this is not the content of an ABAP internal table, but the content of a locally declared table in the database.

Restrictions of AMDP

The functional scope of AMDP debugging depends on the debugger features of the underlying database. AMDP supports basic features such as line breakpoints, step-over (known as F6 in the ABAP debugger), and display of variables as shown above. The best way to debug through AMDP source code is setting breakpoints in all AMDP methods of interest. Inside an AMDP method, the step-over functionality (F6) is available, but there is currently no step-in functionality (i.e., support of F5). The AMDP debugger does not handle non-AMDP procedures or procedures that reside in schemas different from SAPSID. Setting a breakpoint inside an AMDP method makes the database create a debug mode version of the corresponding database procedure, a prerequisite for debugging. This is done for AMDP methods only as they are our main focus. Note that the debug version of a database procedure may slightly differ from the non-debug version.

Starting and Stopping the AMDP Debugger

The AMDP debugger starts automatically when the first AMDP breakpoint is set. During a debugging session it uses two application server work processes for database communication. To make sure that application server resources are not permanently blocked, the AMDP debugger automatically stops after 10 minutes of inactivity. The breakpoint color is no longer green but gray. Developers can restart the AMDP debugger manually in the context menu of the Debug view. The Debug view is a standard view of the ABAP debugger perspective in ADT and contains the execution stack (Figure 14).

More About AMDP

In this section we discuss some supplementary topics. They are not essential for understanding AMDP, but address more special features developers might find helpful.

ABAP Managed Database Functions

AMDP database functions are a variant of AMDP. They differ only in the method interface and always have a returning parameter rather than exporting and changing parameters. The database counterpart is not a database procedure but a database function. The result of a database function is always consumed by a SQL select statement. For this reason there are certain restrictions. AMDP database functions must be read only and must not have any exceptions. For the time being, you can only use AMDP database functions inside other AMDP procedures, but you cannot call them in ABAP directly. In SAP NetWeaver 7.50 AMDP database functions are also used in the context of Core Data Services (CDS) namely to implement CDS table functions.

Using Self-Defined Objects

So far we have assumed that database entities used in an AMDP method body are either managed by ABAP and the Data Dictionary, or they reside outside of the SAPSID schema. There are situations, though, in which developers wish to work with self-managed tables that are created in the SAPSID schema, but not known to the Data Dictionary. They might not even exist at AMDP syntax check time. Actually, objects that are not managed by AMDP need to have the schema name as a prefix, which is not allowed for the SAPSID schema as it would circumvent the AMDP life cycle management. Therefore, there is a reserved, predefined name space /1BCAMDP/ for the creation of these objects. Typically, developers create these objects in the class constructor of the calling AMDP class to make sure they are available at run time. The AMDP syntax check only raises warnings instead of syntax errors once the namespace has been used in the AMDP method body. Developers should be well aware that, in this case, errors that normally would have been detected by the syntax check are delayed and arise at execution time.

Client, Connection, and Transaction Handling

Automatic client handling is one of the most important features of Open SQL and ABAP CDS. It is done in the database interface layer of the ABAP server. The database itself is agnostic to the ABAP client concept. For AMDP methods, developers need to handle client fields of ABAP database tables explicitly in the native AMDP source code if parts of an ABAP application are pushed down to the database via AMDP. Unless specified otherwise, AMDP methods use the standard database connection of the ABAP server. They may use a secondary service connection. In that case, they have to be equipped with a special importing parameter called CONNECTION. Unlike regular parameters, this one cannot be addressed inside the method body, but just contains the name of the connection to be used at run time by the AMDP framework. Currently the connection of an AMDP method has to be a service connection to the ABAP server’s database as AMDP’s syntax and life cycle checks rely on objects of the default database. Finally, implementing source code in a native database language gives developers the freedom to use features beyond the scope of Open SQL, but at the same time they are outside of ABAP’s Logical Unit of Work (LUW) handling. For this reason, AMDP does not allow transactional statements and Data Definition Language (DDL) statements inside an AMDP method body. If violations can be found by static checks a syntax error is raised. The checks depend on the underlying database, though. SQLScript forbids DDL statements in database procedures, and those procedures that contain transactional statements are labelled and can be detected by the AMDP framework.

Extensibility: AMDP Business Add-Ins (BAdIs)

The BAdI extensibility framework supports the use of AMDP methods in several ways. When called in an ABAP context, AMDP methods behave like ABAP methods. So it is understood that a regular BAdI may contain methods implemented as ABAP or as AMDP methods. The implementation language of a method leaves the general BAdI handling unchanged. The CALL BADI statement executes all methods that implement the BAdI interface and are currently switched on. In addition to this straightforward scenario, developers can define so-called AMDP BAdIs (Figure 15). These AMDP BAdIs can be referred to inside other AMDP methods by their BAdI names and thus enable the ABAP BAdI concept for database procedures. The BAdI call displayed here can dispatch one or more methods, but all of them must be AMDP methods. Apart from calling an AMDP BAdI, this example also demonstrates the use of CHANGING parameters in AMDP. The database language does not know CHANGING parameters, so you pass the ABAP CHANGING parameter twice, as an IN parameter and as an OUT parameter. AMDP BAdIs have to be labelled in transaction code SE20. Certain restrictions apply to their implementing classes:

• All implementing classes have to be AMDP classes
• A fallback AMDP implementation has to be available that is executed when no other implementation is switched on. The fallback implementation is also important for the AMDP syntax check, as a BAdI might not yet contain any active implementation at design time (Figure 16).
• All AMDP methods have to be implemented for the same database system, which is retrieved from the fallback implementation
• AMDP classes cannot have filters

At run time, AMDP automatically evaluates the set of methods that are active, taking account of the BAdI’s current switch status.

System Setup

At the very beginning of this article we brought up one of AMDP’s benefits regarding user management: ABAP developers who wish to write their own database-specific source code do not need an extra database user, but can implement AMDP methods with their ABAP users. For database access, AMDP uses the regular database user of the ABAP application server (usually named SAPSID like the default database schema). Beyond what is needed to run an ABAP application server anyway, this SAPSID user needs a set of additional permissions for running the AMDP infrastructure. This obviously includes the permission to create and execute database procedures, but also access to some database features that are needed for AMDP checks and debugging. If AMDP misses any privileges in the database, the transaction SICK provides all necessary information that is needed to set up the system correctly and to enjoy writing AMDP methods.