Procedure OUT param as Table

[italo_naia]

Hi all,

How to call a procedure with out parameter type table using ADBC?

I tried with scalar out paramenter and it's works.

      l_stmt_ref->set_param( data_ref = l_dref  inout = cl_sql_statement=>c_param_out ).
       l_stmt_ref->execute_procedure( l_stmt ).

is It impossible?

Thank you,

[italo_naia]

Hi,

with ADBC (which is the only option in ABAP < 7.4): you can use temporary tables to pass tabular parameters. Roughly the proeeding for tabular output parameters is

• CREATE GLOBAL / LOCAL TEMPORARY ROW TABLE... (with the struture of the tabular parameter)
• call your procedure, e.g. CALL "XXXX"(.... [name of temporary table]) WITH OVERVIEW
• select the result from the temporary table SELECT * FROM [name of temporary table]

In ABAP 7.4: you will be able to work with so-called database procedure proxies.

These internally also work with temporary tables. But this is transparent for the developer. He can use the new CALL DATABASE PROCEDURE statement.

Hope that helps a bit. Best regards

Thorsten

[yeushengteo]

Hi,

I think syntax wise should be feasible since the procedure simply returns whatever you coded in the parameter.

DATA: l_table_wa TYPE <table>,        <= your table name

            l_dref TYPE REF TO DATA.

...

  GET REFERENCE OF l_table_wa INTO l_dref.

  l_stmt_ref->set_param( data_ref = l_dref  inout = cl_sql_statement=>c_param_out ).

  l_stmt_ref->execute_procedure( proc_name = 'XXXX'). <= XXX is your precedure name

You have something like above?

Regards.

YS

[schneidertho]

Hi,

with ADBC (which is the only option in ABAP < 7.4): you can use temporary tables to pass tabular parameters. Roughly the proeeding for tabular output parameters is

• CREATE GLOBAL / LOCAL TEMPORARY ROW TABLE... (with the struture of the tabular parameter)
• call your procedure, e.g. CALL "XXXX"(.... [name of temporary table]) WITH OVERVIEW
• select the result from the temporary table SELECT * FROM [name of temporary table]

In ABAP 7.4: you will be able to work with so-called database procedure proxies.

These internally also work with temporary tables. But this is transparent for the developer. He can use the new CALL DATABASE PROCEDURE statement.

Hope that helps a bit. Best regards

Thorsten

[italo_naia]

I'll try using "with orveview" and Select from temporary table.

Thanks Thorsten

[RichHeilman]

Here is an example of how to handle out parameters when calling a procedure via ADBC.

TYPES: BEGIN OF t_sbook,

         carrid     TYPE sbook-carrid,

         connid     TYPE sbook-connid,

         fldate     TYPE sbook-fldate,

         bookid     TYPE sbook-bookid,

         customid   TYPE sbook-customid,

       END   OF t_sbook.

DATA lt_bookings TYPE STANDARD TABLE OF t_sbook.

DATA lv_rows TYPE i.

DATA lv_output TYPE string.

DATA lr_bookings TYPE REF TO data.

DATA lr_sql TYPE REF TO cl_sql_statement.

DATA lr_result TYPE REF TO cl_sql_result_set.

DATA lr_exception TYPE REF TO cx_sql_exception.

FIELD-SYMBOLS: <ls_bookings> LIKE LINE OF lt_bookings.

PARAMETER p_dbcon TYPE dbcon-con_name DEFAULT 'AB1'.

PARAMETER p_carrid TYPE sbook-carrid DEFAULT 'LH'.

* Create SQL Statement object

CREATE OBJECT lr_sql

  EXPORTING

    con_ref = cl_sql_connection=>get_connection( p_dbcon ).

* Set reference objects

GET REFERENCE OF lt_bookings INTO lr_bookings.

TRY.

* Create temp table type

    lr_sql->execute_query( `CREATE TYPE tt_booking_list AS TABLE (` &&

                             `carrid nvarchar(3),` &&

                             `connid numeric(4),`  &&

                             `fldate date,`  &&

                             `bookid numeric(8),`  &&

                             `customid numeric(8) )` ).

* Create temp table to hold results

    lr_sql->execute_query( `create global temporary table booking_list like tt_booking_list` ).

* Call procedure, passing input parameter

    lr_sql->execute_query( |call "_SYS_BIC"."sap.sflight/GET_BOOKINGS"('{ p_carrid }', booking_list) with overview| ).

* Query result table

    lr_result = lr_sql->execute_query( statement  =  `SELECT * FROM BOOKING_LIST`  ).

    lr_result->set_param_table( lr_bookings ).

    lr_result->next_package( ).

    lr_result->close( ).

* Drop tamp objects

    lr_sql->execute_query( `drop table booking_list` ).

    lr_sql->execute_query( `drop type tt_booking_list` ).

  CATCH cx_sql_exception INTO lr_exception.  " Exception Class for SQL Error

    lr_sql->execute_query( `drop table booking_list` ).

    lr_sql->execute_query( `drop type tt_booking_list` ).

ENDTRY.

WRITE: / 'Carrier ID:', p_carrid.

lv_output =  |Number of Rows: { lines( lt_bookings ) } | .

WRITE:/ lv_output.

LOOP AT lt_bookings ASSIGNING <ls_bookings>.

  WRITE:/ <ls_bookings>-carrid, <ls_bookings>-connid, <ls_bookings>-fldate,

          <ls_bookings>-bookid, <ls_bookings>-customid.

ENDLOOP.

Cheers,

Rich Heilman

[UweFetzer_se38]

And this is an example without temporary tables (tested under 7.40 with HANA as secondary database):

TYPES: BEGIN OF ts_price,
         productid TYPE c LENGTH 10,
         category  TYPE c LENGTH 40,
         price     TYPE c LENGTH 20,
         saleprice TYPE c LENGTH 20,
       END OF ts_price.
DATA lt_price  TYPE STANDARD TABLE OF ts_price.
DATA lr_price  TYPE REF TO data.
DATA lr_sql    TYPE REF TO cl_sql_statement.
DATA lr_result TYPE REF TO cl_sql_result_set.
GET REFERENCE OF lt_price INTO lr_price.
CREATE OBJECT lr_sql
  EXPORTING
    con_ref = cl_sql_connection=>get_connection( 'AWS' ).
TRY.
    lr_result = lr_sql->execute_query(
      |call _SYS_BIC."opensap.hana.models.procedures/get_product_sale_price"( 'HT-1000', NULL)|
      ).
    lr_result->set_param_table( itab_ref = lr_price ).
  CATCH cx_sql_exception cx_parameter_invalid .
    RETURN.
ENDTRY.
IF lr_result->next_package( ) > 0.
  READ TABLE lt_price INDEX 1 ASSIGNING FIELD-SYMBOL(<ls_price>).
  WRITE:/ <ls_price>-saleprice, <ls_price>-category, <ls_price>-price, <ls_price>-saleprice.
ENDIF.

[UweFetzer_se38]

Or of course the 7.40 way

DATA(lr_sql) = NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection( 'AWS' ) ).

[former_member459016]

Hi,

have you resolved this problem?

Can you show me the code ABAP, please?

[jasmin_gruschke]

Hi Alessandro,
do you have the demo report ADBC_DEMO_PROC_CALLS_HDB in your system? If not, please let me know your usecase, i.e. how many tabular output parameters for your procedure you're seeking for.

Cheers,

Jasmin

[italo_naia]

Yes, like Thorsten Schneidetold .

I create Global Temporary Table and call de procedure :

l_stmt = 'call "procedure"(<global_table_name>) with overview'.
 l_stmt_ref->execute_query( l_stmt ).

After select from global temporary table:

l_stmt = 'SELECT * FROM <globall_table_name>'.
l_res_ref = l_stmt_ref->execute_query( l_stmt ).
GET REFERENCE OF t_table INTO l_dref.
l_res_ref->set_param_table( l_dref ).
l_res_ref->next_package( ).

If you have ABAP 7.4 you can use database procedure proxy.