3.2ABAP Database Access

Having introduced the basic database architecture of AS ABAP, we’ll now describe the actual database access from ABAP. This includes both the definition of data models (tables, views, etc.) and write and read operations for data records.

The database is usually accessed via SQL. The SQL database language covers three intersecting categories, which are described in Table 3.2.

Type

Purpose

Examples

Data definition language (DDL)

Definition of data structures and operations

CREATE TABLE, DROP TABLE, CREATE VIEW

Data manipulation language (DML)

Read and write operations for data records

SELECT, INSERT, UPDATE, DELETE

Data control language (DCL)

Definition of access restrictions for database objects

GRANT, REVOKE

Table 3.2Overview of SQL

In traditional ABAP application development, DML operations are implemented via Open SQL (see Section 3.2.2), while DDL is used indirectly via the DDIC (see Section 3.2.1). DCL, on the other hand, isn’t relevant for traditional application development because the ABAP application server—as described in Figure 3.2—uses a technical user to log in to the database. In addition, the authorizations for the actual application user are checked using the ABAP authorization system (e.g., using the AUTHORITY-CHECK command). When using SAP HANA for implementing part of the application logic inside the database and accessing data outside the ABAP schema, you must also consider the authorization concepts of the database.

3.2.1ABAP Data Dictionary

Using the ABAP Data Dictionary (DDIC), you can create data models in the database. These data models can be enriched with semantic aspects such as texts, fixed values, and relationships. This metadata, which is particularly important for business scenarios, plays an important role for developments in SAP HANA because it can be used for modeling and implementation tasks in SAP HANA.

Before we describe the individual types of development objects (tables, views, etc.), let’s discuss two very important qualities of the DDIC:

The extensibility of DDIC objects is also important to consider when performing modeling and programming tasks in SAP HANA.

From a development perspective, Transaction SE11 is the main tool for using the DDIC. You use this transaction to define and maintain the following object types:

Because views plays an important role in the context of SAP HANA, the possibility to define database views via the DDIC will be explained in more detail in Section 3.2.3.

In addition to pure data structures, you can maintain more properties in the technical settings for a database table, including the following two options:

As of SAP NetWeaver 7.4, the DDIC allows you to specify whether tables should be stored in the column store or in the row store in SAP HANA (see Figure 3.4). When selecting the default value Undefined, the column store is used, which is recommended for basically all application cases. There are a few exceptions, which are described in Chapter 14 as well.

Database-Specific Settings for Tables

Figure 3.4Database-Specific Settings for Tables

You can also define database indices in the DDIC. When doing so, you can create indices only for certain databases (inclusion list) or exclude them by specifying an exclusion list. During a database migration to SAP HANA, the system first creates entries for existing secondary indices in the exclusion list, so that the corresponding index on SAP HANA isn’t created automatically. Instead, those indices should only be activated on a case-by-case basis. Technical background information and recommendations for index usage on SAP HANA can also be found in Chapter 14.

Figure 3.5 shows the index exclusion on SAP HANA for table SBOOK. In this case, the ACY and CUS indexes aren’t created on SAP HANA because HDB is on the exclusion list.

Index Exclusion for SAP HANA

Figure 3.5Index Exclusion for SAP HANA

In the past, some database versions came with severe restrictions on the maximum number of tables in the system and provided poor compression capabilities. To avoid these problems, you can create special table types in the DDIC—pool and cluster tables—where several logical tables are combined into one physical database table. These logical tables can be accessed from ABAP like normal database tables; however, there are also a number of restrictions to consider. Because pool and cluster tables aren’t needed on SAP HANA, existing tables are converted into normal transparent tables when migrating to SAP HANA. The main advantage of this conversion is that the tables can also be used for modeling and programming tasks in SAP HANA, as described in Chapter 4. During migration, pool and cluster tables are compatible with existing applications. There is no need to adapt existing ABAP code. However, certain aspects must be considered with regard to the sorting behavior, which will be explained in more detail in Chapter 14.

3.2.2Open SQL

Open SQL provides an option for database access that is integrated into the ABAP programming language. Both the supported syntax and the detailed semantics of Open SQL are database-independent, which makes it possible to write applications in ABAP without knowing the details of the underlying database system. This section provides an overview of the functional scope of classic Open SQL. The Open SQL enhancements, which are available as of ABAP 7.4, are discussed in Chapter 6, Section 6.4.

Read Access with Open SQL

SAP HANA mainly offers options to accelerate read accesses. Using Open SQL is the primary and simplest option to move data-intensive operations to SAP HANA.

In this section, we’ll use examples to detail some of the advanced options of Open SQL that you may not have used in the past. Although the syntax of the ABAP command SELECT won’t be explained in detail here, a comprehensive documentation of the Open SQL syntax can be found in the ABAP online help.

The examples deal with the following three aspects, which basically cover the advanced options for expressing calculation logic in Open SQL:

In the first example, we’ll use a join to read values from tables SCARR and SCURX (currencies). Depending on the table entries that should be included in the result, there are several options for creating joins in SQL. Open SQL supports inner joins and left outer joins. When we describe the process for modeling views in SAP HANA Studio in Chapter 4, Section 4.4, the different join variants are also explained in detail. Listing 3.4 uses the two variants that are supported in Open SQL and shows the differences between them.

REPORT zr_a4h_chapter3_open1.

TYPES: BEGIN OF result_type,
currkey TYPE s_curr,
currdec TYPE currdec,
carrname TYPE s_carrname,
END OF result_type.

DATA: wa TYPE result_type.

" Selection of all currencies and corresponding
" airlines. The inner join is used to select only
" currencies with a corresponding airline that
" uses this currency.
SELECT c~currkey c~currdec r~carrname FROM scurx AS c
INNER JOIN scarr AS r
ON c~currkey = r~currcode INTO wa.

WRITE: / wa-currkey , wa-currdec , wa-carrname.
ENDSELECT.

" Selection of all currencies and corresponding
" airlines. The outer join is used to also select
" currencies without a
" corresponding airline.
" In this case, the value is initial.
SELECT c~currkey c~currdec r~carrname FROM scurx AS c
LEFT OUTER JOIN scarr AS r
ON c~currkey = r~currcode INTO wa.

WRITE: / wa-currkey , wa-currdec , wa-carrname.
ENDSELECT.

Listing 3.4Inner Joins and Left Outer Joins in Open SQL

As already mentioned in Section 3.1.3, there is no representation of the NULL value in ABAP. As shown in Listing 3.4, where no corresponding data record is found in the “right-hand” table, a left outer join generates the value NULL for the corresponding columns of the result set in the database. In ABAP, this value is converted into the initial value of the column. Consequently, whether no value was found or whether the corresponding value happens to be the initial value can’t be determined. When executing the equivalent SQL statement via the SQL console in SAP HANA Studio, NULL values are displayed as question marks (?) as shown in Figure 3.6.

Representation of NULL Values in the SQL Console

Figure 3.6Representation of NULL Values in the SQL Console

In addition to the described inner join and left outer join, the expression FOR ALL ENTRIES provides another option in Open SQL to leverage foreign key relationships and use internal tables to create joins. This SAP-proprietary expression isn’t part of the SQL standard and is a natural enhancement of the ranges that are used in selection options. A typical example of using this expression is shown in Listing 3.5. In this example, all airlines are first read, and then the airlines that can be displayed by the user are stored in an internal table. Subsequently, the FOR ALL ENTRIES clause is used for a type of inner join with table SFLIGHT.

REPORT zr_a4h_chapter3_open2.

TYPES: BEGIN OF ty_carrid,
carrid TYPE s_carrid,
END OF ty_carrid.

DATA: ls_carrier TYPE ty_carrid,
ls_flight TYPE sflight,
lt_carrier TYPE TABLE OF ty_carrid.
SELECT carrid FROM scarr INTO ls_carrier.
" Check authorization and, if
" successful, add to internal table
AUTHORITY-CHECK OBJECT 'S_CARRID'
ID 'CARRID' FIELD ls_carrier-carrid
ID 'ACTVT' FIELD '03'.

IF sy-subrc = 0.
APPEND ls_carrier TO lt_carrier.
ENDIF.

ENDSELECT.

" Output of all flights of the airlines for which
" the user is authorized.
IF ( lt_carrier IS NOT INITIAL ).
SELECT * FROM sflight INTO ls_flight
FOR ALL ENTRIES IN lt_carrier
WHERE carrid = lt_carrier-carrid.

WRITE: / ls_flight-carrid,
ls_flight-connid, ls_flight-fldate.
ENDSELECT.
ENDIF.

Listing 3.5Join of a Database Table with an Internal Table

[ ! ]Special Properties of FOR ALL ENTRIES

For performance reasons, changing a nested SELECT statement into a FOR ALL ENTRIES expression can be useful. However, when doing so, you should pay attention to three important properties of the FOR ALL ENTRIES expression:

More information on SAP HANA is provided in Chapter 14, which also includes recommendations for optimizing ABAP programs.

In the third example, we’ll use the aggregate functions (COUNT, SUM, MIN, MAX, AVG). Using an SQL query, you can determine inconsistencies within the data model. To do so, we’ll execute a query to find out if there are more bookings for the economy class of a flight (based on the entries in table SBOOK) than occupied seats (attribute SEATSOCC in table SFLIGHT). In Listing 3.6, a join is combined directly with the calculation of a quantity (COUNT) and the limitation of the result set based on the result of the aggregation (HAVING).

REPORT zr_a4h_chapter3_open3.

TYPES: BEGIN OF ty_result,
carrid TYPE sbook-carrid,
connid TYPE sbook-connid,
fldate TYPE sbook-fldate,
count_sbook TYPE i,
count_sflight TYPE i,
END OF ty_result.
DATA ls_result TYPE ty_result.

" Determination of all flights with more
" economy class bookings (table SBOOK) than
" occupied seats (table SFLIGHT)
SELECT b~carrid b~connid b~fldate
f~seatsocc AS count_sflight
COUNT( * ) AS count_sbook
FROM sbook AS b
INNER JOIN sflight AS f ON b~carrid = f~carrid
AND b~connid = f~connid
AND b~fldate = f~fldate
INTO ls_result
WHERE b~cancelled <> 'X' AND b~class = 'Y'
GROUP BY b~carrid b~connid b~fldate f~seatsocc
HAVING COUNT( * ) > f~seatsocc
ORDER BY b~fldate b~carrid b~connid.
WRITE: / ls_result-carrid, ls_result-connid,
ls_result-fldate, ls_result-count_sbook,
ls_result-count_sflight.
ENDSELECT.

Listing 3.6Aggregate Functions in Open SQL

When using aggregations, it must always be noted that the GROUP BY expression lists all nonaggregated attributes; this also includes attributes that are used only in a HAVING clause.

As you can see, rather complex queries can be expressed via Open SQL. In the fourth example, we’ll add another element: subqueries—and, as a special case, existence checks. A subquery is a SELECT statement in parentheses that can be used as part of the WHERE clause (both in reading and writing accesses). Typical use cases are existence checks with the following structure:

SELECT ... FROM ... INTO ...
WHERE EXISTS ( SELECT ... ).

If only one column is selected in a subquery, this is referred to as a scalar subquery. In addition to simple comparisons (=, >, <) for a column, these queries support other operations as well (ALL, ANY, SOME, IN). The example in Listing 3.7 shows how subqueries can be used to implement a nested filter condition.

REPORT zr_a4h_chapter3_open4.

DATA: ls_flight TYPE sflight.

" Output of all flights from 2013 with more
" occupied seats than the average value for the
" same route in 2012
SELECT * FROM sflight AS f INTO ls_flight
WHERE fldate LIKE '2013 %' AND seatsocc >
( SELECT AVG( seatsocc ) FROM sflight
WHERE carrid = f~carrid
AND connid = f~connid
AND fldate LIKE '2012 %' ).

WRITE: / ls_flight-carrid,
ls_flight-connid, ls_flight-fldate.
ENDSELECT.

Listing 3.7Usage of Subqueries

The approaches described previously provide a great variety of options for accessing database tables. Using joins, you can define relationships between several tables (and via FOR ALL ENTRIES, even between internal tables); use the aggregate functions for simple calculations; and use subqueries to allow nested selections. In addition to the SQL vocabulary, Open SQL provides further techniques to design database access flexibly and efficiently, which will be described next.

Using Open SQL, you can also specify parts of an SQL statement dynamically so that, for instance, the table name or the selected columns can be controlled via a variable that has to be specified in parentheses (as shown in the example in Listing 3.7).

DATA: lv_table TYPE string,
lt_fields TYPE string_table,
ls_carrier TYPE scarr.

" Table name as a string
lv_table = 'SCARR'.

" Dynamic output of the columns
APPEND 'CARRID' TO lt_fields.
APPEND 'CARRNAME' TO lt_fields.

SELECT (lt_fields) FROM (lv_table)
INTO CORRESPONDING FIELDS OF ls_carrier.
WRITE: / ls_carrier-carrid , ls_carrier-carrname.
ENDSELECT.

Listing 3.8Dynamic Open SQL

When working with dynamic Open SQL, note that the separating keywords (SELECT, FROM, WHERE, etc.) still have to be used statically in the code. This particularly helps prevent potential security vulnerabilities because certain attacks by means of SQL injection (introduction of unwanted database operations by an attacker) aren’t possible. However, especially when using dynamic SQL, you should always make sure that the values of the variables are checked to avoid runtime errors or security issues. To do so, you can use a list of allowed values (whitelists) or regular expression patterns.

Using cursors, you can separate the definition of the selection from the data retrieval. For this purpose, you first have to open a cursor by specifying the selection, and you can then retrieve the data from the database using this cursor at a later point in time or elsewhere (e.g., in a FORM routine), as shown in Listing 3.8. Because the number of cursors that can be used in parallel is limited, you should always make sure to close a cursor after using it.

DATA: lv_cursor TYPE cursor,
ls_flight TYPE sflight.

" Defining the cursor
OPEN CURSOR lv_cursor FOR
SELECT * FROM sflight
WHERE carrid = 'LH'.

" Retrieving a data record via the cursor
FETCH NEXT CURSOR lv_cursor INTO ls_flight.

" Closing the cursor
CLOSE CURSOR lv_cursor.

Listing 3.9Simple Example of Using a Cursor

The data flow between the database and the application server can be controlled in Open SQL by defining package sizes via the PACKAGE SIZE addition (see Listing 3.9). When doing so, the specified number of rows is always retrieved from the database when selecting into an internal table within a loop.

DATA: lt_book TYPE TABLE OF sbook.

" Selection into packages of 1,000 rows each
SELECT * FROM sbook
INTO TABLE lt_book
PACKAGE SIZE 1000.

" ...
ENDSELECT.

Listing 3.10Selecting Data While Specifying a Package Size

Before dealing with writing accesses, we’ll briefly summarize the options for read access provided by Open SQL. Using the SELECT statement, you can efficiently read data records from a relational data model (tables with foreign key relationships). The aggregate function allows you to express simple calculations on a column. Data transfer from the database can be controlled using advanced techniques. However, it isn’t possible to use complex filter expressions, case distinctions, or business calculations directly within the database. Furthermore, interim results can’t be temporarily stored in the database because the result of a query is always transferred to the application server.

Write Accesses and Transaction Behavior

The basic principles of the ABAP transaction concept, and in particular the differences between the database LUW and the SAP LUW, were already discussed in Figure 3.2. To change database contents, Open SQL provides the statements INSERT (creating data records), UPDATE (changing existing data records), MODIFY (changing or creating data records), and DELETE (deleting data records). In addition to changing individual entries, you can also edit several rows at the same time. For example, you can use an Open SQL statement to create or update several data records based on the contents of an internal table in one go. This usually significantly improves the performance of a program because a lot fewer database accesses are necessary. The example in Listing 3.10 shows how these so-called array operations are used. Similarly, you can update all or only selected columns when changing a data record. This can also lead to increased performance. These two techniques are particularly recommended on SAP HANA (see Chapter 14).

REPORT zr_a4h_chapter3_modify_array.

DATA: lt_country TYPE TABLE OF za4h_country_cls.

" Select countries and number of customers
SELECT country COUNT(*) AS class FROM scustom
INTO CORRESPONDING FIELDS OF TABLE lt_country
GROUP BY country.

" Change table entries in one go
MODIFY za4h_country_cls FROM TABLE lt_country.

COMMIT WORK.

Listing 3.11Modifying Table Contents via Array Operations

In Open SQL, the COMMIT WORK or ROLLBACK WORK statements are used for explicit transaction control. There are also situations where an implicit Commit (e.g., after completing a dialog step) or a rollback (e.g., in case of a runtime error) are performed automatically. To process database changes from several dialog steps in a single database LUW, the SAP LUW concept offers several bundling techniques. This primarily includes calling update modules (CALL FUNCTION ... IN UPDATE TASK) and bundling via subroutines (PERFORM ... ON COMMIT). If you perform direct writing operations on the database (e.g., with SQLScript), the programming model differs significantly from the traditional ABAP programming model (see Figure 3.2 and Chapter 14).

Even though Open SQL is database independent, it’s possible to pass hints to the respective database system (or, more specifically, to the database optimizer) to specify how a statement should be executed. In practice, this variant offers tuning options for database experts and is used rather infrequently in normal ABAP development. Using hints, you can specify how the database should access the data (e.g., using a specific index). Because hints must be maintained manually (e.g., when performing a release upgrade or a database migration), this option should only be used if there are no other tuning methods.

Although traditional Open SQL has a large functional scope, it only covers parts of the options available in the SQL standard. One reason for some commands not being available in Open SQL (e.g., UNION, CASE) is that they were implemented differently or not at all by the different database vendors. Moreover, proprietary SQL enhancements of databases via Open SQL can’t be used due to database independence, which complicates the access to special engines particularly for SAP HANA. For this reason, SAP is working on both extending the scope of Open SQL (in cooperation with the manufacturers of supported databases) and providing specific capabilities of SAP HANA to ABAP developers. The chapters of Part II focus on these options.

3.2.3Database Views in the ABAP Data Dictionary

Database views are a standard option for looking at data based on one or several tables in the database and thus predefining parts of a SQL query. In most database systems, views are created using SQL:

CREATE VIEW view_name AS SELECT ...

This section provides an overview of traditional options for creating views using DDIC. Chapter 4 will introduce you to SAP HANA-specific options that go beyond the wrapping of a simple SQL query.

While you can define data views using the DDIC, not all options of Open SQL are available when following this approach. Basically, you can link several tables via an inner join and add fields to the projection list. You can’t use other join types, aggregates, or subqueries, however.

Figure 3.7 shows the standard SFLIGHTS view, which defines a join to add fields from tables SCARR, SPFLI, and SFLIGHT. The corresponding CREATE VIEW statement can be displayed via the menu bar (Extras • CREATE Statement).

These database views can be accessed like tables from ABAP coding using Open SQL and Native SQL. In this context, note that modifying operations can only be executed for views that access only one table. Similar to table access, you can configure buffering in the technical settings.

[»]Core Data Services

SAP currently works on a unified view creation process in SAP HANA and the DDIC called Core Data Services (CDS). The goal of this approach is to enhance the scope of functions significantly for defining views in the DDIC. Chapter 6, Section 6.2.1, presents the options of CDS in detail.

Dictionary View SFLIGHTS

Figure 3.7Dictionary View SFLIGHTS

3.2.4Database Access via Native SQL

In addition to Open SQL, which enables database-independent access that is integrated into the ABAP programming language, there is another method for accessing the database from ABAP. With this variant, you more or less directly specify the native database commands. For this reason, this is also referred to as Native SQL.

Before we deal with the technical aspects of supporting Native SQL in AS ABAP, we’ll explain why this variant plays a more important role in the context of SAP HANA than in the past. To benefit fully from the potential of SAP HANA, you must particularly use those functions that aren’t standard relational database capabilities, includes using capabilities in SAP HANA-specific SQL beyond the SQL standard, and accessing development objects in SAP HANA beyond normal tables and SQL views. (Chapter 4 explains this in detail.) At this point, note that using Native SQL will play an important role in this context.

There are two traditional options for using Native SQL in ABAP: either via the EXEC SQL statement or via ABAP Database Connectivity (ADBC)—an object-oriented interface available as of SAP NetWeaver 2004 (release 6.40). In this context, SAP recommends using ADBC because this approach provides greater flexibility and better options for troubleshooting. Within the scope of this book, we’ll therefore describe and use the ADBC variant. With regard to some of its concepts, ADBC is similar to Java Database Connectivity (JDBC), a standard database interface of the Java platform.

[»]ABAP Managed Database Procedures

In addition to the options mentioned, you can also create ABAP Managed Database Procedures (AMDP) for SAP HANA as of ABAP 7.4 SP 8. These database procedures are implemented with SQLScript and are realized as a special ABAP method. This important new technology is presented in Chapter 6, Section 6.5.

To use ADBC, essentially three ABAP classes are needed: CL_SQL_CONNECTION, CL_SQL_STATEMENT, and CL_SQL_RESULT_SET. In the first step, you must use the constructor (or the static method GET_CONNECTION) of the CL_SQL_CONNECTION class to retrieve a database connection. If you don’t specify any parameters, it will return the standard database connection, which is also used by default in Open SQL. However, you can also specify the name of a secondary connection (see Section 3.2.5). Using this connection, you create an object of type CL_SQL_STATEMENT via the CREATE_STATEMENT method, which can be used, for example, for reading database accesses via the method EXECUTE_QUERY by passing the SQL statement as a string. The result of this query is an instance of type CL_SQL_RESULT_SET. Similarly, writing accesses can be executed via EXECUTE_UPDATE, or DDL statements via EXECUTE_DDL.

To transfer the result of a query to an internal ABAP table, you’ll first have to pass a reference to this table via the SET_PARAM_TABLE method. Then you’ll be able to start the data transfer via NEXT_PACKAGE. When doing so, you can specify the package size, that is, the number of rows. The selected columns and corresponding data types must be compatible with the target structure for a call to be successful.

If an error occurs when executing the SQL statement, an exception of type CX_SQL_EXCEPTION is thrown, which can be used to obtain details such as the error code and error text. Possible runtime errors are described in detail in Chapter 7, Section 7.2.

The example in Listing 3.11 shows how the named classes are used for a simple read access. In this example, the SQL statement uses expressions from the SAP HANA-specific SQL dialect, which can’t be used in the same manner in Open SQL.

REPORT ZR_A4H_CHAPTER3_ADBC.

" Variables for ADBC call
DATA: lv_statement TYPE string,
lo_conn TYPE REF TO cl_sql_connection,
lo_statement TYPE REF TO cl_sql_statement,
lo_result_set TYPE REF TO cl_sql_result_set.

" Definition of the result structure
TYPES: BEGIN OF ty_result,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
fldate TYPE s_date,
days type i,
END OF ty_result.

DATA: lt_result TYPE TABLE OF ty_result,
lr_result TYPE REF TO data.

FIELD-SYMBOLS: <l> TYPE ty_result.
" Data reference
GET REFERENCE OF lt_result INTO lr_result.
" Native SQL Statement: sequence and data types
" of selected columns must match
" results structure
lv_statement =
| SELECT carrid, connid, fldate, |
&& | days_between(fldate, current_utcdate) as days |
&& | FROM sflight WHERE mandt = '{ sy-mandt }' and |
&& | days_between(fldate, current_utcdate) < 10 |.

TRY.
" Prepare SQL connection and statement
lo_conn = cl_sql_connection=>get_connection( ).
lo_statement = lo_conn->create_statement( ).
lo_result_set = lo_statement->execute_query( lv_statement ).
lo_result_set->set_param_table( lr_result ).

" Get result
lo_result_set->next_package( ).
lo_result_set->close( ).
CATCH cx_sql_exception.
" Error handling
ENDTRY.

LOOP AT lt_result ASSIGNING <l>.
WRITE: / <l>-carrid , <l>-connid , <l>-fldate, <l>-days.
ENDLOOP.

Listing 3.12Native SQL Access via ADBC

The days_between function in SAP HANA-specific SQL determines the number of days between the parameters, that is, the number of days between the current date (which is obtained from the current_utcdate variable provided within SAP HANA-specific SQL) and the flight date in the example. The output of Listing 3.11 thus comprises all future flights and all flights within the past 10 days. For every flight, the system also displays the difference (in days) between the flight date and the current date. Because this query can’t be expressed via Open SQL, the only traditional option is to load all data into the application server and calculate the dates via ABAP. Because we “pushed” a complex filter expression down to the database, this means that we implemented a so-called code pushdown via Native SQL.

If you want to use the same SQL statement consecutively with different parameterization, you should use prepared statements for performance reasons. These prepared SQL statements reduce the effort for subsequent executions. To do this, you create an instance of the CL_SQL_PREPARED_STATEMENT class (a subclass of CL_SQL_STATEMENT), while passing an SQL statement with placeholders that can be bound to a variable. Listing 3.13 shows the usage of prepared statements and placeholders using some of the ABAP language elements from ABAP 7.4 (see Appendix B). Note that you can use placeholders independently of prepared statements.

REPORT zr_a4h_chapter3_adbc2.

" Variables for the ADBC call
DATA: lv_sql TYPE string,
lo_result TYPE REF TO cl_sql_result_set.

DATA: lt_result TYPE TABLE OF scarr,
lv_param TYPE s_carrid.

" SQL statement with placeholder
lv_sql =
| SELECT * |
&& | FROM SCARR WHERE mandt = '{ sy-mandt }' |
&& | AND carrid = ? limit 5|.

TRY.
" Create prepared statement and set parameter
DATA(lo_sql) =
NEW cl_sql_prepared_statement( lv_sql ).
lo_sql->set_param( REF #( lv_param ) ).

" Execution with value for placeholder
lv_param = 'LH'.
lo_result = lo_sql->execute_query( ).
lo_result->set_param_table( REF #( lt_result ) ).
" Get and display result
lo_result->next_package( ).
lo_result->close( ).
LOOP AT lt_result ASSIGNING FIELD-SYMBOL(<l1>).
WRITE: / <l1>-carrid , <l1>-carrname.
ENDLOOP.

" Second execution with different value
CLEAR lt_result.
lv_param = 'UA'.
lo_result = lo_sql->execute_query( ).
lo_result->set_param_table( REF #( lt_result ) ).

" Get and display result
lo_result->next_package( ).
lo_result->close( ).
LOOP AT lt_result ASSIGNING FIELD-SYMBOL(<l2>).
WRITE: / <l2>-carrid , <l2>-carrname.
ENDLOOP.

" Close prepared SQL statement
lo_sql->close( ).
CATCH cx_sql_exception INTO DATA(lo_ex).
" Error handling
lv_param = 'UA'.
WRITE: | Exception: { lo_ex->get_text( ) } |.
ENDTRY.

Listing 3.13Prepared SELECT Statement with ADBC

In addition, when working with ABAP 7.4, you can execute mass operations via the ADBC interface—just as you can when using Open SQL. For this purpose, the SET_PARAM_TABLE method is available in the CL_SQL_STATEMENT class, which can be used to pass an internal table as an input parameter. This makes it possible to use the ADBC interface to fill a database table with the values of an internal table, for instance.

[+]Using the SQL Console in SAP HANA Studio

Usage of Native SQL is rather error-prone; this is especially true because syntax errors in SQL statements are only noticed at runtime. Before using a Native SQL statement in ABAP via ADBC, you should therefore first test the statement via the SQL console in SAP HANA Studio, which was introduced in Chapter 2, Section 2.4.3.

When implementing commands that were executed successfully in the SQL console in ABAP, note the following differences:

In comparison to Open SQL, some capabilities aren’t directly integrated into Native SQL. These include the ABAP table buffer (Native SQL doesn’t read from the buffer), automatic client handling (when using Native SQL, the client must be inserted manually in WHERE or JOIN conditions, as shown in the example from Listing 3.11), and some other useful enhancements in Open SQL (IN, FOR ALL ENTRIES, INTO CORRESPONDING FIELDS, etc.).

[+]Pitfalls When Using Native SQL

There are some pitfalls with regard to the syntax when using Native SQL for the first time. To avoid unnecessary errors, consider the following.

Selected fields are separated by a comma:

For table aliases, a period is used instead of the tilde character:

Moreover, the log and trace entries in different analysis tools (see Section 3.3) contain less context information for Native SQL. This means that the names of the tables or views aren’t visible because the ABAP Compiler can’t obtain this information from the Native SQL statement. On the other hand, when using ADBC, this context can be set using the SET_TABLE_NAME_FOR_TRACE method of the CL_SQL_STATEMENT class.

To conclude this section, we’ll briefly explain some transaction-related aspects of Native SQL. If you use the standard database connection for Native SQL access, you must take into account that you share the database transaction within the ABAP session with other program components (e.g., classes from the SAP standard). To avoid inconsistencies, you should not run any commands for transaction control (e.g., COMMIT or ROLLBACK) via Native SQL.

[ ! ]Comprehensive Database Knowledge Required to Use Native SQL

Because incorrect usage of Native SQL can impact system stability, comprehensive database knowledge is required to use this language. Using the following Native SQL statement to set a schema context for the standard database connection of the AS ABAP leads to major problems:

SET SCHEMA <name>

The reason for this is that other database accesses within the same session no longer use the default schema of the AS ABAP but use the set schema instead. This can easily lead to inconsistencies within the system and should be avoided at all costs. In general, use Native SQL with great caution, and always take security concerns into account (e.g., the avoidance of SQL injection).

3.2.5Secondary Database Connections

In addition to the primary database, that is, the database containing all tables maintained by the application server (including the actual ABAP code), AS ABAP can access other databases as well. These are referred to as secondary databases or secondary database connections. This section describes the technical steps to set up and use a secondary database connection.

Secondary connections are important in the context of SAP HANA, especially when implementing the side-by-side scenarios described in Chapter 1, Section 1.4.1. In those cases, complex database queries with very long runtimes are moved to SAP HANA. Secondary connections also form the basis for Redirected Database Access (RDA, see Appendix D), which is offered by the SAP Kernel 7.21. With this kernel release, not only can the secondary connection be maintained in the ABAP code but also in Customizing for specific programs and tables.

As a prerequisite for setting up a secondary database connection, the matching DBSL with the database driver must be installed; for SAP HANA, this means the DBSL must be installed with the SAP HANA Client. You can then create new connections in the Database Administration Cockpit (DBA Cockpit) via Transaction DBACOCKPIT (alternatively, you can also use Transaction ST04). The DBA Cockpit is the central starting point in AS ABAP for almost all database configuration and monitoring tasks. Within this book, we’ll only discuss some of these aspects in the context of Chapter 7.

To set up a new connection, select DB Connections and click Add. You must then specify a unique name and the connection data (database system, host name, port, user, password, etc.), as shown in Figure 3.8. The schema associated with the specified user is always used as the default schema for this connection.

Setting Up a Secondary Connection in the DBA Cockpit

Figure 3.8Setting Up a Secondary Connection in the DBA Cockpit

You also can configure a set of parameters to define how the system should establish the created connections (see Figure 3.9):

After creating the connection, you can test it via the DBA Cockpit.

You can leverage secondary connections using both Open SQL and Native SQL. For Open SQL, the addition CONNECTION is used for this purpose (see Listing 3.12).

DATA: ls_carrier TYPE scarr.
SELECT SINGLE * FROM scarr CONNECTION ('SECONDARY') INTO
ls_carrier WHERE carrid = 'LH'.

Listing 3.14Using Secondary Connections in Open SQL

To use Open SQL via a secondary connection, the tables and corresponding columns to be accessed must be known in the local DDIC, especially for any existing extensions.

In ADBC, you can specify the secondary connection when creating the connection (as shown in the example from Listing 3.13).

DATA: lo_statement TYPE REF TO cl_sql_statement,
lo_result_set TYPE REF TO cl_sql_result_set.

TRY.
" Prepare SQL connection and statement
lo_statement = cl_sql_connection=>get_connection
( 'SECONDARY' )->create_statement( ).
lo_result_set = lo_statement->execute_query(
|SELECT SINGLE * FROM SCARR WHERE carrid = 'LH' AND
mandt = { sy-mandt }| ).

" ...
CATCH cx_sql_exception.
" Error handling
ENDTRY.

Listing 3.15Using Secondary Connections in ADBC

Both Open SQL and ADBC use the associated schema of the secondary connection, which is defined by the database user when configuring the connection, as the default schema. If table SCARR doesn’t exist in this schema in the examples from Listing 3.12 and Listing 3.13, the program terminates. When using Native SQL, you can manually specify the schema; however, you should avoid this in productive scenarios.

Although generally secondary connections are used to accelerate queries by means of SAP HANA, we’ll briefly discuss the transaction behavior for the sake of completeness. Secondary connections form their own transaction context so that data can be committed via a secondary connection (using COMMIT CONNECTION) without affecting the actual transaction. Secondary connections are terminated, at the very latest, after the actual transaction is closed or if a change in the work process is possible in the application program.