5.2Integrating Native Procedures with ABAP

In Chapter 4, you learned what SQLScript is and how you can use it for implementing database procedures. Now we want to explain how to call database procedures from ABAP. You have two options:

• Native SQL and ADBC (see also Chapter 3)

• Database procedure proxies

As of ABAP Release 7.0 and SAP Kernel 7.20, it’s possible to use ADBC to call database procedures in SAP HANA. Database procedure proxies are available as of Release 7.4 and require that SAP HANA be used as the primary database. Moreover, database procedure proxies only support the XML file format (.procedure), which is actually outdated.

5.2.1Access via Native SQL

As already described in Chapter 4, Section 4.3, the system generates different runtime objects in the _SYS_BIC schema when activating a database procedure. It also generates a public synonym. Here, you can use Native SQL to access the database procedure from ABAP.

However, the use of Native SQL to call a database procedure is relatively time-consuming and prone to errors. Later in this section, you’ll see how you can only use temporary tables to exchange tabular input and output parameters with the database procedure. Furthermore, SAP NetWeaver AS ABAP doesn’t detect syntax errors in Native SQL statements until runtime. For more information, refer to the explanations provided in Chapter 3.

We’ll now use several examples to provide a detailed description of how to use Native SQL to access database procedures. First, we’ll consider a database procedure that determines the name of an airline on the basis of the ID. For the remaining examples, we’ll revert to the database procedures from Chapter 4.

Example 1: Calling a Database Procedure

If you use ADBC to call a database procedure, the CL_SQL_STATEMENT class makes the EXECUTE_PROCEDURE method available. You can use this as long as a database procedure doesn’t have a tabular input/output parameter.

Program ZR_A4H_CHAPTER5_CARRNAME_ADBC shows an example of the EXECUTE_PROCEDURE method (see Listing 5.3). It calls the DETERMINE_CARRNAME database procedure, which has the following input and output parameters:

• IV_MANDT: Client.

• IV_CARRID: ID of an airline.

• EV_CARRNAME: Name of an airline.

PARAMETERS: p_carrid TYPE s_carr_id.

DATA: lo_sql_statement TYPE REF TO cl_sql_statement,
lv_carrname TYPE s_carrname.

TRY.
" create statement
lo_sql_statement =
cl_sql_connection=>get_connection(
)->create_statement( ).

" bind parameters
lo_sql_statement->set_param( data_ref =
REF #( sy-mandt )
inout = cl_sql_statement=>c_param_in ).

lo_sql_statement->set_param( data_ref =
REF #( p_carrid )
inout = cl_sql_statement=>c_param_in ).

lo_sql_statement->set_param( data_ref =
REF #( lv_carrname )
inout = cl_sql_statement=>c_param_out ).

" call procedure
lo_sql_statement->execute_procedure(
'"test.a4h.book.chapter04::DETERMINE_CARRNAME"' ).

CATCH cx_sql_exception INTO DATA(lo_ex).
" error handling
WRITE: | { lo_ex->get_text( ) } |.
ENDTRY.

WRITE: / lv_carrname.

Listing 5.3Using Native SQL to Call a Database Procedure

First, the program generates an instance of the CL_SQL_STATEMENT class. Then, it calls the SET_PARAM method to bind the input and output parameters of the database procedures to the actual parameters. It then calls the EXECUTE_PROCEDURE method.

Example 2: Tabular Output Parameters

Alternatively, you can use the EXECUTE_QUERY method (together with the WITH OVERVIEW addition) to execute a database procedure. This also works for database procedures that have tabular input and output parameters.

Program ZR_A4H_CHAPTER5_TOP_ADBC in Listing 5.4 shows an example of the EXECUTE_QUERY method, in which the DETERMINE_TOP_CONNECTIONS database procedure is called. This database procedure determines an airline’s top connections and has the following input and output parameters:

• IV_MANDT: Client.

• IV_CARRID: ID of an airline.

• IV_ALGORITHM: Controls how the top connections are determined.

• ET_CONNECTIONS: A table parameter that contains the airline’s ID CARRID and connection code CONNID.

PARAMETERS: p_carrid TYPE s_carr_id.

" Definition of the result structure
TYPES: BEGIN OF ty_connections,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
END OF ty_connections.

DATA: lt_connections TYPE TABLE OF ty_connections,
lv_statement TYPE string,
lo_result_set TYPE REF TO cl_sql_result_set,
lo_connections TYPE REF TO data.

TRY.
" Delete local temporary table
lv_statement = | DROP TABLE #ET_CONNECTIONS |.
cl_sql_connection=>get_connection(
)->create_statement( )->execute_ddl( lv_statement ).
CATCH cx_sql_exception.
" The local temporary table may not exist,
" we ignore this error
ENDTRY.

TRY.
" Create local temporary table
lv_statement = | CREATE LOCAL TEMPORARY ROW|
&& | TABLE #ET_CONNECTIONS LIKE "_SYS_BIC".|
&& |"test.a4h.book.chapter04::GlobalTypes.t|
&& |t_connections" |.
cl_sql_connection=>get_connection(
)->create_statement( )->execute_ddl( lv_statement ).

" Call database procedure
lv_statement = | CALL "test.a4h.bo|
&& |ok.chapter04::DETERMINE_TOP_CONNECTIONS|
&& |"( '{ sy-mandt }' , '{ p_carrid }', 'P'|
&& |, #ET_CONNECTIONS ) WITH OVERVIEW |.
lo_result_set = cl_sql_connection=>get_connection(
)->create_statement( )->execute_query(
lv_statement ).
lo_result_set->close( ).

" Read local temporary table
lv_statement = | SELECT * FROM #ET_CONNECTIONS |.
lo_result_set = cl_sql_connection=>get_connection(
)->create_statement( )->execute_query(
lv_statement ).

" Read result
GET REFERENCE OF lt_connections INTO
lo_connections.
lo_result_set->set_param_table( lo_connections ).
lo_result_set->next_package( ).
lo_result_set->close( ).
CATCH cx_sql_exception INTO DATA(lo_ex).

" Error handling
WRITE: | { lo_ex->get_text( ) } |.
ENDTRY.

LOOP AT lt_connections ASSIGNING
FIELD-SYMBOL(<ls_connections>).
WRITE: / <ls_connections>-carrid ,
<ls_connections>-connid.
ENDLOOP.

Listing 5.4Handling Table-Based Output Parameters

We’ll now use the program to explain, in particular, how tabular input and output parameters are exchanged with a database procedure. Program ZR_A4H_CHAPTER5_TOP_ADBC uses temporary table #ET_CONNECTIONS to transfer the ET_CONNECTIONS table parameter.

[»]Temporary Tables

Many databases, including the SAP HANA database, enable you to save temporarily the interim and final results of calculations in temporary tables. For this use case, temporary tables have many different advantages over conventional tables:

• The table definition and table contents are deleted automatically from the database if they are no longer required.

• The database automatically isolates data in parallel sessions from one another. It’s neither necessary nor possible to place locks on temporary tables.

• The database doesn’t write a transaction log for temporary tables.

• Generally, it’s more efficient to use temporary tables than conventional tables.

SAP HANA supports global and local temporary tables:

• Global temporary tables
  The table definition can be used in different sessions. The table contents can only be displayed for the current session. At the end of the session, the table contents are deleted from the database automatically.

• Local temporary tables
  Both the table definition and the table contents are only valid for the current session. In other words, both are deleted from the database automatically at the end of the session.

When using temporary tables to transfer data between AS ABAP and a database procedure, note the following:

• If you work with global temporary tables, you can create these once (because they can be used in different sessions). Organizationally, however, you must ensure that the table name isn’t used for different use cases (that require a different table structure).

• You can create global temporary tables at design time. Then you must ensure that the tables are also available in the test and production systems after a transport.

• If you decide to create global temporary tables at runtime, you must ensure that—before you call a database procedure—the table structure is suitable for the interface of the database procedure called (because this may have changed in the meantime).

• You must create local temporary tables at least once for each session (also note the following explanations in relation to the ABAP work process and database connection). Consequently, you can only create local temporary tables when an ABAP program is running.

• Because each ABAP work process has only one connection with the database, multiple ABAP programs processed by the same work process subsequently, are one session for the database. Therefore, after an ABAP program ends, neither the definition nor the contents of local (and global) temporary tables are deleted automatically.

• For global and local temporary tables, you should delete the contents (of the current session) before you call the database procedure.

Program ZR_A4H_CHAPTER5_TOP_ADBC in Listing 5.4 works with a local temporary table. First, it uses DROP TABLE #ET_CONNECTIONS to delete local temporary table #ET_CONNECTIONS if it exists. It then uses the CREATE LOCAL TEMPORARY ROW TABLE statement to create a (new) local temporary table with the name #ET_CONNECTIONS. Here, the program refers to the table type that the system automatically created for the ET_CONNECTIONS output parameter when the database procedure was activated. This approach enables the program to ensure that, before the database procedure is called, the temporary table is empty and suitable for the current structure of the ET_CONNECTIONS output parameter.

The program now uses the EXECUTE_QUERY method to call the database procedure. It transfers SY-MANDT, P_CARRID, and 'P' to the input parameters, and it transfers temporary table #ET_CONNECTIONS to the output parameter for the database procedure.

After the database procedure has been called, the program reads the contents of temporary table #ET_CONNECTIONS, which correspond to the transferred airline’s top connections.

Example 3: Tabular Input Parameters

If a database procedure has tabular input parameters, you can proceed in the same way as for tabular output parameters. Program ZR_A4H_CHAPTER5_KPIS_ADBC in Listing 5.5 shows how to call the GET_KPIS_FOR_CONNECTIONS database procedure for a set of flight connections. The database procedure determines some key performance indicators (KPIs) for each connection transferred.

The procedure has the following input and output parameters:

• IV_MANDT: Client.

• IT_CONNECTIONS: A table parameter that contains the airline’s ID CARRID and connection code CONNID.

• ET_KPIS: A table parameter that contains KPIs for connections.

  ...
  LOOP AT lt_connections INTO ls_connections.
  lv_statement = | INSERT INTO #IT_CONNECTIONS VALUES
  ( '{ ls_connections-carrid }', '{ ls_connections-
  connid }' )|.
  cl_sql_connection=>get_connection(
  )->create_statement(
  )->execute_update( lv_statement ).
  ENDLOOP.
  " Call database procedure
  lv_statement = | CALL "test.a4h.bo|
  && |ok.chapter04::GET_KPIS_FOR_CONNECTIONS|
  && |"( '{ sy-mandt }' , #IT_CONNECTIONS, #ET_KPIS )
  WITH OVERVIEW |.
  lo_result_set = cl_sql_connection=>get_connection(
  )->create_statement( )->execute_query( lv_statement ).
  lo_result_set->close( ).
  ...

  Listing 5.5Handling Table-Based Input Parameters

Before the database procedure is called, the program fills local temporary table #IT_CONNECTIONS with the relevant flight connections. EXECUTE_QUERY is used to call the database procedure.

5.2.2Defining Database Procedure Proxies

As of ABAP Release 7.4, you can define a database procedure proxy to access database procedures from ABAP. Note that only the XML file format (.procedure) is supported (see Chapter 4, Section 4.3).

A database procedure proxy is a proxy object that represents a database procedure in the DDIC.

[ ! ]Multiple Proxy Objects for One Database Procedure

Technically, it’s possible to create multiple database procedure proxies for one database procedure. However, we don’t recommend this. In the DDIC, you should never create more than one proxy object for a database procedure.

The system also automatically creates an interface for each database procedure proxy. You can use this interface to influence the parameter names and data types used when calling the database procedure with ABAP:

• You can change the names of the input and output parameters as soon as they exceed 30 characters. In this case, the system initially abbreviates the parameter names. You can then overwrite these abbreviated names, if necessary.

• You can always overwrite the component names of table parameters.

• You can assign the relevant data type to each parameter. This is important because SQL data types aren’t uniquely mapped to ABAP data types and DDIC types. Consequently, when creating a proxy object, the system can’t (always) derive the correct ABAP data type and/or DDIC type.

We’ll now explain how to create a proxy object for the DETERMINE_TOP_CONNECTIONS_XML database procedure. To do this, open the ABAP Development Tools in Eclipse, and choose the menu option, File • New • Other. Then, choose Database Procedure Proxy, and click Next. Figure 5.3 shows the window that opens.

Figure 5.3Creating a Database Procedure Proxy

In this window, enter the following data for the database procedure proxy:

• Name
  Used to call the database procedure later in ABAP.

• Description
  Piece of explanatory text.

• SAP HANA Procedure
  Name of the existing database procedure in the SAP HANA repository.

• Parameter Types Interface
  Name of the interface that is automatically created when you create the proxy object (see Listing 5.6).

After you choose Next and Finish, the system creates the database procedure proxy and the corresponding interface.

The Project Explorer contains the database procedure proxy in the corresponding package below the Dictionary • DB Procedure Proxies node. Just like the other interfaces, the parameter type interface is located in the corresponding package below the Source Library node.

Figure 5.4 shows the database procedure proxy for the DETERMINE_TOP_CONNECTIONS_XML database procedure. If you want to adjust parameter names or data types, you can do this in the ABAP Name, ABAP Type, and DDIC Type Override columns. For example, you can map the CONNID column in the table-based ET_CONNECTIONS output parameter to the S_CONN_ID data element (and therefore to the ABAP data type N length 4).

Figure 5.4Database Procedure Proxy and Interface

Listing 5.6 shows the interface that the system automatically creates after the data types have been adjusted.

interface ZIF_DETERMINE_TOP_CONNECTIONS public.
types: iv_mandt type mandt.
types: iv_carrid type s_carr_id.
types: iv_algorithm type c length 1.
types: begin of et_connections,
carrid type s_carr_id,
connid type s_conn_id,
end of et_connections.
endinterface.

Listing 5.6Interface of the Proxy Object

5.2.3Calling Database Procedure Proxies

Now that you’ve activated the database procedure proxy, you can use the proxy object to call the database procedure. Program ZR_A4H_CHAPTER5_TOP_PROXY in Listing 5.7 shows an example of this usage.

PARAMETERS: p_carrid TYPE s_carr_id.

DATA: lt_connections TYPE TABLE OF
zif_determine_top_connections=>et_connections.

TRY.
CALL DATABASE PROCEDURE
zdp_determine_top_connections
EXPORTING
iv_mandt = sy-mandt
iv_carrid = p_carrid
iv_algorithm = 'P'
IMPORTING
et_connections = lt_connections.

CATCH cx_sy_db_procedure_sql_error
cx_sy_db_procedure_call INTO DATA(lo_ex).
" Error handling
iv_algorithm = 'P'
WRITE: | { lo_ex->get_text( ) } |.
ENDTRY.

LOOP AT lt_connections ASSIGNING
FIELD-SYMBOL(<ls_connections>).
WRITE: / <ls_connections>-carrid ,
<ls_connections>-connid.
ENDLOOP.

Listing 5.7Calling a Database Procedure Proxy

The program uses the CALL DATABASE PROCEDURE statement to call the DETERMINE_TOP_CONNECTIONS_XML database procedure via the ZDP_DETERMINE_TOP_CONNECTIONS proxy. When defining internal table LT_CONNECTIONS, the program refers to the ZIF_DETERMINE_TOP_CONNECTIONS interface. The program catches any problems that may occur when calling the database procedure (exceptions of the type CX_SY_DB_PROCEDURE_SQL_ERROR and CX_SY_DB_PROCEDURE_CALL).

5.2.4Adjusting Database Procedure Proxies

If you change a database procedure (or more accurately, the interface of a database procedure) in SAP HANA Studio, you must synchronize the proxy object with the SAP HANA repository via the Synchronize button (refer to Figure 5.4).

During the synchronization process, you can decide whether you want to retain or overwrite the adjustments made to the proxy object (component names or data types).

Chapter 6 introduces you to ABAP Managed Database Procedures (AMDP). When used within the scope of ABAP, they have several advantages compared with procedures that you’ve created via SAP HANA Studio. For this reason, we generally recommend the usage of ABAP database procedures if you want to use SQLScript within ABAP.