Execute Procedure with i/o parameters from ABAP

[jitin_kharbanda]

Hello,

We are using HANA as secondary database and I need to execute HANA Procedures from ABAP.

I have created a procedure in HANA with a parameter as input and table as output.

I executed it in HANA as

CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA"('MT02',?)

and it worked fine.

but I'm not able to execute it from ABAP.

My need is to pass parameter as input and get the result in a internal table.

Following is my code:-

DATA:  stmt_ref    TYPE REF TO cl_sql_statement,

        con_ref     TYPE REF TO cl_sql_connection,

        res_ref     TYPE REF TO cl_sql_result_set,

        d_ref       TYPE REF TO data,

        d_ref1      TYPE REF TO data,

        lv_mtart    TYPE zhmara-mtart,

        output      TYPE i.

lv_mtart = 'MT02'.

     con_ref = cl_sql_connection=>get_connection( 'ABC' ).

     stmt_ref = con_ref->create_statement( ).

GET REFERENCE OF lv_mtart INTO d_ref.

         stmt_ref->set_param( data_ref = d_ref

                              inout    = cl_sql_statement=>c_param_in ).

        stmt_ref->execute_procedure( proc_name =

                                '"_SYS_BIC"."xxx/RETREIVE_ZHMARA"' ).

GET REFERENCE OF lt_zhmara INTO d_ref1.

                                           res_ref->set_param_table( d_ref1 ).

* Get the complete result set in the internal table

         output = res_ref->next_package( ).

         LOOP AT lt_zhmara INTO ls_zhmara.

           WRITE : /, ls_zhmara-mandt,

                      ls_zhmara-matnr,

                      ls_zhmara-ersda,

                      ls_zhmara-ernam,

                      ls_zhmara-laeda,

                      ls_zhmara-mtart,

                      ls_zhmara-matkl,

                      ls_zhmara-meins.

         ENDLOOP.

This works fine till  stmt_ref->execute_procedure   , sy-subrc = 0.

but gives a dump later as  'res_ref' is not initialized hence has ZERO value.

How can I set this?

** set_param_table for output parameter is only available in class cl_sql_result_set  but what should be the reference for its object res_ref??

Kindly suggest...

[vairma]

Hi Jatin,

You can call procedure like this...

    lo_result = lo_sql->execute_query(

              |CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA"({ lv_mtart },null) WITH OVERVIEW|

          ).

----then results return by the procedure as an table type, you can retrieve it by  following code snippets

GET REFERENCE OF lt_table INTO lr_data.  -- where lt_table is the internal table  which is exactly similar to output structure return by the proc

    lo_result->set_param_table( lr_data ).

    lo_result->next_package( ).

    lo_result->close( ).

Hope this helps.

Best Regards,

Vaibhav

[yeushengteo]

Hi,

The lo_result object will not be able to capture the table parameter result by calling the procedure right since the procedure is not returning result set directly.

You will need a tempoary table to store the table parameter result first and then select the result back.

Regards.

YS

[jitin_kharbanda]

Hello Vaibhav,

I tried as u explained.

It gives me an exception

invalid column name: MT02: line 1 col 41 (at pos 40)

MT02 is the values that I need to pass as input parameter.

Kindly suggest.

[jitin_kharbanda]

Hello Yeu,

Thanks for your reply.

I'm not able to understand :-

how to create a temporary table to store the table parameter??

how to assign that table as output table of my procedure??

Kindly explain with an example

Thanks.

[vairma]

Hi Jatin,

Could you tell me the signature[ i mean input and output parameters]of your DB procedure.

Best Regards,

Vaibhav

[jitin_kharbanda]

Hello Vaibhav,

Thanks for your response.

I tried giving the value directly and without WITH OVERVIEW

  |CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA"('MT02',NULL)|

and it worked fine.

But I need to pass the input value through a variable, thus I tried

lv_mtart = '''MT02'''.

  |CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA"({ lv_mtart },NULL)|

and it worked fine.

I'm able to get the data into output table now.

[jitin_kharbanda]

Hi Vaibhav,

I created another procedure but this has 2 output parameters as tables:-

I tried

         res_ref = stmt_ref->execute_query( |CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA_4"({ lv_mtart },NULL, NULL)| ).

* Set output table

         GET REFERENCE OF lt_zhmara INTO d_ref.

         res_ref->set_param_table( d_ref ).

* Get the complete result set in the internal table

         output = res_ref->next_package( ).

         GET REFERENCE OF lt_zhmara_tmp INTO d_ref1.

         res_ref->set_param_table( d_ref1 ).

* Get the complete result set in the internal table

         output = res_ref->next_package( ).

lt_zhmara  is populated, but there is no data in lt_zhmara_tmp

Can you help me on this?

Thanks.

[vairma]

Hi Jatin,

This code snippet which you were using, retrieve outputs of the procedure, in your case output is two table type structure.

---------------------------------------------

      GET REFERENCE OF lt_zhmara INTO d_ref.

         res_ref->set_param_table( d_ref ).

* Get the complete result set in the internal table

         output = res_ref->next_package( ).

-----------------------------------------------------

After that what you have to do is to read the each output table type and then retrieve data from the each table type accordingly.Following are the code snippets for the same.

READ TABLE  lt_zhmara REFERENCE INTO lr_zhmara index 1.

lo_result = lo_sql->execute_query( |SELECT * FROM { lt_zhmara ->value } | ).

GET REFERENCE OF lt_ouput_table1 INTO lr_data.

lo_result->set_param_table( lr_data ).

lo_result->next_package( ).

lo_result->close( ).

READ TABLE  lt_zhmara REFERENCE INTO lr_zhmara index 2.

lo_result = lo_sql->execute_query( |SELECT * FROM { lt_zhmara ->value } | ).

GET REFERENCE OF lt_ouput_table2 INTO lr_data.

lo_result->set_param_table( lr_data ).

lo_result->next_package( ).

lo_result->close( ).

Hope this helps.

Best Regards,

Vaibhav

[jitin_kharbanda]

Vaibhav,

This is giving me an error

" "LT_ZHMARA" is not a reference variable "  at line

lo_result = lo_sql->execute_query( |SELECT * FROM { lt_zhmara ->value } | ).

I have declared  LT_ZHMARA as internal table of standard table which is my output parameter type.

so how to get the complete procedure output in a reference variable?

Kindly suggest.

[vairma]

Hi Jatin,

Sorry, that was a typo error. Please use lr_zhmara [Highlighted with Bold letters] instead of lt_zhmara .

Here is the Code Snippets.

TYPE : BEGIN OF zhmara_t,
param   TYPE string,
value   TYPE string,
END OF zhmara_t.

TYPES: BEGIN OF ls_ouput_table1 ,
* define fields as per your output structure from the procedure
END OF ls_ouput_table1 .

TYPES: BEGIN OF ls_ouput_table2 ,
* define fields as per your output structure from the procedure
END OF ls_ouput_table2 .

DATA:

lt_zhmara TYPE TABLE OF zhmara_t,

lr_zhmara TYPE REF TO zhmara_t.

DATA: lt_ouput_table1 TYPE STANDARD TABLE OF ls_ouput_table1 .
DATA: lt_ouput_table2 TYPE STANDARD TABLE OF ls_ouput_table2 .

DATA: lr_data TYPE REF TO data.

-------------------------------------------------------

    GET REFERENCE OF lt_zhmara INTO d_ref.

         res_ref->set_param_table( d_ref ).

   * Get the complete result set in the internal table

         output = res_ref->next_package( ).

**** in your case since you have two output tables returned by the procedure, so lt_zhmara contains only two rows, with each row  contains key value pair.

READ TABLE  lt_zhmara REFERENCE INTO lr_zhmara index 1.

lo_result = lo_sql->execute_query( |SELECT * FROM { lr_zhmara ->value } | ).

GET REFERENCE OF lt_ouput_table1 INTO lr_data.

lo_result->set_param_table( lr_data ).

lo_result->next_package( ).

lo_result->close( ).

READ TABLE  lt_zhmara REFERENCE INTO lr_zhmara index 2.

lo_result = lo_sql->execute_query( |SELECT * FROM { lr_zhmara ->value } | ).

GET REFERENCE OF lt_ouput_table2 INTO lr_data.

lo_result->set_param_table( lr_data ).

lo_result->next_package( ).

lo_result->close( ).

Let me know, if this works.

Best Regards,

Vaibhav

Message was edited by: Vaibhav Kumar

[jitin_kharbanda]

Thanks Vaibhav for your help and efforts, it is working now.

** Have to use WITH OVERVIEW to get results in reference variable like

|CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA_4"({ lv_mtart }, NULL, NULL) WITH OVERVIEW |

[jasmin_gruschke]

Hi Jitin,
examples of how to call a DB procedure using ADBC can be found in the demo report ADBC_DEMO_PROC_CALLS_HDB (available in any system with a minimum NetWeaver release 7.40 SP5).

Cheers,

  Jasmin

[oliver_sviszt]

Hi Jasmin,

Ist it pure ADBC code, that would also work with 7.30, or is it the famous CALL DATABASE PROCEDURE statement?

Unfortunately a lot of us are not on 7.40 yet, and it is not possible to migrate that quickly - but we still need the possibility to call HDB procedures with table input and output parameters. I have tried several approaches now, but it seems to be impossible - except by storing the input data in temporary tables.

Oliver

[jasmin_gruschke]

Hi Oliver,
the report is purely based on ADBC, not using the CALL PROCEDURE statement. But unfortunately I didn't include the "tabular input parameter" case in the example report.

However, your approach to work with temporary tables is to my knowledge *the only* or let's say *the best* way to work with tabular input parameters - tabular output parameters should not be problematic, right? So you're inserting into the temporary table and then call the HANA procedure (with tabular input parameter) using this temporary table as input parameter. But be aware that the data inside the temporary table is database session local, so a change in database session/ABAP work proccess (e.g. due to a commit work) in between temporary table insertion and DB procedure execution might lead to an empty input parameter.

Cheers,

  Jasmin

[oliver_sviszt]

Hi Jasmin,

A think "table in, table out" is what most people are interested in, to quickly process mass data in HANA.

In CL_SQL_STATEMENT there is an EXECUTE_PROCEDURE method, but I can't seem to get that working, it always leads to sy-subrc=8 in the internal C  call. According to my understanding this was already working with non-HANA databases? (Though I never tried calling procedures before HANA.)

I have now implemented the logic with putting the data to tables, but to be honest, I believe this solution to have a quite poor performance due to the thousands of separate insert statements. Is there any way to insert a whole internal tabel at a time?

Oliver

[jasmin_gruschke]

Hi Oliver,

maybe I was not quite clear, procedures with one tabular output parameters in HANA can be called via CL_SQL_STATEMENT, i.e. with the EXECUTE_QUERY method, retrieving a CL_SQL_RESULT_SET object from which you get the tabular output after binding an internal table via SET_PARAM_TABLE (of class CL_SQL_RESULT_SET).

On the other hand, if you would like to call a HANA procedure with a tabular input parameter, there is no mechanism (in ADBC) for setting input parameters via a similar method like the SET_PARAM_TABLE and then call the procedure. Currently, the only way is the one I skeched above.

The method EXECUTE_PROCEDURE does not return a CL_SQL_RESULT_SET object, so you cannot query on any output table. The method fits best if you have a procedure without parameters (don't ask about the historical reasons). And yes it's true, EXECUTE_PROCEDURE already worked with non-HANA databases, like anything else does within ADBC - it's native SQL, not "HANA" native SQL.

You said that you have questions about the performance of your implementation because of the separate insert statements. In this case you might not be aware of the "Array Insert" functionality. Depending on your release you're working with, you should be able to see the method SET_PARAM_TABLE in your CL_SQL_STATEMENT class. If yes, you can insert into the temporary table the whole internal table with just one insert statement. An example:

DATA lo_sql    TYPE REF TO cl_sql_statement.
DATA lt_insert TYPE STANDARD TABLE OF ZTABLE.
DATA ls_insert LIKE LINE OF lt_insert.
DATA lv_rc      TYPE i.

DATA lr_insert TYPE REF TO data.

CREATE OBJECT lo_sql EXPORTING tab_name_for_trace = 'ZTABLE'.

GET REFERENCE OF lt_insert INTO lr_insert.

lo_sql->set_param_table( lr_insert ).

DO 50 TIMES.
  ls_insert-col1 = sy-index.
  ls_insert-col2 = sy-index.
  ls_insert-col3 = sy-index.
  APPEND ls_insert TO lt_insert.
ENDDO.

lv_rc = lo_sql->execute_update( `INSERT INTO ZTABLE VALUES (?, ?, ?)` ).

Let me know if you don't have the class in your system, I'll look up what is the minimal release required.

Cheers,

  Jasmin

[oliver_sviszt]

Hi Jasmin,

I was hoping that EXECUTE_PROCEDURE would put the OUT paramater data automatically into the bound tables once the procedure was executed. That way no result set object would be required anyway.

Thank you for the hint with SET_PARAM_TABLE. Unfortunately we do not have this method yet - our patch Level is 7.30 SP09. I could not find any notes with correction instructions providing this method either, so I guess we have lost this battle and have to wait until next year, when we upgrade to 7.40.

Oliver

[jasmin_gruschke]

Hi Oliver,
unfortunately not, EXECUTE_PROCEDURE cannot help you there.

"Lost the battle"? You can check for SAP note 1757012. But I think you're right, the note is only valid for SAP_BASIS 731, Kernel 7.21.

Cheers,

  Jasmin

[eric_zhu]

Hello Jasmin,

As you mentioned,

"

procedures with one tabular output parameters in HANA can be called via CL_SQL_STATEMENT, i.e. with the EXECUTE_QUERY method, retrieving a CL_SQL_RESULT_SET object from which you get the tabular output after binding an internal table via SET_PARAM_TABLE (of class CL_SQL_RESULT_SET).

"

but I have ever seen in other material said that, we cannot use set_param_table to get the result set from EXECUTE_QUERY, just like Yeu Sheng Teo replied in this page, "You will need a tempoary table to store the table parameter result first and then select the result back."

so you mena it is possible to get the tabular result set direclty without a tempoary table anymore,  right?

May I know it is always possible from the begining or some changes has made it possible?

Maybe I just got the wrong information or I misunderstood...

thanks,

Eric

[jasmin_gruschke]

Hi Eric,

yes it's true. For procedures with only one tabular output parameter, you can call the procedure using EXECUTE_QUERY. The retrieved CL_SQL_RESULT_SET object can be used to bind an internal table using SET_PARAM_TABLE and the NEXT_PACKAGE method then retrieves the output into the internal table.

If the procedure has more than one tabular output parameters, you can still call the procedure with EXECUTE_QUERY, however, the retrieval of the output is a bit more cumbersome. You can use the statement "CALL ... WITH OVERVIEW" to call the procedure. The retrieved CL_SQL_RESULT_SET then contains this "overview" table comprising the names of the temporary tables wherin the result is stores. You can then query each of the temporary tables with "SELECT ... <temp_table_name>".

Concerning you comment "maybe there have been changes" let me just say "not to my knowledge"

Cheers,

  Jasmin

[oliver_sviszt]

Hi Jasmin,

I think with 7.30 my current solution with the temporary tables and single inserts is the best I can achieve, but I hope next year with 7.40 we will "win the war"

Thank you for all your help and the fast responses - I find it very positive that more and more SAP employees get involved into community forums. The forum entries are more useful than the documentation, and I hope the discussions also provide feedback which features would be most welcome in the next releases.

Oliver

[Former Member]

Hi Jitin,

I am connecting Amazon Web Service and calling Stored Procedure.

i have 2 output Parameter and 1 Input Parameter

Code to call Stored Procedure

  lr_conn = cl_sql_connection=>GET_CONNECTION( 'AWS' ) .

  lr_sql_statement = lr_conn->CREATE_STATEMENT( ) .

ls_num = '123' .

lr_sql_result = lr_sql_statement->EXECUTE_QUERY( |CALL Schemaname"."StoredProcName"({ ls_num },NULL,NULL) WITH OVERVIEW | ) .

At this statement i am getting error "expression cannot be used as an assignment target: NULL: line 1 col 52 (at pos 51)" .

My Stored Procedure has 3 Parameter

Could you please share your code or example how i can resolve issue.

Thanks,
Manish

[christian_seitel]

Hi, I think you are missing the single quotes of the ls_num parameter.

see code snippet above; three consecutive single quotes are used here:

lv_mtart = '''MT02'''.

  |CALL "_SYS_BIC"."xxx/RETREIVE_ZHMARA"({ lv_mtart },NULL)|

[Former Member]

Hi Christian,

Thanks for your reply.

I changed code still I am getting same error Message "invalid  Column Name: 1101001010 : line 1 col41 "

Below is stored procedure column names and logic

[jasmin_gruschke]

Hi Manish,
one side remark, you might want to try the same call on the SQL console (and see whether the syntax works).
You can find examples on how to call procedures with input/output parameters in report ADBC_DEMO_PROC_CALLS_HDB (as mentioned above).

Yet, your issue above is that you use " instead of ', so you may want to use BP_NUM='''123''' (with three single quotes).

Best,
  Jasmin

[Former Member]

Hi Jasmin,

I Tried with 3 Single quotes , still the same.

Referring to example ADBC_DEMO_PROC_CALLS_HDB , there are no example with 1 Input Parameter and 2 Output Parameter.

I tried using SET_PARAM didn't worked.

Any Piece of code will help. Below is my code.

[christian_seitel]

I am not sure whether the problem is caused by the varchar data type instead of nvarchar.

In my system the following code snippet works:

  DATA(lv_param) = '''Abc'''.

  DATA(lv_sql) = |CALL "ZCL_AMDP_EXEC_SQL=>AMDP_DP_SIGNATURE"({ lv_param },NULL,NULL)|.

  TRY.

      DATA(lo_result) = NEW cl_sql_statement( )->execute_query( lv_sql ).

[Former Member]

Hi ,

I changed the type from Varchar to Nvarchar and I tried in SQL Console , I get the result.

Below code is still giving me error " expression cannot be used as an assignment target:NULL:line 1 col54(at pos 53) .

Statement CALL "CI_DEV_USER"."GET_PREDICT_RESULT"('1101001010',NULL,NULL)

Below is code

[horst_keller]

Wouldn't it be worth a try to use AMDP instad of ADBC, i.e. encapsulating the call of the native stored procedure inside an AMDP method? Syntax of the native SQL statements is checked better there.

See Calling an SQLScript Procedure from AMDP or SAP HANA, Call Database Procedure.

See also SAP HANA, from ADBC to AMDP.

[christian_seitel]

Hi,

there are too many single quotes now: both at lv_num-definition and at lv_sql-definition the actual value is wrapped by single quotes.