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.
No comments:
Post a Comment