ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → ABAP and SAP HANA → ABAP and SAP HANA, Examples
SAP HANA, from ADBC to AMDP
This example demonstrates how a task can be performed using HANA-specific language elements.
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_from_adbc_to_amdp( 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
The task is to read all rows from the database table SFLIGHTS whose key fields MANDT, CARRID, and CONNID
occur in a predefined three-column internal table. To do this, the example program calls various methods
of the class CL_DEMO_FROM_ADBC_TO_AMDP in whose instance constructor the internal table connection_tab is filled in accordance with user input.
Note
The examples of using HANA-specific language elements shown in the methods are purely syntax examples. The task presented here can be solved just as well using Open SQL, which is why Open SQL is the preferred method as specified in the programming guidelines.
Reference Implementation Using Open SQL
The method OSQL shows how the task can be solved in Open SQL by simply using the addition
FOR ALL ENTRIES. The result of the method is used as a reference for the HANA-specific implementations.
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.
Using Native SQL with ADBC
The method ADBC solves the task using HANA-specific
Native SQL statements passed to the SAP HANA database using
ADBC. The table with the key values is evaluated after the addition
exists in a subquery of a select statement.
To do this, a temporary table DEMO_ADBC_CONNECTIONS is created on the
database and filled with the content of the connection_tab using the statement
insert. To access the result of the select
statement, a standard table std_flights must be declared as a local internal
table of the method, since the return value flights cannot be used for ADBC.
The temporary table DEMO_ADBC_CONNECTIONS is removed again after SPFLI is read.
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 ` &&
`( MANDT NVARCHAR(3), ` &&
`CARRID NVARCHAR(3), ` &&
`CONNID NVARCHAR(4) )` ).
sql->set_param_table( REF #( connection_tab ) ).
sql->execute_update(
|insert into DEMO_ADBC_CONNECTIONS values ( ?, ?, ? )| ).
DATA(result) = sql->execute_query(
` select * ` &&
` from SFLIGHT as S ` &&
` where exists (` &&
` select MANDT, CARRID, CONNID ` &&
` from DEMO_ADBC_CONNECTIONS as C` &&
` where C.MANDT = S.MANDT and ` &&
` C.CARRID = S.CARRID and ` &&
` C.CONNID = S.CONNID )` ).
DATA std_flights TYPE STANDARD TABLE OF sflight WITH EMPTY KEY.
result->set_param_table( REF #( std_flights ) ).
result->next_package( ).
result->close( ).
flights = std_flights.
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 a Database Procedure Using a Database Procedure Proxy
The method CDBP solves the task by implementing the HANA-specific select statement in a
database procedure called by specifying a
database procedure
proxy in the statement CALL
DATABASE PROCEDURE. In a more realistic example, the database procedure DEMO_ADBC_GET_FLIGHTS
and the database procedure proxy DEMO_ADBC_GET_FLIGHTS_PROXY would already exist and the implementation of the method would be restricted to the call CALL DATABASE PROCEDURE (compare with the example for
procedure calls). In this example, the database
procedure and the database procedure proxy are created temporarily using ADBC methods and the associated
API, then deleted again. The input parameter connections of the procedure
references an additional temporary HANA table type, DEMO_ADBC_CONNECTIONS_TYPE.
HANA does not support a separate type for numeric text, which means that the type NVARCHAR(4)
must be used for the column CONNID. Also, the internal table connection_tab
must be assigned to a temporary table connections with the correct row type
before the procedure is called. No auxiliary table is required for the result, 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.
DATA(sql) = NEW cl_sql_statement( ).
TRY.
sql->execute_ddl( 'drop type DEMO_ADBC_CONNECTIONS_TYPE' ).
CATCH cx_sql_exception ##no_handler.
ENDTRY.
TRY.
sql->execute_ddl( `drop procedure DEMO_ADBC_GET_FLIGHTS` ).
CATCH cx_sql_exception ##no_handler.
ENDTRY.
TRY.
sql->execute_ddl(
`create type DEMO_ADBC_CONNECTIONS_TYPE as table ` &&
`( MANDT NVARCHAR(3), ` &&
`CARRID NVARCHAR(3), ` &&
`CONNID NVARCHAR(4) )` ).
sql->execute_ddl(
`create procedure DEMO_ADBC_GET_FLIGHTS ` &&
` ( in CONNECTIONS DEMO_ADBC_CONNECTIONS_TYPE, ` &&
` out FLIGHTS SFLIGHT ) language sqlscript as ` &&
` 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` ).
DATA(params) =
VALUE if_dbproc_proxy_basic_types=>ty_param_override_t(
( db_name = 'FLIGHTS'
abap_name = 'FLIGHTS'
descr = cl_abap_typedescr=>describe_by_name(
'SFLIGHT' ) ) ).
DATA(api) = cl_dbproc_proxy_factory=>get_proxy_public_api(
if_proxy_name = 'DEMO_ADBC_GET_FLIGHTS_PROXY' ).
api->delete( ).
api->create_proxy( EXPORTING
if_proc_schema = 'SAP' && sy-sysid
it_param_override = params
if_proc_name = 'DEMO_ADBC_GET_FLIGHTS' ).
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.
CALL FUNCTION 'DB_COMMIT'.
api->delete( ).
sql->execute_ddl( `drop procedure DEMO_ADBC_GET_FLIGHTS` ).
sql->execute_ddl( 'drop type DEMO_ADBC_CONNECTIONS_TYPE' ).
CATCH cx_sql_exception cx_dbproc_proxy.
CLEAR flights.
ENDTRY.
ENDMETHOD.
AMDP Procedure Call
The method AMDP solves the task in the easiest possible way by calling an AMDP method AMDP_METH in which the HANA-specific select statement is implemented. The main advantage of the AMDP method is that it can be called in the same way as any ABAP method, without the need to implement any auxiliary tables (as in the preceding examples).
amdp_meth( EXPORTING connections = connection_tab
IMPORTING flights = flights ).
ENDMETHOD.
LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING sflight.
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 );
ENDMETHOD.