topic Re: Execute Procedure with input parameters as table from ABAP in Application Development and Automation Discussions

Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Wed, 13 Nov 2013 12:17:35 GMT

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.

Re: Execute Procedure with input parameters as table from ABAP

vairma — Wed, 13 Nov 2013 12:45:14 GMT

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

Re: Execute Procedure with input parameters as table from ABAP

vairma — Wed, 13 Nov 2013 13:02:39 GMT

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-is-easy]

Best Regards,

Vaibhav

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Thu, 14 Nov 2013 03:23:54 GMT

Hello Vaibhav,

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

Kindly suggest.

Thanks.

Re: Execute Procedure with input parameters as table from ABAP

yeushengteo — Thu, 14 Nov 2013 07:49:00 GMT

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.

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Thu, 14 Nov 2013 08:03:57 GMT

Hi Yeu,

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

Thanks

Re: Execute Procedure with input parameters as table from ABAP

yeushengteo — Thu, 14 Nov 2013 09:26:09 GMT

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.

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Thu, 14 Nov 2013 09:58:39 GMT

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.

Re: Execute Procedure with input parameters as table from ABAP

jasmin_gruschke — Tue, 26 Nov 2013 14:52:52 GMT

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

Re: Execute Procedure with input parameters as table from ABAP

jasmin_gruschke — Tue, 26 Nov 2013 14:58:53 GMT

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

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Wed, 27 Nov 2013 05:30:39 GMT

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

Re: Execute Procedure with input parameters as table from ABAP

yeushengteo — Wed, 27 Nov 2013 05:50:00 GMT

Hi Jitin,

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

Regards.

Re: Execute Procedure with input parameters as table from ABAP

yeushengteo — Wed, 27 Nov 2013 06:41:26 GMT

Hi Jitin,

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

Regards.

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Wed, 27 Nov 2013 06:47:01 GMT

Thanks Yeu,

I understand that but how to do this?

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

Regards

Jitin

Re: Execute Procedure with input parameters as table from ABAP

yeushengteo — Wed, 27 Nov 2013 06:54:25 GMT

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

Regards.

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Wed, 27 Nov 2013 07:11:08 GMT

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.

Re: Execute Procedure with input parameters as table from ABAP

yeushengteo — Wed, 27 Nov 2013 07:39:51 GMT

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.

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Wed, 27 Nov 2013 08:07:10 GMT

Thanks for clarification Yeu.

Re: Execute Procedure with input parameters as table from ABAP

jasmin_gruschke — Wed, 27 Nov 2013 10:38:20 GMT

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

Re: Execute Procedure with input parameters as table from ABAP

jitin_kharbanda — Wed, 27 Nov 2013 12:02:22 GMT

Hello Jasmin,

Please elaborate.

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

Thanks