Skip to Content

Execute Procedure with i/o parameters from ABAP

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...

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    Posted on Nov 12, 2013 at 09:03 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.


  • Posted on Nov 12, 2013 at 11:01 AM

    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.


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 27, 2013 at 08:37 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 13, 2015 at 02:32 PM

    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


    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 14, 2015 at 03:37 PM

    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.


    code.JPG (37.6 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • 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 ).

  • author's profile photo Former Member
    Former Member
    Posted on Oct 15, 2015 at 03:59 PM

    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


    SQL Console.JPG (31.2 kB)
    CODE1.JPG (31.1 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.