ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  ADBC - ABAP Database Connectivity →  Examples of ADBC 

ADBC, Stored Procedure

The example demonstrates the execution of a stored procedure using ADBC.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA incprice TYPE sflight-price.
    DATA price_before TYPE sflight-price.
    DATA price_after  TYPE sflight-price.
    SELECT SINGLE price FROM sflight INTO price_before.
    cl_demo_input=>request( CHANGING field = incprice ).
    IF incprice IS INITIAL.
      RETURN.
    ENDIF.
    DATA(sql) = NEW cl_sql_statement( ).
    TRY.
        CASE substring( val = cl_db_sys=>dbsys_type len = 3 ).
          WHEN 'HDB'.
            TRY.
                sql->execute_ddl(
                  'DROP PROCEDURE abap_docu_demo_incprice' ).
              CATCH cx_sql_exception ##NO_HANDLER.
            ENDTRY.
            sql->execute_ddl(
               `CREATE PROCEDURE  `
            && `abap_docu_demo_incprice (IN inc DECIMAL(15,2)) AS `
            && `BEGIN `
            && `UPDATE sflight SET price = price + :inc`
            && `              WHERE mandt = '` && sy-mandt && `'; `
            && `END` ).
            sql->set_param( data_ref = REF #( incprice )
                           inout    = cl_sql_statement=>c_param_in ).
            sql->execute_procedure(
              proc_name = 'abap_docu_demo_incprice' ).
          WHEN 'ADA'.
            TRY.
                sql->execute_ddl(
                  'DROP PROCEDURE abap_docu_demo_incprice' ).
              CATCH cx_sql_exception ##NO_HANDLER.
            ENDTRY.
            DATA schema TYPE c LENGTH 256.
            CALL FUNCTION 'DB_DBSCHEMA'
              IMPORTING
                dbschema = schema.
            sql->execute_ddl(
               `CREATE DBPROC abap_docu_demo_incprice `
            && `(IN inc VARCHAR(15)) AS `
            && `UPDATE `
            && cl_abap_dyn_prg=>check_whitelist_str(
                 val = schema
                 whitelist = `SAP` && sy-sysid ) && `.sflight `
            && `  SET price = price + TO_NUMBER(:inc)`
            && `  WHERE mandt = '` && sy-mandt && `'; ` ).
            DATA(char_incprice) = CONV string( incprice ).
            sql->set_param( data_ref = REF #( char_incprice )
                           inout    = cl_sql_statement=>c_param_in ).
            sql->execute_procedure(
              proc_name = 'abap_docu_demo_incprice' ).
          WHEN 'ORA'.
            sql->execute_ddl(
              `CREATE OR REPLACE PROCEDURE `
            && `abap_docu_demo_incprice (inc IN NUMBER) IS `
            && `BEGIN `
            && `UPDATE sflight SET price = price + inc`
            && `              WHERE mandt = '` && sy-mandt && `'; `
            && `END;` ).
            sql->set_param( data_ref = REF #( incprice )
                           inout    = cl_sql_statement=>c_param_in ).
            sql->execute_procedure(
              proc_name = 'abap_docu_demo_incprice' ).
          WHEN OTHERS.
            cl_demo_output=>display( `Example is not supported for `
                                    && sy-dbsys ).
            LEAVE PROGRAM.
        ENDCASE.
      CATCH cx_sql_exception cx_abap_not_in_whitelist INTO DATA(err).
        cl_demo_output=>display( err->get_text( ) ).
    ENDTRY.
    SELECT SINGLE price FROM sflight INTO price_after.
    IF price_after - price_before = incprice.
      cl_demo_output=>display( `Price increased succesfully` ).
    ENDIF.

Description

Using the method EXECUTE_PROCEDURE of the class CL_SQL_STATEMENT, the SQLScript procedure increase_price defined in the same program using the method EXECUTE_DDL is called. This raises all the flight prices in the table SFLIGHT in the current client by the value contained in the parameter incprice.

This procedure must be implemented specially for each database. The databases in this example are the SAP HANA database, MaxDB, and Oracle. This example program can only be executed for these databases.

The example AMDP, simple procedure call of an SQLScript procedure shows how the procedure can be managed and called as an ABAP Managed Database Procedure. A further example, specific to SAP HANA database, shows how the procedure is called using statement CALL DATABASE PROCEDURE.