
Part 3: http://scn.sap.com/community/abap/hana/blog/2014/01/08/as
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |