topic Re: Procedure OUT param as Table in Application Development and Automation Discussions

Procedure OUT param as Table

italo_naia — Fri, 19 Apr 2013 20:04:56 GMT

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,

Re: Procedure OUT param as Table

yeushengteo — Mon, 22 Apr 2013 05:56:14 GMT

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.

Re: Procedure OUT param as Table

schneidertho — Mon, 22 Apr 2013 08:21:44 GMT

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

Re: Procedure OUT param as Table

italo_naia — Mon, 22 Apr 2013 12:36:53 GMT

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

Thanks Thorsten

Re: Procedure OUT param as Table

RichHeilman — Wed, 24 Apr 2013 19:41:36 GMT

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

Re: Procedure OUT param as Table

UweFetzer_se38 — Tue, 18 Jun 2013 17:40:39 GMT

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.

Re: Procedure OUT param as Table

UweFetzer_se38 — Thu, 20 Jun 2013 09:33:27 GMT

Or of course the 7.40 way

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

Re: Procedure OUT param as Table

former_member459016 — Wed, 18 Mar 2015 14:01:04 GMT

Hi,

have you resolved this problem?

Can you show me the code ABAP, please?

Re: Procedure OUT param as Table

jasmin_gruschke — Wed, 18 Mar 2015 14:25:57 GMT

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

Re: Procedure OUT param as Table

italo_naia — Wed, 18 Mar 2015 14:26:35 GMT

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.