Skip to content

ABAP Keyword Documentation →  ABAP Programming Guidelines →  Architecture →  Data Storage 

Database Accesses

Other versions: 7.31 | 7.40 | 7.54

Background

In ABAP, data in database tables can be accessed in the following ways:

  • Open SQL
    Implemented by ABAP statements, this is a subset of the Structured Query Language (SQL) comprising the DML (Data Manipulation Language) part. The statements of Open SQL use the Open SQL interface to access, independently of the platform, the database tables of the database of an AS ABAP that are defined in the ABAP Dictionary.
  • Native SQL
    Database-specific SQL statements that include both DML and DDL (Data Definition Language) statements and which can be passed to the Native SQL interface of the database as follows:
  • The methods of ADBC make it possible to execute dynamic SQL statements on a database system and process the results. ADBC (ABAP Database Connectivity), a class-based API that enables object-oriented access to the Native SQL interface.
  • The AMDP framework is used to manage and call ABAP Managed Database Procedures. These are stored procedures or database procedures implemented as AMDP procedures in an AMDP method of an AMDP class and replicated to the database system from here.
  • Native SQL statements can be specified in ABAP programs between the statements EXEC SQL and ENDEXEC. Static Native SQL statements of this kind are not checked completely by the syntax check and are forwarded almost unchanged from the Native SQL interface to the database of an AS ABAP.

All access types (with the exception of AMDP) also enable access to other databases using additional connections (except for access to the central database of an AS ABAP (standard connection)).


Note

For calls of database procedures of an SAP HANA database, there is also the statement CALL DATABASE PROCEDURE, which is particularly well suited to access using a secondary database connection.

Rule

Using Open SQL

Use Open SQL for general persistence services where possible. Only use Native SQL for tasks where Open SQL is not suitable.

Details

Only Open SQL is guaranteed to be independent of the database platform used. For this reason, Open SQL does not contain the set of all possible SQL statements in a specific database, but only a subset of the DML scope of all database systems supported by AS ABAP. The database tables that can be processed using Open SQL can be used in ABAP directly as structured types for the declaration of suitable work areas. Only Open SQL supports SAP buffering of table content in the shared memory.

Native SQL should only be used if the task really cannot be solved using Open SQL. Services that work with Native SQL are generally dependent on the database system used, so that they cannot be executed in all AS ABAP systems. For platform-independent services, implementations should be provided for all supported databases.

If the database is accessed using the Native SQL interface instead of the Open SQL interface, ADBC or AMDP should be used.

  • ADBC is a modern object-oriented API that is better suited to modern ABAP programming than EXEC SQL. Enhancements to the Native SQL interface, such as bulk access using internal tables, are now only provided using ADBC. ADBC also enables dynamic access; Native SQL on the other hand is just static.
  • AMDP, currently only available for the central SAP HANA database of an AS ABAP, is recommended for all tasks that swap out code from ABAP programs to this SAP HANA database for performance reasons.


Note

The rule dictating that Open SQL is to be used for as long as possible applies in particular to AMDP too. It is not a good idea to swap out SQL statements to database procedures if these could be implemented using Open SQL or ABAP CDS too. In this case, no performance gains can be expected since the Open SQL statements are updated to Native SQL by the database interface in exactly the same way as they would be written in the database procedure. Using AMDP is a good idea only if HANA-specific properties can be exploited by procedure calls or if repeated transports of large amounts of data between the database and the application server can be bypassed.

Bad example

See AMDP, Comparison with Open SQL. A database access not programmed well using Open SQL can often be optimized by improved use of Open SQL, making it unnecessary to use AMDP in these cases.

Good example

See Currency Conversion with SQLScript. In this case, a specific operator of the language SQLScript is used that is not usually available.


This translation does not reflect the current version of the documentation.