Can some one help me to call a Procedure in HANA from a transformation

[Hari_Krishana]

HI Experts,

Can some one help me how to call a SQL procedure in HANA  from  ABAP.

I have the Below logic but the when I call  the procedure using   lr_sql->execute_procedure( proc_name  ). it goes to a Dump as sy-subrc returned is 8.

Any inputs as to why it is failing

here is the code

DATA: itab_vbrk type standard table of VBRK.

     data: lr_sql type ref to cl_sql_statement,

           l_vbeln type VBAK-vbeln.

        data: lr_vbrk type ref to  data.

        DAta: proc_name(50) type c.

     create object lr_sql

       exporting

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

   l_vbeln = '0090012007'.

       data lr_vbeln type ref to data.

      get reference of l_vbeln into lr_vbeln.

      lr_sql->set_param( data_ref = lr_vbeln inout = cl_sql_statement=>c_param_in ).

     data: itab_vbrk2 type ref to data.

     get reference of itab_vbrk into itab_vbrk2.

        lr_sql->set_param( data_ref = itab_vbrk2 inout = cl_sql_statement=>c_param_out ).

  proc_name = '"XXXX."PR_VBRK"'.(XXX Refers to SCHEMA name )

        DATA lr_result TYPE REF TO cl_sql_result_set.

    lr_sql->execute_procedure( proc_name  ).

[sagarjoshi]

Try with method specified in

http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

You can use following code

    lv_query in this would be concatenated string value for calling of procedure as if you would have done from HANA studio

    e.g. CALL "SCHEMA".PROCEDURE ( 'INPUT_PARAMETER_VALUE', NULL)

NULL is specified for your table output parameter

DATA: sql_result       TYPE REF TO cl_sql_result_set.

   sql_result = lr_sql->execute_query( lv_query ).

   GET REFERENCE OF lt_output INTO dref.

   sql_result->set_param_table( itab_ref = dref

                                corresponding_fields = cols ).

* Output

   IF sql_result->next_package( ) > 0.

     LOOP AT lt_output INTO lwa_output.

*       do something in ABAP

     ENDLOOP.

   ENDIF.

[Hari_Krishana]

Thanks a lot Sagar for your Valuable inputs i may still need your help out here here is the code that I tried after your inputs

where in PC_SCHEMA is the Schema name i have in HANA and PR_VBRK is the Procedure that I have created  in HANA.

   when  sql_result = lr_sql->execute_query( lv_query ) gets executed it runs into Short dump with sy subrc 8. is it some thing related to  access issues or some thing  before that i opened a ADBC conncetion  by using

create object lr_sql

       exporting

         con_ref = cl_sql_connection=>get_connection( 'IBM' )

My requirement is simple I have a table VBRK in Hana  I would like to access the table in SAP BW and use for all Entries and get only data which is Required so can I use it in a BW transformation. using a Procedure  or if there is any other way also I am fine with it

here is the sample code am using.

   DATA: sql_result       TYPE REF TO cl_sql_result_set,

          lv_query type string,

           dref      TYPE REF TO DATA,

                 COLS Type  ADBC_COLUMN_TAB.

    lv_Query = 'call  "PC_SCHEMA".PR_VBRK ( l_vbeln , NULL)'.

   sql_result = lr_sql->execute_query( lv_query ).

      GET REFERENCE OF itab_vbrk  INTO dref.

       sql_result->set_param_table( itab_ref = dref

                                corresponding_fields = cols ).

[sagarjoshi]

Please tell what is the dump talking about.

One obvious mistake is

lv_Query = 'call "PC_SCHEMA".PR_VBRK ( l_vbeln , NULL)'.

You should use CONCATENATE statement to generate this string and not put l_vbeln directly

The generated string should look like

call PC_SCHEMA.PR_VBRK ( 'Value of l_vbeln' , NULL)

Also make sure that you have provided schema access to PC_SCHEMA to user defined in your connection.

If your requirement is simple then you can also fire direct SELECT statement on your table using the same code. Just instead of procedure generate the lv_query such that it has appropriate string for SELECT statement

[Hari_Krishana]

Hi Sagar,

I will check this out if you dont mind can you help me with a simple Select  statement with for all entries on VBRK(in HANA) where VBELN in SAP BW EQ VBELN in HANA and fetch the data into a internal table.