zSQL: A tool to execute SQL statements directly in sap - Source Code

[basarozgur_kahraman]

Source code of blog  http://scn.sap.com/community/abap/blog/2013/10/09/zsql-a-tool-to-execute-sql-statements-directly

*&---------------------------------------------------------------------*
*& Report  ZSQL
*&
*&---------------------------------------------------------------------*
*& Developer: Basar Ozgur KAHRAMAN - @basarozgur
*&---------------------------------------------------------------------*
REPORT zsql.

TYPES: BEGIN OF typ_tablename,
         tabname LIKE dd02l-tabname,
         alias   LIKE dd02l-tabname,
        END OF typ_tablename.
TYPES: typ_it_tablename TYPE STANDARD TABLE OF typ_tablename.

TYPES: BEGIN OF typ_selfields,
         tabname    LIKE lvc_s_fcat-tabname,
         fieldname  LIKE lvc_s_fcat-fieldname,
       END OF typ_selfields.
TYPES: typ_it_selfields TYPE STANDARD TABLE OF typ_selfields.

DATA: cc_sql  TYPE REF TO cl_gui_custom_container,
       sqltext TYPE REF TO cl_gui_textedit.

DATA: BEGIN OF it_sql OCCURS 0,
         tdline LIKE tline-tdline,
       END OF it_sql.

DATA: gv_changeallowed TYPE boolean.

INITIALIZATION.
   CALL SCREEN 0100.

*&---------------------------------------------------------------------*
*&      Form  CREATE_DYNAMIC_SQL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM create_dynamic_sql TABLES   p_it_sql  STRUCTURE it_sql
                         CHANGING p_program TYPE string
                                  p_isselectquery TYPE boolean.

   TYPES: _typ_pline(72).

   DATA: _message(128),
         _line        TYPE i,
         _it_pline    TYPE TABLE OF _typ_pline WITH HEADER LINE.

   CLEAR: p_program,
          p_isselectquery.

   _it_pline = 'REPORT ZSQL_INNER_DYNAMICPRG.'.
   APPEND _it_pline.
   _it_pline = 'FORM call_sql'.
   APPEND _it_pline.
   _it_pline = 'TABLES p_it_return'.
   APPEND _it_pline.
   _it_pline = 'CHANGING p_subrc LIKE sy-subrc.'.
   APPEND _it_pline.

   _it_pline = 'REFRESH p_it_return. p_subrc = 4.'.
   APPEND _it_pline.

   READ TABLE p_it_sql INDEX 1.
   IF p_it_sql-tdline CS 'SELECT'.
     p_isselectquery = 'X'.
   ENDIF.

   LOOP AT p_it_sql.
     IF p_it_sql-tdline CS 'FROM' AND
        p_isselectquery = 'X'.
       _it_pline = 'INTO CORRESPONDING FIELDS OF TABLE p_it_return'.
       APPEND _it_pline.
     ENDIF.
     _it_pline = p_it_sql-tdline .
     APPEND _it_pline.

   ENDLOOP.
   _it_pline = '.' .
   APPEND _it_pline.

   _it_pline = 'p_subrc = sy-subrc.'.
   APPEND _it_pline.
   _it_pline = 'ENDFORM.'.
   APPEND _it_pline.

   GENERATE SUBROUTINE POOL _it_pline
   NAME p_program
   MESSAGE _message
   LINE _line.

   IF sy-subrc <> 0.
     MESSAGE _message TYPE 'E'.
   ENDIF.

ENDFORM.                    " CREATE_DYNAMIC_SQL
*&---------------------------------------------------------------------*
*&      Module  STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
*  SET PF-STATUS 'xxxxxxxx'.
*  SET TITLEBAR 'xxx'.

   IF sqltext IS INITIAL.
     CREATE OBJECT cc_sql
       EXPORTING
         container_name              = 'CC_SQL'
       EXCEPTIONS
         cntl_error                  = 1
         cntl_system_error           = 2
         create_error                = 3
         lifetime_error              = 4
         lifetime_dynpro_dynpro_link = 5
         OTHERS                      = 6.
     CREATE OBJECT sqltext
       EXPORTING
         wordwrap_mode          =
         cl_gui_textedit=>wordwrap_at_fixed_position
         parent                 = cc_sql
       EXCEPTIONS
         error_cntl_create      = 1
         error_cntl_init        = 2
         error_cntl_link        = 3
         error_dp_create        = 4
         gui_type_not_supported = 5
         OTHERS                 = 6.
   ENDIF.

ENDMODULE.                 " STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE user_command_0100 INPUT.

   CASE sy-ucomm.
     WHEN 'EXIT'.
       LEAVE TO SCREEN 0.
     WHEN 'RUN'.
       PERFORM run.
   ENDCASE.

ENDMODULE.                 " USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*&      Form  RUN
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM run .

   DATA: _it_fcat TYPE lvc_t_fcat,
         _cl_salv TYPE REF TO cl_salv_table,
         _cl_func TYPE REF TO cl_salv_functions_list,
         _program TYPE string,
         _subrc   LIKE sy-subrc,
         _isselectquery TYPE boolean.

   DATA:  _it_newtable TYPE REF TO data.
   FIELD-SYMBOLS: <_it_dyntable> TYPE STANDARD TABLE.

   REFRESH it_sql.
   CALL METHOD sqltext->get_text_as_r3table
     IMPORTING
       table = it_sql[].

* Delete initial lines
   DELETE it_sql WHERE tdline IS INITIAL.

* Translate SQL to upper case for string operations
   LOOP AT it_sql.
     TRANSLATE it_sql-tdline TO UPPER CASE.
     CONDENSE it_sql-tdline.
     MODIFY it_sql.
   ENDLOOP.
   CHECK it_sql[] IS NOT INITIAL.

* Check operation is allowed
   PERFORM check_operation TABLES it_sql.

* Generate Subroutine pool SQL
   PERFORM create_dynamic_sql TABLES   it_sql
                              CHANGING _program
                                       _isselectquery.

* Create fieldcatalog for SQL output
   PERFORM create_fieldcatalog TABLES it_sql
                                      _it_fcat.

* Create output table
   CALL METHOD cl_alv_table_create=>create_dynamic_table
     EXPORTING
       it_fieldcatalog = _it_fcat
     IMPORTING
       ep_table        = _it_newtable.
   ASSIGN _it_newtable->* TO <_it_dyntable>.

* Call SQL Query and get results to output table
   PERFORM ('CALL_SQL') IN PROGRAM (_program)
   TABLES <_it_dyntable> CHANGING _subrc IF FOUND.
   IF _subrc IS NOT INITIAL.
     MESSAGE 'No records found !! / Operation cant executed' TYPE 'W'.
     EXIT.
   ELSEIF <_it_dyntable>[] IS INITIAL.
     MESSAGE 'Operation executed' TYPE 'I'.
     EXIT.
   ENDIF.

* ALV output
   CALL METHOD cl_salv_table=>factory
     IMPORTING
       r_salv_table = _cl_salv
     CHANGING
       t_table      = <_it_dyntable>.
   _cl_func = _cl_salv->get_functions( ).
   _cl_func->set_all( ).
   _cl_salv->display( ).

ENDFORM.                    " RUN
*&---------------------------------------------------------------------*
*&      Form  CREATE_FIELDCATALOG
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_IT_SQL  text
*----------------------------------------------------------------------*
FORM create_fieldcatalog  TABLES p_it_sql  STRUCTURE it_sql
                                  p_it_fcat STRUCTURE lvc_s_fcat.

   DATA: BEGIN OF _it_dbfields OCCURS 0,
           tabname    LIKE dd02l-tabname,
           itdbfield TYPE STANDARD TABLE OF dbfield,
         END OF _it_dbfields.

   DATA: _dbfield TYPE dbfield.

   DATA: _it_tablename TYPE TABLE OF typ_tablename    WITH HEADER LINE,
         _it_selfields TYPE TABLE OF typ_selfields WITH HEADER LINE.

   REFRESH p_it_fcat.

* Get table names
   PERFORM get_tables_in_sql TABLES p_it_sql
                                    _it_tablename.

* Get return fields of sql
   PERFORM get_fields_in_sql TABLES p_it_sql
                                    _it_selfields.

* Convert Alias to TableName
   LOOP AT _it_tablename WHERE alias IS NOT INITIAL.
     _it_selfields-tabname = _it_tablename-tabname.
     MODIFY _it_selfields
     TRANSPORTING tabname
     WHERE tabname = _it_tablename-alias.
   ENDLOOP.

* Whole fields of tables in sql
   LOOP AT _it_tablename.
     _it_dbfields-tabname = _it_tablename-tabname.
     CALL FUNCTION 'DB_GET_TABLE_FIELDS'
       EXPORTING
         tabname  = _it_tablename-tabname
       TABLES
         dbfields = _it_dbfields-itdbfield.
     APPEND _it_dbfields.
     CLEAR _it_dbfields.
   ENDLOOP.

* Fill fieldcatalog
   IF _it_selfields[] IS INITIAL.
*   Select * case fieldcatalog
     LOOP AT _it_dbfields.
       LOOP AT _it_dbfields-itdbfield INTO _dbfield.
         READ TABLE p_it_fcat WITH KEY fieldname = _dbfield-name.
         CHECK sy-subrc <> 0.
         p_it_fcat-fieldname = _dbfield-name.
         p_it_fcat-ref_table = _it_dbfields-tabname.
         p_it_fcat-ref_field = _dbfield-name.
         APPEND p_it_fcat.
       ENDLOOP.
     ENDLOOP.
   ELSE.
*   Select fields case fieldcatalog
     LOOP AT _it_selfields.
       READ TABLE p_it_fcat WITH KEY fieldname = _it_selfields-fieldname.
       CHECK sy-subrc <> 0.
       p_it_fcat-fieldname = _it_selfields-fieldname.
       p_it_fcat-ref_table = _it_selfields-tabname.
       p_it_fcat-ref_field = _it_selfields-fieldname.
       APPEND p_it_fcat.
     ENDLOOP.

*   Fill ref_table value of row, if they initial
     LOOP AT p_it_fcat WHERE ref_table IS INITIAL.
       LOOP AT _it_dbfields.
         READ TABLE _it_dbfields-itdbfield INTO _dbfield
         WITH KEY name = p_it_fcat-ref_field.
         CHECK sy-subrc = 0.
         p_it_fcat-ref_table = _it_dbfields-tabname.
         EXIT.
       ENDLOOP.
       MODIFY p_it_fcat.
     ENDLOOP.

   ENDIF.

ENDFORM.                    " CREATE_FIELDCATALOG
*&---------------------------------------------------------------------*
*&      Form  get_tables_in_sql
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_IT_SQL        text
*      -->P_IT_TABLENAME  text
*----------------------------------------------------------------------*
FORM get_tables_in_sql TABLES p_it_sql       STRUCTURE it_sql
                               p_it_tablename TYPE      typ_it_tablename.

   DATA: BEGIN OF _it_split OCCURS 0,
           str LIKE lvc_s_fcat-tabname,
         END OF _it_split.

   REFRESH p_it_tablename.

   LOOP AT p_it_sql WHERE tdline CS 'FROM'
                       OR tdline CS 'JOIN'
                       OR tdline CS 'UPDATE'.

*    TRANSLATE p_it_sql-tdline USING '( ) '.

     REFRESH _it_split.
     SPLIT p_it_sql-tdline+sy-fdpos AT space INTO TABLE _it_split.
     DELETE _it_split WHERE str IS INITIAL
                         OR str = '('
                         OR str = ')'.

     CLEAR _it_split.
     READ TABLE _it_split INDEX 2.
     CHECK _it_split-str IS NOT INITIAL.

     p_it_tablename-tabname = _it_split-str.
     CLEAR _it_split.
     READ TABLE _it_split INDEX 3.
     IF _it_split-str = 'AS'.
       CLEAR _it_split.
       READ TABLE _it_split INDEX 4.
       p_it_tablename-alias = _it_split-str.
     ENDIF.

     COLLECT p_it_tablename.
     CLEAR p_it_tablename.
   ENDLOOP.

ENDFORM.                    " GET_TABLES_IN_SQL
*&---------------------------------------------------------------------*
*&      Form  GET_FIELDS_IN_SQL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_P_IT_SQL  text
*      -->P__IT_SELFIELDS  text
*----------------------------------------------------------------------*
FORM get_fields_in_sql TABLES p_it_sql       STRUCTURE it_sql
                               p_it_selfields TYPE      typ_it_selfields.

   DATA: BEGIN OF _it_split OCCURS 0,
           str LIKE lvc_s_fcat-tabname,
         END OF _it_split.

   DATA: _willexit TYPE boolean.

   REFRESH p_it_selfields.

* Get Selection Fields
   LOOP AT p_it_sql.

     CONDENSE p_it_sql-tdline.

     IF p_it_sql-tdline CS 'FROM' OR
        p_it_sql-tdline CS 'UPDATE'.
       IF sy-fdpos = 0.
         EXIT.
       ENDIF.
       p_it_sql-tdline = p_it_sql-tdline(sy-fdpos).
       _willexit = 'X'.
     ENDIF.

     REFRESH _it_split.
     SPLIT p_it_sql-tdline AT space INTO TABLE _it_split.
     DELETE _it_split WHERE str IS INITIAL
                         OR str = 'SELECT'
                         OR str = 'SINGLE'
                         OR str = '*'
                         OR str = 'INSERT'
                         OR str = 'UPDATE'
                         OR str = 'MODIFY'
                         OR str = 'DELETE'.
     LOOP AT _it_split.
       IF _it_split-str CA '~'.
         SPLIT _it_split-str AT '~' INTO p_it_selfields-tabname
         p_it_selfields-fieldname.
       ELSE.
         p_it_selfields-fieldname = _it_split-str.
       ENDIF.
       APPEND p_it_selfields.
       CLEAR p_it_selfields.
     ENDLOOP.

     CHECK _willexit = 'X'.
     EXIT.
   ENDLOOP.

ENDFORM.                    " GET_FIELDS_IN_SQL
*&---------------------------------------------------------------------*
*&      Form  CHECK_OPERATION
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_IT_SQL  text
*----------------------------------------------------------------------*
FORM check_operation  TABLES   p_it_sql STRUCTURE it_sql.

*   DATA: _ccnocliind LIKE t000-ccnocliind.
*
*   IF gv_changeallowed IS INITIAL.
*     SELECT SINGLE ccnocliind
*     INTO _ccnocliind
*     FROM t000
*     WHERE mandt = sy-mandt.
*     IF sy-subrc = 0 AND _ccnocliind IS INITIAL.
*       gv_changeallowed = 'X'.
*     ELSE.
*       gv_changeallowed = '-'.
*     ENDIF.
*   ENDIF.
*   CHECK gv_changeallowed <> 'X'.

   READ TABLE p_it_sql INDEX 1.

   IF p_it_sql-tdline CS 'DELETE' OR
      p_it_sql-tdline CS 'UPDATE'.
     MESSAGE 'DELETE/UPDATE operations not allowed'
     TYPE 'E'.
   ENDIF.

ENDFORM.                    " CHECK_OPERATION