Execute Procedure with input parameters as table from ABAP

[jitin_kharbanda]

Hello Experts,

I need to get the data from HANA stored procedure through ABAP but one of the input parameter is a table.

Kindly suggest how can we pass an internal table as input parameter to a procedure.

Also, how to execute this procedure on HANA.

Thanks.

[vairma]

Hi Jatin,

If HANA is a primary database then you can create "DATABASE PROCEDURE PROXY" at the ABAP side from the corresponding HANA stored procedure at the backend.

Then call the same database proxy at ABAP as following

CALL DATABASE PROCEDURE  DB_PROXY( EXPORTING input_table = lt_in_table  importing output_table = lt_out_table).

where DB_PROXY is the name of DATABASE PROCEDURE PROXY

lt_in_table is an ABAP internal table, in your case, probably you can fill this ABAP internal table with your input parameters .

lt_out_table  is an ABAP internal table.

Hope this helps.

Best Regards,

Vaibhav

[vairma]

Hi Jatin,

You can also refer this link for more info on "DATABASE PROCEDURE PROXY"

[http://scn.sap.com/community/abap/eclipse/blog/2013/05/12/consuming-existing-hana-artifacts-in-abap-...

Best Regards,

Vaibhav

[jitin_kharbanda]

Hello Vaibhav,

We are using HANA as secondary DB, thus database procedure proxy will not work.

Kindly suggest.

Thanks.

[yeushengteo]

Hi,

The simpliest workaround (not sure if efficient) will be create global temporary column table to store the ABAP internal table data. This temporary table will be used to hand data into your procedure.

So you always need to perform the following order:

1. truncate data in your global temporary table in HANA;

2. insert ABAP internal table data into the temporary table in HANA;

3. call your procedure using the global temporary table as input table

Regards.

YS

[jitin_kharbanda]

Hi Yeu,

Can you please explain in detail with an example or code, how to achieve this?

Thanks

[yeushengteo]

Hi,

something like this (syntax wise you adjust it accordingly):

* HANA temporary table is already created and flushing out table record

lo_sql->execute_ddl( 'TRUNCATE TABLE "HANA_INPUT_TABLE"' ).

lo_sql->execute_ddl( 'TRUNCATE TABLE "HANA_OUTPUT_TABLE"' ).

* bring abap internal table record into temporary table

LOOP AT abap_it_table INTO lv_tab_content
  lo_sql->execute_update( 'INSERT INTO "HANA_INPUT_TABLE" VALUES(''' && lv_tab_content &&''')').
ENDLOOP.

* call your own procedure which processed your business logic

lo_result = lo_sql->execute_query( 'CALL "your.procedure"( "HANA_INPUT_TABLE", "HANA_OUTPUT_TABLE" ) WITH OVERVIEW ' ).

lo_result->close( ).

* Get the data from output table (optional depending on your requirement)

lo_result = lo_sql->execute_query( 'SELECT * from "HANA_OUTPUT_TABLE"' ).

Regards.

YS

[jitin_kharbanda]

Hello Yeu,

This solution will work but it doesn't seems too efficient.

Does it mean that whenever we have to execute a procedure with table as input, we have to first create a DB table in HANA, populate it with the required input data and then pass it as input parameter to the HANA Procedure?

Also, if multiple users try to call the procedures at the same time, then HANA_INPUT_TABLE will be truncated each time. This may result in data loss for other users.

Kindly suggest.

Thanks.

[jasmin_gruschke]

Hi Jitin,
short comment on your reply to Vaibhavs message. Depending on the NetWeaver version you are working with (i.e. with a minimum release 7.40 SP2) you can access DB Procedure Proxies via a Secondary DB Connection. Just have a look at the ABAP documentation of the statement "CALL DATABASE PROCEDURE <proxy>" with the optional statement "CONNECTION <con>".

Cheers,

  Jasmin

[jasmin_gruschke]

Hi Jitin,
which NW release are you working with? Maybe the new ABAP managed DB procedures are useful for your needs? However, in the end the AMDPs are doing exactly the same job (but transparent for the user).

There is afaik right now no possiblity to use a tabular input parameter for a DB Procedure in HANA.

Cheers,

  Jasmin

[jitin_kharbanda]

Hello Jasmin,

Thanks for your reply. I'm working with NW 7.40 release.

CALL DATABASE PROCEDURE <proxy> will not work for me as I'm not able to create DB Proxy in HANA.

Whenever I try to create one, it gives me an error message

"Database Procedure Proxies can only be created on ABAP systems running on a NewDB"

What I understand from this message is to create DB proxy in HANA, it should be primary database. Whereas in my case, primary DB is ORACLE and we are using HANA as secondary DB by creating connection in DBCON and using native SQL to retreive data.

Though, I'm now able to pass table as input parameter but I still want to know how can I use database procedure proxies?

Kindly suggest.

Thanks

Jitin Kharbanda

[yeushengteo]

Hi Jitin,

Use global temporary table, this table data is private to the connecting session only.

Regards.

YS

[yeushengteo]

Hi Jitin,

You need to use SAP HANA as the primary database to create the Database Procedure Proxy. No other way round it.

Regards.

YS

[jitin_kharbanda]

Thanks Yeu,

I understand that but how to do this?

Any note, document or blog that you can refer please?

Regards

Jitin

[yeushengteo]

Check this document link http://scn.sap.com/docs/DOC-41604.

Regards.

YS

[jitin_kharbanda]

Yeu, This document explains how to execute HANA procedures from ABAP through database procedure proxy. For this DB Proxy first must be created through HANA but I'm not able to do so as I'm not able to create Database Procedure Proxy through HANA.

I'm able to retrieve from HANA using DB connections, but can I use DB proxy at all?

** we are using HANA as side car approach, SAP NW 7.40.

[yeushengteo]

As mentioned earlier in all previous replies.. no other way round it without having a HANA database as primary database.

Side car approach is not considered for database procedure proxy.

Regards.

YS

[jitin_kharbanda]

Thanks for clarification Yeu.

[jasmin_gruschke]

Hi YS,
not entirely true. You cannot create the DB procedure proxy via the the "standard tools", i.e. the ADT in the eclipse. In order to create the DB procedure proxy if HANA is not the primary DB but a secondary DB you need to create the proxy programatically. Once this is done, you can use the CALL DATABASE PROCEDURE.

Cheers,

  Jasmin

[jitin_kharbanda]

Hello Jasmin,

Please elaborate.

how to create DB proxy programatically when using HANA as secondary database.

Thanks

[yeushengteo]

Hi Jasmin,

My layman interpretation of the proxy creation - ADT faciltate the linkage of the ABAP application server with the HANA DB which allows us to create the proxy. For this case, ABAP apps server is hosting the HANA as primary DB.

In a side car approach, there is no ABAP apps server but only a standalone HANA DB. In such situation, only possibility is creating the proxy natively in HANA without using ABAP. If this is feasible, please share the know-how. It will benefits us...

Thanks.

YS

[jasmin_gruschke]

Hi YS,
side car approach still means there's an AS ABAP which can connect to the HANA DB via secondary DB connection. In this case proxy creation is still feasible but not via the ADT approach (the nice and easy one) but still there is the possiblility to do this programatically. I'll reply to Jitin with a solution how.

Cheers,

  Jasmin

[jitin_kharbanda]

Hello Jasmin,

If DB proxy via secondary DB connection is possible, then I'm eagerly waiting for a solution

Thanks and Regards

Jitin

[jasmin_gruschke]

Hi Jitin,
please have a look at the ABAP Keyword Documentation (F1 help in the ABAP System). You will find for "CALL DATABASE PROCEDURE" a paragraph:

Example

The example Database Procedure Call uses a
database procedure proxy created in the program to call a database procedure
created using ADBC.

Navigating to "Database Procedure Call" you will find the example for creating a proxy programatically, i.e.

DATA(api) = cl_dbproc_proxy_factory=>get_proxy_public_api( if_proxy_name = prox_name ).

api->create_proxy( EXPORTING  if_proc_schema    = '_SYS_BIC'

                                                      it_param_override = params

                                                      if_proc_name        = proc_name ).

Hope this example suits your needs.

Cheers,

  Jasmin