Consuming HANA Views, Procedures, External Views in ABAP 7.40 Syntax – Part 3

Part 1: http://scn.sap.com/community/abap/hana/blog/2014/01/08/consuming-hana-views-procedures-external-views-in-abap-740-syntax–part-1

• ABAP Report with new data declaration syntaxes on 7.40
• ABAP Report on HANA using ADBC

Part 2:http://scn.sap.com/community/abap/hana/blog/2014/01/08/consuming-hana-views-procedures-external-views-in-abap-740-syntax–part-2

• Consuming Attribute View using External View.
• Consuming Attribute View using Native SQL
• Consuming Analytic View/Calculation View in ABAP

Part 3: http://scn.sap.com/community/abap/hana/blog/2014/01/08/as

• Consuming HANA artifact Stored Procedure using ABAP Proxy Procedure.
• Consume HANA artifact Stored Procedure by Calling it in ABAP Code.

T6. Consuming HANA artifact Stored Procedure using ABAP Proxy Procedure.

Steps to create Stored Procedure:

Step 1: Go to ‘Modeler’ or ‘SAP HANA Development ‘prospective and right click on your package and create a new procedure.

Step 2:  Enter name and description, select the ‘Default Schema’ (SAP-SID) and Run With as ‘Invoker’s Right’ and click on finish.

Step 3: Add ‘Output’ and ‘Input’ parameters by right clicking on the respective folders

Step 4: Place the code in the procedure editor

BEGIN

et_last_rev_bill = SELECT empinfo.pernr as PERNR, DAYS_BETWEEN(empbill.BILL_DATE,CURRENT_UTCDATE) AS LAST_REV_BILL

                     FROM DTAB_EMP_INFO AS empinfo INNER JOIN DTAB_EMP_BILL AS empbill

                       ON empinfo.pernr = empbill.pernr

                    WHERE empbill.mandt = empinfo.mandt

                    GROUP BY empinfo.pernr, empbill.bill_date ;

END;

/********* End Procedure Script ************/

Step5: Save and activate the procedure. Execute the procedure in ‘SQL Console’.

Call “_SYS_BIC”.“mohas97_ha5/ZEMP_BILL_PP”(?) WITH OVERVIEW;

Call “_SYS_BIC”.“mohas97_ha5/ZEMP_BILL_PP”(?);

Now create a Proxy Procedure in ABAP:

Step 1: Go to ‘ABAP’ prospective.

Step 2:  Right click on ABAP package under which you want to create this Proxy Procedure. Under ‘New’ click on ‘Other ABAP Repository Object’

Step 3: Choose ‘Database Procedure Proxy’.

Step 4: Enter name, description, HANA s procedure name

Step 5: Now your proxy procedure is created just activate the object.

Source code to Consume the ‘Proxy Procedure’  in ABAP:

  DATA: lt_bill_rev_days TYPE TABLE OF if_emp_bill_proxy_procedure=>et_last_rev_bill,

        lv_count     TYPE i.

  CALL DATABASE PROCEDURE emp_bill_proxy_procedure

IMPORTING et_last_rev_bill = lt_bill_rev_days.

  LOOP AT lt_bill_rev_days ASSIGNING FIELD-SYMBOL(<fs_bill_rev_days>).

    WRITE: / ‘Days Since Bill Rate is changed’ ,<fs_bill_rev_days>-pernr.

    WRITE: ‘=’, <fs_bill_rev_days>-last_rev_bill , /.

  ENDLOOP.

Output:

T7. Consume HANA artifact Stored Procedure by Calling it in ABAP Code.

Source Code

* Calling hana procedure in ABAP
TYPES: BEGIN OF lty_s_overview,
param TYPE string,
value TYPE string,
END OF  lty_s_overview.

DATA: lt_overview TYPE  TABLE OF lty_s_overview.
DATA: ls_overview TYPE  lty_s_overview.

TRY.

lv_sql = | CALL _SYS_BIC.“mohas97_ha5/ZEMP_BILL_PP”  | &&
| ( null ) WITH OVERVIEW |.

*     execute the native SQL query/ SQL Call
lo_result = NEW cl_sql_statement( )->execute_query( lv_sql ).   ” new syntax

*     read the result into the internal table lt_partner
GET REFERENCE OF lt_overview INTO lr_data.
lo_result->set_param_table( lr_data ).  “Retrieve result of native SQL call
lo_result->next_package( ).
lo_result->close( ).

* Read internal table
READ TABLE lt_overview INTO ls_overview WITH KEY param = ‘ET_LAST_REV_BILL’.
lv_sql = ` select * from ` && ls_overview–value.
*     execute the native SQL query/ SQL Call
lo_result = NEW cl_sql_statement( )->execute_query( lv_sql ).   ” new syntax

*     read the result into the internal table lt_partner
GET REFERENCE OF lt_bill_rev_days  INTO lr_data.
lo_result->set_param_table( lr_data ).  “Retrieve result of native SQL call
lo_result->next_package( ).
lo_result->close( ).

CATCH cx_sql_exception INTO lx_sql_exc.
lv_text = lx_sql_exc->get_text( ).
MESSAGE lv_text TYPE ‘E’.

ENDTRY.

LOOP AT lt_bill_rev_days ASSIGNING FIELD–SYMBOL(<fs_bill_rev_days>).
WRITE: / ‘Days Since Bill Rate is changed::’ ,<fs_bill_rev_days>–pernr.
WRITE: ‘::’, <fs_bill_rev_days>–last_rev_bill , /.
ENDLOOP.