How to Create Table and Push Data to ODBC/Remote Database using Native SQL with ABAP DB Connectivity (ADBC)

[kamilmirza]

Recently I had a task to Push Data from SAP Reports to MS SQL DB, periodically, and to Create Tables of the required Reports in their MS SQL DB, as well

Being a complete novice ABAP developer, I learned about ABAP DB Connectivity (ADBC) DDL and DML methods, which helped me complete the task more dynamically, instead of using EXEC SQL with Placeholders ? for each field

Before moving forward I would like to state the obvious that by no means my approach is best/optimal out there

So lets start the code with the following objectives:

1. Create a Program to fetch data from SAP Report using SUBMIT


2. Create Table in MS SQL DB according to Report fields


3. Push Data in newly created Table

 

1. Create a Program to fetch data from SAP Report using SUBMIT

REPORT ZSCHEDULE.



DATA:

  CREATE_FIELDS TYPE STRING,

  CREATE_PKEY   TYPE STRING,

  INSERT_VALUES TYPE STRING,

  IT_INSERT     TYPE STRINGTAB,

  DATUM         TYPE DATUM,

  UZEIT         TYPE UZEIT.



FIELD-SYMBOLS: <LT_SALV> TYPE ANY TABLE,

               <VALUE>   TYPE ANY.



DATA:

  LS_VBAK TYPE VBAK,

  LS_VBAP TYPE VBAP.



SELECT-OPTIONS:

    SO_VKORG FOR LS_VBAK-VKORG,

    SO_VTWEG FOR LS_VBAK-VTWEG,

    SO_SPART FOR LS_VBAK-SPART,

    SO_VSTEL FOR LS_VBAP-VSTEL,

    SO_VKBUR FOR LS_VBAK-VKBUR.



START-OF-SELECTION.



TRY.



  CL_SALV_BS_RUNTIME_INFO=>SET( DISPLAY = ABAP_FALSE

                                METADATA = ABAP_TRUE

                                DATA = ABAP_TRUE ).



  SUBMIT Z_RPT_PROG

  WITH  SO_VKORG IN  SO_VKORG

  WITH  SO_VTWEG IN  SO_VTWEG

  WITH  SO_SPART IN  SO_SPART

  WITH  SO_VSTEL IN  SO_VSTEL

  WITH  SO_VKBUR IN  SO_VKBUR

    AND RETURN.



  DATA(SALV_META) = CL_SALV_BS_RUNTIME_INFO=>GET_METADATA( ).

  CL_SALV_BS_RUNTIME_INFO=>GET_DATA_REF( IMPORTING R_DATA = DATA(SALV_DATA) ).

  ASSIGN SALV_DATA->* TO <LT_SALV>.



  CL_SALV_BS_RUNTIME_INFO=>CLEAR_ALL( ).

...

I have used the formidable CL_SALV_BS_RUNTIME_INFO class to extract ALV Data from the SUBMIT program

2. Create Table in MS SQL DB according to Report fields

I have also fetched the ALV Metadata because I need Field Catalog of the SUBMIT program in order to create our DB Table fields

So I had to write a logic to create different Data type fields in SQL, to be used in CREATE TABLE Native SQL statement dynamically

...

  LOOP AT SALV_META-T_FCAT INTO DATA(WA_FCAT).



    REPLACE ALL OCCURRENCES OF '/' IN WA_FCAT-FIELDNAME WITH '_'. " forward slash / is considered COMMENT in SQL

    MODIFY SALV_META-T_FCAT FROM WA_FCAT TRANSPORTING FIELDNAME.



    CASE WA_FCAT-DATATYPE.

      WHEN 'CHAR'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } varchar({ WA_FCAT-INTLEN })|.

      WHEN 'CUKY'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } varchar(5)|.

      WHEN 'UNIT'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } varchar(3)|.

      WHEN 'DATS'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } date|.

      WHEN 'TIMS'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } time|.

      WHEN 'RAW'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } nvarchar(max)|.

      WHEN 'NUMC' OR 'INT4' OR 'DEC' OR 'QUAN' OR 'CURR'.

        CREATE_FIELDS = CREATE_FIELDS && |{ WA_FCAT-FIELDNAME } float|.

    ENDCASE.



    CREATE_FIELDS = CREATE_FIELDS && ','.



    IF WA_FCAT-KEY EQ 'X'.

      IF CREATE_PKEY IS NOT INITIAL.

        CREATE_PKEY = CREATE_PKEY && ','.

      ENDIF.

      CREATE_PKEY = CREATE_PKEY && |[{ WA_FCAT-FIELDNAME }]|.

    ENDIF.

  ENDLOOP.



  DATA(TABLE) = `SAP_ZREPORT`.



  IF CREATE_PKEY IS INITIAL.

    DATA(CREATE_TABLE) = |CREATE TABLE { TABLE } ( { CREATE_FIELDS } )|.

  ELSE.

    CREATE_TABLE = |CREATE TABLE { TABLE } ( { CREATE_FIELDS } PRIMARY KEY ({ CREATE_PKEY }) )|.

  ENDIF.

...

CREATE TABLE SQL DDL statement is ready with all fields in the Field Catalog, along with Primary Keys and ready to be executed with ADBC EXECUTE_DDL statement

Since I was creating SQL DB table fields with SAP fieldnames, Client asked to have Field Description in the Comment section of every field for identification, like the following:



So I used ALTER TABLE SQL DDL statement to add Description in the Comment field

...

  LOOP AT SALV_META-T_FCAT ASSIGNING FIELD-SYMBOL(<FIELD>).

    CASE <FIELD>-DATATYPE.

      WHEN 'CHAR'.

        <FIELD>-TOOLTIP = |varchar({ <FIELD>-INTLEN })|.

      WHEN 'CUKY'.

        <FIELD>-TOOLTIP = |varchar(5)|.

      WHEN 'UNIT'.

        <FIELD>-TOOLTIP = |varchar(3)|.

      WHEN 'DATS'.

        <FIELD>-TOOLTIP = |date|.

      WHEN 'TIMS'.

        <FIELD>-TOOLTIP = |time|.

      WHEN 'RAW'.

        <FIELD>-TOOLTIP = |nvarchar(max)|.

      WHEN 'NUMC' OR 'INT4' OR 'DEC' OR 'QUAN' OR 'CURR'.

        <FIELD>-TOOLTIP = |float|.

    ENDCASE.

    REPLACE ALL OCCURRENCES OF '/'  IN <FIELD>-SELTEXT WITH '-'. " forward slash / is considered COMMENT in SQL

    IF <FIELD>-SELTEXT IS INITIAL. " COMMENT/DESCRIPTION cannot be blank in ALTER TABLE statement

      <FIELD>-SELTEXT = <FIELD>-FIELDNAME.

    ENDIF.

  ENDLOOP.



  LOOP AT SALV_META-T_FCAT INTO DATA(WA_TABLE_FIELDS).

    DATA(ALTER_TABLE) = |ALTER TABLE { TABLE } ALTER COLUMN "{ WA_TABLE_FIELDS-FIELDNAME }" { WA_TABLE_FIELDS-TOOLTIP } NULL|.

    DATA(ALTER) = |{ ALTER_TABLE } EXECUTE sp_addextendedproperty 'MS_Description', '{ WA_TABLE_FIELDS-SELTEXT }', 'Schema', 'dbo', 'table', '{ TABLE }', 'column', '{ WA_TABLE_FIELDS-FIELDNAME }';|.

   APPEND ALTER TO IT_INSERT.

  ENDLOOP.

...

Note that ALTER TABLE SQL DDL statement with MS_Description is for MS SQL only and it needs to be passed with the same order like:

ALTER TABLE <tablename> ALTER COUMN "<fieldname>" <datatype> NULL EXECUTE sp_addextendedproperty 'MS_Description', '<comment/description>', 'Schema', '<DBschema>', 'table', '<tablename>', 'column', '<fieldname>';

and have appended ALTER statements in internal table IT_INSERT 

now it is time for the execution of Native SQL statements using ADBC 

  DATA(DBCONN) = CL_SQL_CONNECTION=>GET_CONNECTION( 'SQL' ).

  DATA(CREATE_STATEMENT) = DBCONN->CREATE_STATEMENT( ).



  CREATE_STATEMENT->EXECUTE_DDL( CREATE_TABLE ). " create table statement



  LOOP AT IT_INSERT INTO DATA(ROW).

    CREATE_STATEMENT->EXECUTE_DDL( ROW ). " alter table statment

  ENDLOOP.



  DBCONN->COMMIT( ).

  DBCONN->CLOSE( ).

3. Push Data in newly created Table

So now my table is created in the ODBC DB, shown in the previous screenshot, I don't need CREATE TABLE and ALTER TABLE statements because it was a one-time process, since I am going to execute this program on schedule to push data to remote DB

The tricky part was where I had to create INSERT SQL DML statement with my complete structure/work area values, accordingly, using nested loop and ASSIGN COMPONENT

and had to write logic to handle DATE and TIME field for SQL because SQL uses ISO format for DATE and TIME

...

  LOOP AT <LT_SALV> ASSIGNING FIELD-SYMBOL(<WA>).



    LOOP AT SALV_META-T_FCAT ASSIGNING <FIELD>.



      IF INSERT_VALUES IS NOT INITIAL.

        INSERT_VALUES = INSERT_VALUES && ','.

      ENDIF.



      ASSIGN COMPONENT <FIELD>-FIELDNAME OF STRUCTURE <WA> TO <VALUE>.

        IF <VALUE> IS ASSIGNED.

          IF <FIELD>-DATATYPE = 'DATS'.

            IF <VALUE> IS INITIAL.

              INSERT_VALUES = INSERT_VALUES && |''|.

            ELSE.

              DATUM = <VALUE>.

              DATA(DATE_STR) = |{ DATUM DATE = ISO }|.

              INSERT_VALUES = INSERT_VALUES && |'{ DATE_STR }'|.

            ENDIF.

          ELSEIF <FIELD>-DATATYPE = 'TIMS'.

            IF <VALUE> IS INITIAL.

              INSERT_VALUES = INSERT_VALUES && |''|.

            ELSE.

              UZEIT = <VALUE>.

              DATA(TIME_STR) = |{ UZEIT TIME = ISO }|.

              INSERT_VALUES = INSERT_VALUES && |'{ TIME_STR }'|.

            ENDIF.

          ELSE.

            INSERT_VALUES = INSERT_VALUES && |'{ <VALUE> }'|.

          ENDIF.

        ENDIF.



    ENDLOOP.



    DATA(INSERT) = |INSERT INTO { TABLE } VALUES ({ INSERT_VALUES })|.

    APPEND INSERT TO IT_INSERT.

    CLEAR: INSERT, INSERT_VALUES.

  ENDLOOP.



  IF IT_INSERT IS NOT INITIAL.

    DATA(DBCONN) = CL_SQL_CONNECTION=>GET_CONNECTION( 'SQL' ).

    DATA(INSERT_STATEMENT) = DBCONN->CREATE_STATEMENT( ).

    LOOP AT IT_INSERT INTO DATA(ROW).

      INSERT_STATEMENT->EXECUTE_UPDATE( ROW ).

    ENDLOOP.

  ENDIF.



  DBCONN->COMMIT( ).

  DBCONN->CLOSE( ).



  IF SY-SUBRC = 0.

    MESSAGE 'SQL operation successfull!' TYPE 'I' DISPLAY LIKE 'S'.

  ENDIF.



  CATCH CX_ROOT INTO DATA(CX_ERROR).

    DATA(ERROR) = CX_ERROR->GET_LONGTEXT( ).

    IF ERROR IS INITIAL.

      ERROR = CX_ERROR->GET_TEXT( ).

    ENDIF.

    MESSAGE ERROR TYPE 'I'.



ENDTRY.

 

this concludes the final step and this blog post

This code can be used for Standard and CDS Reports, as well, without any issues

I would like to mention that THIS BLOG which helped me, a lot, in writing my code

Would love to have your feedback