ZBCC0006-ADBC reuse routine-SE38(I類型)

*&---------------------------------------------------------------------*
*& INCLUDE          ZBCC0006: ADBC reuse routine
*&---------------------------------------------------------------------*

DATA: %g_er_msg TYPE string. "Error mesage global use

***  DB connection name
DATA: %g_dbcon_name  TYPE dbcon_name VALUE 'ORDB'. "default DB connect

***  DB Schema name
DATA: %g_schema_name  TYPE dbadbc_schema_name. "default schema

* ADBC - Handling the Connection and statements
DATA: %o_conn     TYPE REF TO cl_sql_connection.






*&---------------------------------------------------------------------*
*& Macro
*&---------------------------------------------------------------------*
DEFINE check_error.
  IF %g_er_msg IS NOT INITIAL.
    e_msg = %g_er_msg.
    e_status = 'E'.

    IF %o_conn IS NOT INITIAL.
      %o_conn->close( ).
    ENDIF.

    RETURN.
  ENDIF. "IF %g_er_msg IS NOT INITIAL
END-OF-DEFINITION.




******
*&---------------------------------------------------------------------*
*& Form CONNECT_EXT_DB
*&---------------------------------------------------------------------*
*& connect to external DB  -> return %o_conn
*&---------------------------------------------------------------------*
FORM connect_ext_db  USING    f_dbcon_name
                              f_schema_name.
*** exception handle
  DATA: lo_cx_sql  TYPE REF TO cx_sql_exception.

*************************

  CLEAR: %g_er_msg.

  IF f_dbcon_name IS NOT INITIAL.
    %g_dbcon_name = f_dbcon_name."DB connect Name
  ENDIF.

  IF f_schema_name IS NOT INITIAL.
    %g_schema_name = f_schema_name."DB schema Name
  ENDIF.

  TRY.
***    connect to external DB
      %o_conn = cl_sql_connection=>get_connection( %g_dbcon_name ).

    CATCH cx_sql_exception INTO lo_cx_sql.
      %g_er_msg = lo_cx_sql->get_text( ).
      IF %g_er_msg IS INITIAL.
        %g_er_msg = `Connect to DB: ` &&  %g_dbcon_name  && ' Error'.
      ENDIF.
  ENDTRY.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_COLUMNS_INFO
*&---------------------------------------------------------------------*
*& 取得 ORACLE DB TABLE 欄位的設定 + primary key 欄位
*&---------------------------------------------------------------------*
FORM get_columns_info  USING   ft_col    TYPE adbc_tabcol_descr_tab "回傳的 欄位 ITAB
                               ft_pk_col TYPE adbc_column_tab  "回傳的 KEY FIELD 欄位 ITAB
                               f_mid_table  "oracle table name
                               .

  DATA: lo_metadata TYPE REF TO cl_sql_metadata.

  DATA:
    lt_tabdescr_tab TYPE adbc_table_descr_tab,
    lt_schema_rgtab TYPE adbc_schema_name_rgtab,
    lt_table_rgtab  TYPE adbc_name_rgtab.

*  FIELD-SYMBOLS:
*    <lfs_tab_rg>   LIKE LINE OF lt_table_rgtab,
*    <lfs_tabdescr> LIKE LINE OF lt_tabdescr_tab,
*    <lfs_schema>   TYPE adbc_schema_name,
*    <lfs_tabname>  TYPE adbc_name.

  DATA: l_schema TYPE adbc_name.
  DATA: l_table TYPE adbc_name.

  DATA  l_pk_name       TYPE adbc_name.


****  exception
  DATA: lo_sqlerr       TYPE REF TO cx_sql_exception.

**********************************************************
  CLEAR: %g_er_msg.
* create a metadata object
  lo_metadata = %o_conn->get_metadata( ).

* search for the specified table in the database catalog; because
* the method GET_TABLES expects a range table for the table search
* we create one with an EQ-condition
  TRY.
***  set schema
      APPEND INITIAL LINE TO lt_schema_rgtab ASSIGNING FIELD-SYMBOL(<lfs_schema>).
      <lfs_schema>-sign   = 'I'.
      <lfs_schema>-option = 'EQ'.
      <lfs_schema>-low    =  %g_schema_name.

****  set table
      APPEND INITIAL LINE TO lt_table_rgtab ASSIGNING FIELD-SYMBOL(<lfs_table>).
      <lfs_table>-sign = 'I'.
      <lfs_table>-option = 'EQ'.
      <lfs_table>-low = f_mid_table.

      CALL METHOD lo_metadata->get_tables
        EXPORTING
          schema_rgtab    = lt_schema_rgtab
          table_rgtab     = lt_table_rgtab
        IMPORTING
          table_descr_tab = lt_tabdescr_tab.

*       check if the table was found in the catalog (this means that the
*       internal table returned contains at least one row)
      READ TABLE lt_tabdescr_tab INDEX 1 ASSIGNING FIELD-SYMBOL(<lfs_tabdescr>).
      IF sy-subrc <> 0.
        %g_er_msg = `Table: ` && f_mid_table && ` not found in Schema: ` && %g_schema_name.
        RETURN.
      ENDIF.

      l_schema = <lfs_tabdescr>-schema.
      l_table = <lfs_tabdescr>-table_name.

**     get all columns of the table/view from the database catalog
      CALL METHOD lo_metadata->get_columns
        EXPORTING
          schema_name = l_schema
          table_name  = l_table
        IMPORTING
          column_tab  = ft_col.
*     Check if returned table is empty
      READ TABLE ft_col INDEX 1 TRANSPORTING NO FIELDS.
      IF sy-subrc <> 0.
        %g_er_msg = `Table: ` && l_table && `DB catalog not found!!`.
        RETURN.
      ENDIF.

      SORT ft_col BY position.

*****  get primary key field
      CALL METHOD lo_metadata->get_primary_keys
        EXPORTING
          schema_name      = l_schema
          table_name       = l_table
        IMPORTING
          primary_key_name = l_pk_name
          primary_key_tab  = ft_pk_col.
      READ TABLE ft_pk_col INDEX 1 TRANSPORTING NO FIELDS.
      IF sy-subrc <> 0.
        %g_er_msg = `Table: ` && l_table && `Primary Key not found!!`.
        RETURN.
      ENDIF.


    CATCH cx_sql_exception INTO lo_sqlerr.

      %g_er_msg = 'Table ' && l_table && ` get metadat error: `
                  && lo_sqlerr->sql_code && ` `
                  && lo_sqlerr->get_text( ).                "#EC NOTEXT
  ENDTRY.


ENDFORM.
*&---------------------------------------------------------------------*
*& Form GEN_INS_SQL_AND_WA
*&---------------------------------------------------------------------*
*& 產生 insert filed / value list,  work area reference
*&---------------------------------------------------------------------*
FORM gen_ins_sql_and_wa  USING    ft_col  TYPE adbc_tabcol_descr_tab
                                  f_fields  "field list
                                  f_values  "value list
                                  fr_wa
                                        .

  DATA: l_col LIKE LINE OF ft_col.

  DATA: l_tabix LIKE sy-tabix.
  DATA: lt_comp TYPE abap_component_tab
        WITH HEADER LINE.

  DATA: lo_stru_desc  TYPE REF TO cl_abap_structdescr.

  DATA: l_value TYPE string.

  DATA: l_fields TYPE string.   "Duke

***********************
  CLEAR: %g_er_msg.
  CLEAR: f_fields, f_values.

  LOOP AT ft_col INTO l_col.

    ADD 1 TO l_tabix.

    IF l_col-ddic_type = 'DATS'.
      l_value =  `TO_DATE(?, 'YYYYMMDD')`.
**----->>> change by tommy 2021/03/05
*      l_fields = `TO_CHAR(` && l_col-column_name && `, 'YYYYMMDD')`.
      l_fields = l_col-column_name.
**-----<<< change by tommy 2021/03/05
    ELSE.
      l_value = '?'.
      l_fields = l_col-column_name.
    ENDIF.

    IF l_tabix = 1.
*      f_fields = l_col-column_name.
      f_fields = l_fields.
      f_values = l_value.
    ELSE.
*      f_fields = f_fields && `, ` && l_col-column_name.
      f_fields = f_fields && `, ` && l_fields.
      f_values =  f_values && `, ` && l_value.
    ENDIF.

****  WA field 描述
    CLEAR: lt_comp.
    lt_comp-name =  l_col-column_name.
    PERFORM set_type USING lt_comp-type l_col .
    IF %g_er_msg IS NOT INITIAL.
      RETURN.
    ENDIF.
    APPEND lt_comp.

  ENDLOOP."LOOP AT %t_coldescr


*****    work area
  lo_stru_desc = cl_abap_structdescr=>create( lt_comp[] ).
  CREATE DATA fr_wa TYPE HANDLE lo_stru_desc.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_TYPE
*&---------------------------------------------------------------------*
*&  data type mapping
*&---------------------------------------------------------------------*
FORM set_type  USING    f_type
                        f_coldescr TYPE   adbc_tabcol_descr.

  DATA: l_length TYPE i.
  DATA: l_decimals TYPE i.
*************************

  l_length  = f_coldescr-ddic_length.
  l_decimals = f_coldescr-ddic_decimals.

  CASE f_coldescr-ddic_type.
    WHEN 'NUMC' ."N
      f_type = cl_abap_elemdescr=>get_n( l_length ).

    WHEN 'CHAR'.
      f_type = cl_abap_elemdescr=>get_c( l_length ).

    WHEN 'DATS'."D
      f_type = cl_abap_elemdescr=>get_d( ).

    WHEN  'TIMS'."T
      f_type = cl_abap_elemdescr=>get_t( ).

    WHEN 'INT2'  ."I
      f_type = cl_abap_elemdescr=>get_int2( ).

    WHEN  'INT4'    ."I
      f_type = cl_abap_elemdescr=>get_i( ).

    WHEN  'INT8' ."I
      f_type = cl_abap_elemdescr=>get_int8( ).

    WHEN 'DEC'."P

      f_type = cl_abap_elemdescr=>get_p( p_length = ( l_length / 2  + 1 )
      p_decimals = l_decimals
      ).

    WHEN 'RAW'.
      f_type = cl_abap_elemdescr=>get_x( p_length = l_length ).
    WHEN 'FLTP'.
      f_type = cl_abap_elemdescr=>get_f( ).

    WHEN OTHERS.

      %g_er_msg  = `DDIC Type: ` && f_coldescr-ddic_type && `not process mapping, check FORM SET_TYPE!!`.

  ENDCASE."CASE l_comp-type_kind.

ENDFORM.                    " SET_TYPE
*&---------------------------------------------------------------------*
*& Form GEN_UPD_DEL_WHERE_AND_WA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
FORM gen_upd_del_where_and_wa  USING    ft_pk_col  TYPE adbc_column_tab "primary key field list
                                        ft_col TYPE adbc_tabcol_descr_tab "table field list
                                        f_where
                                        fr_wa.


  DATA: l_pk_col LIKE LINE OF ft_pk_col.
  DATA: l_col LIKE LINE OF ft_col.

  DATA: lt_comp TYPE abap_component_tab
        WITH HEADER LINE.
  DATA: l_tabix LIKE sy-tabix.
  DATA: lo_stru_desc  TYPE REF TO cl_abap_structdescr.

  DATA: l_value TYPE string.

*******************

  CLEAR: f_where.

  LOOP AT ft_pk_col INTO l_pk_col.
    ADD 1 TO l_tabix.

****  update/delete where condition
    READ TABLE ft_col INTO l_col
      WITH KEY column_name = l_pk_col.

    IF l_col-ddic_type = 'DATS'.
      l_value =  `TO_DATE(?, 'YYYYMMDD')`.
    ELSE.
      l_value = '?'.
    ENDIF.

    IF l_tabix = 1.
      f_where = l_pk_col && ` = ` && l_value.
    ELSE.
      f_where = f_where && ` AND ` && l_pk_col && ` = ` && l_value.
    ENDIF.

****  WA 描述
    CLEAR: lt_comp.
    lt_comp-name = l_col-column_name.
    PERFORM set_type USING lt_comp-type l_col.
    IF %g_er_msg IS NOT INITIAL.
      RETURN.
    ENDIF.

    APPEND lt_comp.

  ENDLOOP."LOOP AT %t_pk_col


** insert/delete work area
  lo_stru_desc = cl_abap_structdescr=>create( lt_comp[] ).
  CREATE DATA fr_wa TYPE HANDLE lo_stru_desc.


ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_PRE_STAT_OBJ_R
*&---------------------------------------------------------------------*
*& create statemant object and set parameter reference
*&---------------------------------------------------------------------*
FORM create_pre_stat_obj_r  USING    f_query
                                     fo_pre_stat TYPE REF TO cl_sql_prepared_statement
                                     f_st TYPE REF TO data. "WA
  DATA: lo_cx_root TYPE REF TO cx_root.
********************
  CLEAR: %g_er_msg.
  TRY .
**** SQL statemant create
      fo_pre_stat = %o_conn->prepare_statement( f_query ).
*** QUERY 參數欄位 binding
      fo_pre_stat->set_param_struct( f_st ).
    CATCH cx_sql_exception
           cx_parameter_invalid
      INTO lo_cx_root.
      %g_er_msg = lo_cx_root->get_text( ).
      IF %g_er_msg IS INITIAL.
        %g_er_msg = 'Create Query Object Error!'.
      ENDIF.
  ENDTRY.

ENDFORM.                    " CREATE_PRE_STAT_OBJ_R
*&---------------------------------------------------------------------*
*& Form CREATE_INSERT_STATEMENT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> LT_COL_M
*&      --> LT_PK_COL_M
*&      --> I_MDBNAME_M
*&      --> LO_INS_STAT_M
*&      --> LR_INS_WA_M
*&---------------------------------------------------------------------*
FORM create_insert_statement  USING    p_lt_col_m
                                       p_lt_pk_col_m
                                       p_i_mdbname_m
                                       p_lo_ins_stat_m
                                       p_lr_ins_wa_m.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_INSERT_STATEMENT_OBJ
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> LT_COL_M
*&      --> I_MDBNAME_M
*&      --> LO_INS_STAT_M
*&      --> LR_INS_WA_M
*&---------------------------------------------------------------------*
FORM create_insert_statement_obj  USING   ft_pk_col TYPE adbc_column_tab  "return table primary key field list
                                          ft_col TYPE adbc_tabcol_descr_tab "return table field list
                                           f_mdbname "oracle table name
                                           fo_ins_stat TYPE REF TO cl_sql_prepared_statement
                                           fr_st TYPE REF TO data."WA reference

  DATA: l_fields_ins TYPE string."insert field list
  DATA: l_values_ins TYPE string."insert value list

  DATA: l_ins_query  TYPE string.

********************************

****  1. matadata
****  get oracle master table field and primary key field
  PERFORM get_columns_info USING ft_col ft_pk_col
                    f_mdbname .
  IF %g_er_msg IS NOT INITIAL.
****   metadata error
*    e_msg = %g_er_msg.
*    e_status = 'E'.
    RETURN.
  ENDIF. "IF %g_er_msg IS NOT INITIAL

****   2. insert field list / value list / WA
  PERFORM gen_ins_sql_and_wa
       USING ft_col
             l_fields_ins l_values_ins fr_st
             .
  IF %g_er_msg IS NOT INITIAL.
****   generate WA error
*    e_msg = %g_er_msg.
*    e_status = 'E'.
    RETURN.
  ENDIF. "IF %g_er_msg IS NOT INITIAL


****  3. insert query statemant object
  l_ins_query = `INSERT INTO ` &&  %g_schema_name && `.`  && f_mdbname
             && ` ( `  && l_fields_ins && ` ) VALUES ( `
             && l_values_ins && ` )`.

  PERFORM create_pre_stat_obj_r
     USING l_ins_query fo_ins_stat fr_st.
  IF %g_er_msg IS NOT INITIAL.
*    e_msg = %g_er_msg.
*    e_status = 'E'.
    RETURN.
  ENDIF.


ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_DELETE_STATEMENT_OBJ
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
FORM create_delete_statement_obj  USING    f_del_where
                                           f_mdbname
                                           fo_del_stat TYPE REF TO cl_sql_prepared_statement
                                           fr_st TYPE REF TO data."WA reference

  DATA: l_del_query  TYPE string.

*****************************
  CLEAR: %g_er_msg.

  l_del_query = `DELETE FROM ` &&  %g_schema_name && `.`  && f_mdbname
               && ` WHERE ` && f_del_where.

  PERFORM create_pre_stat_obj_r
     USING l_del_query fo_del_stat  fr_st.
  IF %g_er_msg IS NOT INITIAL.
*    e_msg = %g_er_msg.
*    e_status = 'E'.
    RETURN.
  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form GEN_SAP_DEL_WHERE
*&---------------------------------------------------------------------*
*& update or delete where clause
*&---------------------------------------------------------------------*
FORM gen_sap_del_where  USING    ft_pk_col  TYPE adbc_column_tab
                                 f_data
                                f_where.

  DATA: l_pk_col LIKE LINE OF ft_pk_col.
  DATA: l_tabix LIKE sy-tabix.
  DATA: l_value TYPE string.
  FIELD-SYMBOLS: <lfs>.
************************************
  LOOP AT ft_pk_col INTO l_pk_col.

****  update/delete where condition
    ADD 1 TO l_tabix.

    ASSIGN COMPONENT l_pk_col OF STRUCTURE f_data TO <lfs>.

    IF l_tabix = 1.
      f_where = l_pk_col && ` = '` && <lfs> && `'`.
    ELSE.
      f_where = f_where && ` AND ` && l_pk_col && ` = '` && <lfs>  && `'`.
    ENDIF.

  ENDLOOP."LOOP AT %t_pk_col

ENDFORM.
*&---------------------------------------------------------------------*
*& Form GEN_UPD_DEL_WHERE_AND_WA_BY_LV
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
FORM gen_upd_del_where_and_wa_by_lv  USING
                                        ft_key_col  TYPE adbc_column_tab "primary key field list
                                        ft_col TYPE adbc_tabcol_descr_tab "table field list
                                        f_key_level
                                        f_where
                                        fr_wa.


  DATA: l_key_col LIKE LINE OF ft_key_col.
  DATA: l_col LIKE LINE OF ft_col.

  DATA: lt_comp TYPE abap_component_tab
        WITH HEADER LINE.
  DATA: l_tabix LIKE sy-tabix.
  DATA: lo_stru_desc  TYPE REF TO cl_abap_structdescr.

  DATA: l_value TYPE string.

*******************

  CLEAR: f_where.

  LOOP AT ft_key_col INTO l_key_col TO f_key_level.
    ADD 1 TO l_tabix.

****  update/delete where condition
    READ TABLE ft_col INTO l_col
      WITH KEY column_name = l_key_col.

    IF l_col-ddic_type = 'DATS'.
      l_value =  `TO_DATE(?, 'YYYYMMDD')`.
    ELSE.
      l_value = '?'.
    ENDIF.

    IF l_tabix = 1.
      f_where = l_key_col && ` = ` && l_value.
    ELSE.
      f_where = f_where && ` AND ` && l_key_col && ` = ` && l_value.
    ENDIF.

****  WA 描述
    CLEAR: lt_comp.
    lt_comp-name = l_col-column_name.
    PERFORM set_type USING lt_comp-type l_col.
    IF %g_er_msg IS NOT INITIAL.
      RETURN.
    ENDIF.

    APPEND lt_comp.

  ENDLOOP."LOOP AT %t_pk_col


** insert/delete work area
  lo_stru_desc = cl_abap_structdescr=>create( lt_comp[] ).
  CREATE DATA fr_wa TYPE HANDLE lo_stru_desc.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form GEN_SAP_DEL_WHERE_MULTI
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
FORM gen_sap_del_where_multi  USING    ft_key_col  TYPE adbc_column_tab
                                        f_key_level
                                f_wa_name
                                f_where.

  DATA: l_key_col LIKE LINE OF ft_key_col.
  DATA: l_tabix LIKE sy-tabix.
*  DATA: l_value TYPE string.
*  FIELD-SYMBOLS: <lfs>.
************************************

  LOOP AT ft_key_col INTO l_key_col TO f_key_level.

****  update/delete where condition
    ADD 1 TO l_tabix.

    IF l_tabix = 1.
      f_where = l_key_col && ` = @` && f_wa_name && `-` && l_key_col.
    ELSE.
      f_where = f_where && ` AND ` && l_key_col && ` = @` && f_wa_name && `-` && l_key_col.
    ENDIF.

  ENDLOOP."LOOP AT %t_pk_col

ENDFORM.