Does ADBC facilitate code push down on Oracle DB?

Above — Sat, 27 Nov 2021 11:55:04 GMT

Hi Members,

I used ADBC technique to facilitate code push down on oracle database. Bothe the connection and query execution are successful but there's error in reading data using the method next_package. Please see my code below and help out.

""@@Obtaining last movement date.
*    * Establish the DB connection (ORACLE DB)
    DATA: db_con  TYPE dbcon_name,
          con_obj TYPE REF TO cl_sql_connection.


    CREATE OBJECT con_obj.
    CALL METHOD con_obj->get_con_name
      RECEIVING
        con_name = db_con.


    TRY.
        CLEAR v_msg.
        CALL METHOD cl_sql_connection=>get_connection
          EXPORTING
            con_name = db_con
*            sharable = space
          RECEIVING
            con_ref  = o_con.

      CATCH cx_sql_exception INTO  o_sql_excp .
        CALL METHOD o_sql_excp->if_message~get_longtext
          RECEIVING
            result = v_msg.
        .
        WRITE :/ 'Error in Establishing DB connection :',v_msg.
    ENDTRY.

    IF o_con IS BOUND.
*  * Instantiate the statement class on top of DB connection
      DATA: o_stmt TYPE REF TO cl_sql_statement.
      CREATE OBJECT o_stmt
        EXPORTING
          con_ref = o_con.
    ENDIF.

    BREAK-POINT.
    IF o_stmt IS BOUND.
**    Construct the sql query containing select statement
      DATA: v_sql TYPE string.


      v_sql = | SELECT MK.MBLNR, MK.MJAHR, MK.BUDAT, MS.MATNR, MS.WERKS | &&
                 | FROM MKPF AS MK INNER JOIN MSEG AS MS | &&
                 | ON MK.MBLNR = MS.MBLNR AND MK.MJAHR = MS.MJAHR| &&
                 | WHERE MS.MATNR = { wa_mbew-matnr } AND MS.WERKS = { wa_mbew-bwkey } | &&
                 | ORDER BY MK.BUDAT | &&
                 || .



*      * Execute Select Query

      DATA : o_result TYPE REF TO cl_sql_result_set,
             o_param  TYPE REF TO cx_parameter_invalid.
      TRY.
          CALL METHOD o_stmt->execute_query
            EXPORTING
              statement  = v_sql
*             hold_cursor = SPACE
            RECEIVING
              result_set = o_result.
        CATCH cx_sql_exception INTO o_sql_excp.
          CLEAR v_msg.
          CALL METHOD o_sql_excp->if_message~get_longtext
            RECEIVING
              result = v_msg.
          WRITE :/ 'Error in Executing select query :',v_msg.
        CATCH cx_parameter_invalid INTO o_param.
          CLEAR v_msg.
          CALL METHOD o_param->if_message~get_longtext
            RECEIVING
              result = v_msg.

          WRITE :/ 'Error in Parameter binding ....',v_msg.

      ENDTRY.

    ENDIF.
    IF o_result IS BOUND. "not initial
      WRITE :/ 'Select Query successfully executed...'.
*    * set the Internal table parameter
*      data it_mseg_mkpf type TABLE OF WB2_V_MKPF_MSEG.
      DATA o_data TYPE REF TO data.

      GET REFERENCE OF it_mseg_mkpf INTO o_data.

* set the internal table as out parameter
      TRY.
          CALL METHOD o_result->set_param_table
            EXPORTING
              itab_ref = o_data.
        CATCH cx_parameter_invalid INTO o_param .
          CLEAR v_msg.
          CALL METHOD o_param->if_message~get_longtext
            RECEIVING
              result = v_msg.
          WRITE :/ 'Error in setting internal table as OUT parameter ...',v_msg.
      ENDTRY.

* Retrieve result set
      DATA o_param_type TYPE REF TO cx_parameter_invalid_type.

      TRY.
          CALL METHOD o_result->next_package.

        CATCH cx_sql_exception INTO o_sql_excp.
          CLEAR v_msg.
          CALL METHOD o_sql_excp->if_message~get_longtext
            RECEIVING
              result = v_msg.
          WRITE :/ 'Error in reading result set :',v_msg.

        CATCH cx_parameter_invalid_type .
          CLEAR v_msg.
          CALL METHOD o_param_type->if_message~get_longtext
            RECEIVING
              result = v_msg.
          WRITE :/ 'Error in parameter type while reading result set :',v_msg.
      ENDTRY.

*        * close the result set
      CALL METHOD o_result->close.

      TRY.
          CALL METHOD o_con->close.
          CLEAR v_msg.
        CATCH cx_sql_exception INTO o_sql_excp.
          CALL METHOD o_sql_excp->if_message~get_longtext
            RECEIVING
              result = v_msg.

          WRITE :/ 'Error in closing DB connection :',v_msg.
      ENDTRY.

*      ENDTRY.

    ENDIF.

Re: Does ADBC facilitate code push down on Oracle DB?

Sandra_Rossi — Sat, 27 Nov 2021 13:28:49 GMT

It's not called "code push-down", this is a feature of AMDP methods, you're talking about executing "native (Oracle) SQL".

If you display o_sql_excp using the debugger, you'll see the detailed message and other information about what the error is. Another possibility is to execute your code directly using an Oracle SQL console, and you'll see that there's an error and probably it will give more details about the error.

Wrong:

WHERE MS.MATNR = 0000000000000545054 AND MS.WERKS = ABCD

Good:

WHERE MS.MATNR = '0000000000000545054' AND MS.WERKS = 'ABCD'

That said, you'd better use a Prepared Statement for better performance (reuse of execution plan), so that to define 2 placeholders so that to bind variables:

WHERE MS.MATNR = ? AND MS.WERKS = ?

and use SET_PARAM sequentially to bind the value to each placeholder.

topic Does ADBC facilitate code push down on Oracle DB? in Application Development and Automation Discussions

Does ADBC facilitate code push down on Oracle DB?

Re: Does ADBC facilitate code push down on Oracle DB?