ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → ABAP and SAP HANA → ABAP and SAP HANA, Examples

SAP HANA, Call Database Procedure

This example demonstrates how a database procedure in the SAP HANA database can be called.

Other versions: ~~7.31~~ | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

    IF cl_db_sys=>is_in_memory_db = abap_false.
      out->display(
        `Example can be executed on SAP HANA Database only` ).
      LEAVE PROGRAM.
    ENDIF.

    DATA carrier TYPE spfli-carrid VALUE 'LH'.
    cl_demo_input=>request( CHANGING field = carrier ).
    DATA(oref) =
      NEW cl_demo_call_hana_db_procedure( to_upper( carrier ) ).

    DATA(osql_result) = oref->osql( ).
    DATA(adbc_result) = oref->adbc( ).
    DATA(cdbp_result) = oref->cdbp( ).
    DATA(amdp_result) = oref->amdp( ).

    IF osql_result = adbc_result AND
       osql_result = cdbp_result AND
       osql_result = amdp_result.
      out->begin_section(
        `Result of Open SQL, ADBC, CALL DATABASE PROCEDURE, and AMDP`
        )->write( amdp_result ).
    ELSE.
      out->write( 'Error' ).
    ENDIF.
    out->display( ).

Description

Take a database procedure called /1BCAMDP/DEMO_ADBC_GET_FLIGHTS in the database schema SAPsid:

begin
  FLIGHTS =
    select *
           from SFLIGHT as S
           where exists (
                  select MANDT, CARRID, CONNID
                  from :CONNECTIONS as C
                  where C.MANDT  = S.MANDT and
                        C.CARRID = S.CARRID and
                        C.CONNID = S.CONNID );
end

The procedure has a tabular input parameter CONNECTIONS with the three-column HANA table type DEMO_ADBC_CONNECTIONS_TYPE and an output parameter FLIGHTS with the type SFLIGHT defined in ABAP Dictionary. The HANA table type has three character-like columns, MANDT with length 3, CARRID with length 3, and CONNID with length 4. The task is to call this procedure from ABAP while passing an internal table to the input parameter and applying the output parameter to an internal table.

To do this, the example program calls various methods of the class CL_DEMO_CALL_HANA_DB_PROCEDURE. The passed table connection_tab is filled in the instance constructor of the class in accordance with user input. Furthermore, the database procedure, the HANA table type, and a database procedure proxy (usually static) are created here temporarily.

Comparison Implementation Using Open SQL

The method OSQL shows how the implementation of the database procedure can be expressed in Open SQL too. If the function is required only in ABAP, an implementation in SQLScript is unnecessary in this case and should be avoided in fact (as specified in the relevant programming guideline. In our example, the database procedure is a replacement for a globally available procedure also to be called in ABAP. The result of the method OSQL is compared with the results of the various procedure calls to check their correctness.

METHOD osql.
  IF connection_tab IS NOT INITIAL.
    SELECT *
          FROM sflight
          INTO TABLE @flights
          FOR ALL ENTRIES IN @connection_tab
        WHERE carrid = @connection_tab-carrid AND
              connid = @connection_tab-connid.
  ENDIF.
ENDMETHOD.

Call with Native SQL Using ADBC

The method ADBC uses Native SQL and ADBC to call the database procedure. To pass the internal table connection_tab to the database procedure, a temporary table DEMO_ADBC_CONNECTIONS with the required type DEMO_ADBC_CONNECTIONS_TYPE is created on the database and provided with the values of the internal table using insert. The database procedure is then called with the temporary table as an input parameter and the result is assigned to an ADBC results set. To read from the results set, a standard table flights_std is required as an auxiliary table, since the return value flights is a sorted table that cannot be used for ADBC. The temporary table DEMO_ADBC_CONNECTIONS is removed again after the database procedure is called.

METHOD adbc.
  DATA(sql) = NEW cl_sql_statement( ).

  TRY.
      sql->execute_update( 'truncate table DEMO_ADBC_CONNECTIONS' ).
      sql->execute_ddl(    'drop table DEMO_ADBC_CONNECTIONS' ).
    CATCH cx_sql_exception ##no_handler.
  ENDTRY.

  TRY.
      sql->execute_ddl(
        `create global temporary row table DEMO_ADBC_CONNECTIONS ` &&
           `like DEMO_ADBC_CONNECTIONS_TYPE` ).
      sql->set_param_table( REF #( connection_tab ) ).
      sql->execute_update(
        |insert into DEMO_ADBC_CONNECTIONS values ( ?, ?, ? )| ).

      DATA(result) =
              sql->execute_query(
                `call "/1BCAMDP/DEMO_ADBC_GET_FLIGHTS"( ` &&
                `  CONNECTIONS =>DEMO_ADBC_CONNECTIONS, ` &&
                `  FLIGHTS => NULL )` ).

      DATA flights_std TYPE STANDARD TABLE OF sflight WITH EMPTY KEY.
      result->set_param_table( REF #( flights_std ) ).
      result->next_package( ).
      result->close( ).
      flights = flights_std.

      sql->execute_update( 'truncate table DEMO_ADBC_CONNECTIONS' ).
      sql->execute_ddl(    'drop table DEMO_ADBC_CONNECTIONS' ).
    CATCH cx_sql_exception.
      CLEAR flights.
  ENDTRY.
ENDMETHOD.

Call Using Database Procedure Proxy

The method CDBP calls the database procedure with the statement CALL DATABASE PROCEDURE using the assigned database procedure proxy DEMO_ADBC_GET_FLIGHTS_PROXY. The only thing to note here is that input parameters and output parameters of the database procedure have different row types than the internal tables assigned to them. In both cases, the column CONNID has the type NVARCHAR(4) on the database, but the type n with length 4 in ABAP. For this reason, the internal table connection_tab is first assigned to a temporary table connections with the correct row type before the procedure is called. No auxiliary table is required for the result flights, since the row type is described using an ABAP Dictionary type. An appropriate mapping can be performed for this type using the mapping table params. As an alternative to an auxiliary table for connection_tab, its data type could be declared in ABAP Dictionary too and an appropriate mapping performed.

METHOD cdbp.
  TYPES:
    BEGIN OF connection,
      mandt  TYPE c LENGTH 3,
      carrid TYPE c LENGTH 3,
      connid TYPE c LENGTH 4,
    END OF connection.
  DATA connections TYPE STANDARD TABLE OF connection
                   WITH EMPTY KEY.
  connections = connection_tab.

  CALL DATABASE PROCEDURE ('DEMO_ADBC_GET_FLIGHTS_PROXY')
    EXPORTING connections = connections
    IMPORTING flights     = flights.

ENDMETHOD.

Call Using AMDP

The method AMDP calls the AMDP method AMDP_METH, in which a call of the database procedure is implemented in SQLScript. In this case, all type-dependent actions required are performed by the AMDP framework.

METHOD amdp.
amdp_meth( EXPORTING connections = connection_tab
IMPORTING flights = flights ).
ENDMETHOD.

METHOD amdp_meth BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
  call "/1BCAMDP/DEMO_ADBC_GET_FLIGHTS"(
         CONNECTIONS => :CONNECTIONS,
         FLIGHTS => :FLIGHTS );
ENDMETHOD.