# 1. 에러 발생 시 에러 위치 선택 후 포커스 처리기능
# 2. order by 구문 추가 시 ALV SORT 처리
# 3. 권한관리
# 4. Standard table 처리 제한( 가능하나 일단 막음)
# 5. DDL, DML 모두 가능
확인 SAP 패치버전
SAP_BASIS 756 0001 SAPK-75601INSAPBASIS SAP Basis Component
SAP_ABA 75G 0001 SAPK-75G01INSAPABA Cross-Application Component
-> 위 버전의 경우 CL_HDB_SQL_EXECUTOR->GET_STATEMENT_TYPE 메서드에서 오류가 있음
23라인 아래쪽에 첨부 로직 추가 하여 overwrite 필요.
추가를 안하는 경우 쿼리 앞부분 공백이나 탭 엔터가 있을 시 비정상 작동함.
SPLIT lv_rest AT space INTO lv_anfang lv_rest. ( line 23 )
*--- Additional code : Denise 2022.04.14 START
DATA: lv_pattern TYPE string.
DATA: lv_match TYPE string.
lv_pattern = '^\s*([^\s]+)\s*'.
FIND REGEX lv_pattern IN lv_anfang SUBMATCHES lv_match.
lv_anfang = lv_match.
*--- Additional code : Denise 2022.04.14 END
*&---------------------------------------------------------------------*
*& Program : HANA SQL Editor
*& Author : Denise
*& Date : 2015.04.15
*& Description :
*&---------------------------------------------------------------------*
* Modification History
*&---------------------------------------------------------------------*
* Name Date Remark
*----------------------------------------------------------------------*
* Denise 2015.04.15 Create PGM
*----------------------------------------------------------------------*
REPORT zhanasql MESSAGE-ID zdk001.
*----------------------------------------------------------------------*
* CONSTANTS
*----------------------------------------------------------------------*
CONSTANTS : gc_enclose TYPE char30 VALUE '#ENCLOSED#'.
CONSTANTS : gc_sys_dev TYPE char10 VALUE 'NHD'.
CONSTANTS : gc_sys_prd TYPE char10 VALUE 'NHP'.
CONSTANTS : gc_select TYPE zesql_auth VALUE '1'.
CONSTANTS : gc_change TYPE zesql_auth VALUE '2'.
CONSTANTS : gc_ddl TYPE zesql_auth VALUE '3'.
CONSTANTS : gc_err_prefix TYPE string
VALUE 'An SQL-Error is occurred:'.
*----------------------------------------------------------------------*
* Types
*----------------------------------------------------------------------*
TYPES: gty_textline TYPE c LENGTH 4000.
TYPES: gty_texttab TYPE TABLE OF gty_textline.
TYPES: BEGIN OF gty_s_alias,
fieldname LIKE dd03l-fieldname,
alias LIKE dd03l-fieldname,
END OF gty_s_alias.
TYPES: gty_t_alias TYPE TABLE OF gty_s_alias
WITH DEFAULT KEY.
TYPES: BEGIN OF gty_s_selected,
obj TYPE char80,
line TYPE char10,
col TYPE char10,
pos TYPE char10,
END OF gty_s_selected.
TYPES: BEGIN OF gty_s_dictionary,
level TYPE numc4,
alias TYPE char30,
table TYPE char30,
field TYPE char30,
column TYPE char30,
END OF gty_s_dictionary.
TYPES: gty_t_dictionary TYPE TABLE OF gty_s_dictionary.
*----------------------------------------------------------------------*
* Global variables
*----------------------------------------------------------------------*
DATA: gv_ok_code TYPE sy-ucomm,
gv_ok_save TYPE sy-ucomm.
DATA: gv_sql TYPE string.
DATA: gv_offset TYPE i.
DATA: gv_lines TYPE char30.
DATA: gv_sql_code TYPE c LENGTH 20.
DATA: go_hdb_sql TYPE REF TO cl_hdb_sql_executor.
DATA: go_dbsys TYPE REF TO cl_db6_sys.
DATA: go_con_ref TYPE REF TO cl_sql_connection.
DATA: go_ref TYPE REF TO data.
DATA: gs_selected TYPE gty_s_selected.
DATA: gt_comps TYPE abap_component_tab WITH HEADER LINE.
DATA: gt_dictionary TYPE gty_t_dictionary WITH HEADER LINE.
DATA: gs_table LIKE LINE OF gt_dictionary.
DATA: gt_text TYPE gty_texttab WITH HEADER LINE.
FIELD-SYMBOLS : <gt_table> TYPE STANDARD TABLE.
*----------------------------------------------------------------------*
* ALV Objects
*----------------------------------------------------------------------*
DATA: go_container1 TYPE REF TO cl_gui_custom_container,
go_grid TYPE REF TO cl_gui_alv_grid,
gs_layout TYPE lvc_s_layo.
DATA: gt_sort TYPE lvc_t_sort WITH HEADER LINE.
DATA: go_container TYPE REF TO cl_gui_custom_container,
go_editor TYPE REF TO cl_gui_textedit.
DATA: gv_refresh TYPE c.
*----------------------------------------------------------------------*
* ALV Objects
*----------------------------------------------------------------------*
CLASS : gcl_event DEFINITION DEFERRED.
DATA : go_receiver TYPE REF TO gcl_event.
CLASS : gcl_event DEFINITION.
PUBLIC SECTION.
METHODS : handle_after_command
FOR EVENT after_user_command
OF cl_gui_alv_grid
IMPORTING sender
e_ucomm
e_saved
e_not_processed.
ENDCLASS.
CLASS : gcl_event IMPLEMENTATION.
METHOD handle_after_command.
PERFORM handle_after_command USING sender
e_ucomm
e_saved
e_not_processed.
ENDMETHOD.
ENDCLASS.
*----------------------------------------------------------------------*
* $_set_sort : for alv sort
*----------------------------------------------------------------------*
DEFINE $_set_sort.
gt_sort-fieldname = &1.
IF &2 IS INITIAL.
gt_sort-up = 'X'.
ELSE.
gt_sort-down = 'X'.
ENDIF.
APPEND gt_sort.
CLEAR gt_sort.
END-OF-DEFINITION.
*----------------------------------------------------------------------*
* Screen process
*----------------------------------------------------------------------*
START-OF-SELECTION.
IF sy-sysid EQ gc_sys_prd.
PERFORM check_auth USING gc_select.
IF sy-subrc <> 0.
MESSAGE i393(nr) DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDIF.
CALL SCREEN 0100.
*----------------------------------------------------------------------*
* PAI
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Module EXIT_0100 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE exit_0100 INPUT.
CLEAR gv_ok_save.
gv_ok_save = gv_ok_code.
CLEAR gv_ok_code.
CASE gv_ok_save.
WHEN 'BACK' OR 'CANC' OR 'EXIT'.
LEAVE TO SCREEN 0.
ENDCASE.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0100 INPUT
*&---------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
CLEAR gv_ok_save.
gv_ok_save = gv_ok_code.
CLEAR gv_ok_code.
CASE gv_ok_save.
WHEN 'EXEC'.
PERFORM get_script.
PERFORM execute_sql USING gv_sql.
WHEN 'EXPL'.
PERFORM get_script.
PERFORM explain_sql USING gv_sql.
ENDCASE.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0200 INPUT
*&---------------------------------------------------------------------*
MODULE user_command_0200 INPUT.
CLEAR gv_ok_save.
gv_ok_save = gv_ok_code.
CLEAR gv_ok_code.
CASE gv_ok_save.
WHEN 'REFRESH'.
PERFORM get_sql_result USING gv_sql ' '.
PERFORM create_alv USING gv_sql.
PERFORM set_refresh CHANGING gv_refresh.
PERFORM refresh_alv USING gv_refresh go_grid.
ENDCASE.
ENDMODULE.
*----------------------------------------------------------------------*
* PBO
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
SET PF-STATUS 'S100'.
SET TITLEBAR 'T100'.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module INIT_EDITOR OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE init_editor OUTPUT.
IF go_container IS INITIAL.
* create container control for sql editor
CREATE OBJECT go_container
EXPORTING
container_name = 'GC_EDITOR'
EXCEPTIONS
OTHERS = 1.
IF sy-subrc <> 0.
MESSAGE ID 'S1' TYPE 'E' NUMBER 899
WITH TEXT-m20 space space space.
ENDIF.
* create text editor object and link to container
CREATE OBJECT go_editor
EXPORTING
parent = go_container
wordwrap_mode = 1 " at spec. column
EXCEPTIONS
OTHERS = 1.
IF sy-subrc <> 0.
MESSAGE ID 'S1' TYPE 'E' NUMBER 899
WITH TEXT-m20 space space space.
ENDIF.
* Set fixed font
CALL METHOD go_editor->set_font_fixed
EXPORTING
mode = cl_gui_textedit=>true
EXCEPTIONS
error_cntl_call_method = 1
invalid_parameter = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE ID 'S1' TYPE 'E' NUMBER 899
WITH TEXT-m20 space space space.
ENDIF.
* Set autoindent mode on
CALL METHOD go_editor->set_autoindent_mode
EXPORTING
auto_indent = cl_gui_textedit=>true
EXCEPTIONS
error_cntl_call_method = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID 'S1' TYPE 'E' NUMBER 899
WITH TEXT-m20 space space space.
ENDIF.
ENDIF.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module STATUS_0200 OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE status_0200 OUTPUT.
CLEAR gv_lines.
DESCRIBE TABLE <gt_table> LINES gv_lines.
CONDENSE gv_lines.
SET PF-STATUS 'S200'.
SET TITLEBAR 'T200' WITH gv_lines.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module INIT_ALV OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE init_alv OUTPUT.
PERFORM create_alv USING gv_sql.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Form GET_SCRIPT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_script .
CLEAR : gt_text, gt_text[]
, gv_sql.
CALL METHOD go_editor->get_text_as_r3table
* EXPORTING
* only_when_modified = false
IMPORTING
table = gt_text[]
* is_modified =
EXCEPTIONS
error_dp = 1
error_cntl_call_method = 2
error_dp_create = 3
potential_data_loss = 4
OTHERS = 5.
IF sy-subrc <> 0 AND sy-subrc <> 4.
MESSAGE ID 'S1' TYPE 'E' NUMBER 591.
ENDIF.
CHECK gt_text[] IS NOT INITIAL.
CALL METHOD go_editor->set_selection_pos
EXPORTING
from_line = 0
from_pos = 0.
LOOP AT gt_text.
IF sy-tabix = 1.
gv_sql = gt_text.
ELSE.
CONCATENATE gv_sql
cl_abap_char_utilities=>cr_lf
gt_text
INTO gv_sql.
ENDIF.
ENDLOOP.
* REPLACE ALL OCCURRENCES OF REGEX '/\*.*\*/'
* IN gv_sql WITH ' '.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form EXECUTE_SQL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_SQL text
*----------------------------------------------------------------------*
FORM execute_sql USING pv_sql.
DATA : lv_is_select TYPE c.
DATA : lv_table TYPE tabname.
DATA : lv_command TYPE char30.
DATA : lv_message TYPE char255.
DATA : lv_log TYPE c.
IF pv_sql IS INITIAL.
MESSAGE ID 'S1' TYPE 'E' NUMBER 899
WITH 'Statement is empty.'
DISPLAY LIKE 'E'.
EXIT.
ENDIF.
CLEAR gs_selected.
FREE go_ref.
PERFORM get_sql_instance.
PERFORM check_statement USING pv_sql.
IF sy-subrc IS NOT INITIAL.
PERFORM set_line_selected.
EXIT.
ENDIF.
lv_is_select = go_hdb_sql->check_statement_type(
EXPORTING statement = pv_sql
).
IF sy-sysid EQ gc_sys_prd.
PERFORM check_auth USING gc_select.
IF sy-subrc <> 0.
MESSAGE e359(gi).
ENDIF.
ENDIF.
IF lv_is_select IS INITIAL.
FIND REGEX '^\s*(.{6})' IN pv_sql
SUBMATCHES lv_command.
TRANSLATE lv_command TO UPPER CASE.
IF lv_command = 'SELECT'.
lv_is_select = 'X'.
ENDIF.
ENDIF.
IF lv_is_select IS NOT INITIAL.
lv_log = ' '.
ELSE.
lv_log = 'X'.
IF sy-sysid EQ gc_sys_prd.
PERFORM check_auth USING gc_change.
IF sy-subrc <> 0.
MESSAGE e369(38).
ENDIF.
ENDIF.
*--- Check CBO table
PERFORM get_dml_table USING pv_sql
CHANGING lv_table
lv_command.
IF lv_table CP 'Z*'
OR lv_table CP 'Y*' .
ELSE.
MESSAGE e554(ad) WITH lv_table.
ENDIF.
ENDIF.
*--- Execute SQL
PERFORM get_sql_result USING pv_sql lv_log.
IF lv_is_select IS NOT INITIAL.
IF <gt_table> IS ASSIGNED.
CALL SCREEN 0200.
ENDIF.
ELSE.
TRANSLATE lv_command TO LOWER CASE.
CONCATENATE lv_command 'd' INTO lv_message.
CONCATENATE 'Data in' lv_table lv_message
INTO lv_message
SEPARATED BY space.
MESSAGE lv_message TYPE 'S'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CREATE_ALV
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM create_alv USING pv_sql.
DATA : lt_fieldcat TYPE lvc_t_fcat WITH HEADER LINE,
lt_field TYPE slis_t_fieldcat_alv.
DATA : lo_elem TYPE REF TO cl_abap_elemdescr,
ls_dfies TYPE dfies,
lv_pos TYPE i.
*--- Find Table info ( for ALV output )
* TRY.
* PERFORM get_field_info USING gv_sql.
* CATCH cx_root.
* CLEAR gt_dictionary[].
* ENDTRY.
LOOP AT gt_comps.
lo_elem ?= gt_comps-type.
lv_pos = lv_pos + 1.
lt_fieldcat-fieldname = gt_comps-name.
IF gt_dictionary[] IS NOT INITIAL.
CLEAR : gt_dictionary.
READ TABLE gt_dictionary WITH KEY level = 1
column = gt_comps-name
BINARY SEARCH.
lt_fieldcat-ref_table = gt_dictionary-table.
lt_fieldcat-ref_field = gt_dictionary-field.
ENDIF.
IF lt_fieldcat-ref_table IS INITIAL.
IF lo_elem->is_ddic_type( ) EQ 'X'.
ls_dfies = lo_elem->get_ddic_field( ).
lt_fieldcat-outputlen = ls_dfies-outputlen.
ELSE.
lt_fieldcat-outputlen = lo_elem->output_length.
ENDIF.
lt_fieldcat-inttype = lo_elem->type_kind.
ENDIF.
lt_fieldcat-seltext =
lt_fieldcat-tooltip =
lt_fieldcat-coltext = gt_comps-name.
lt_fieldcat-lowercase = 'X'.
lt_fieldcat-col_pos = lv_pos.
APPEND lt_fieldcat.
CLEAR lt_fieldcat.
ENDLOOP.
IF go_container1 IS INITIAL.
CREATE OBJECT go_container1
EXPORTING
container_name = 'GC_CONTAINER'.
CREATE OBJECT go_grid
EXPORTING
i_parent = go_container1.
ENDIF.
PERFORM set_layout.
PERFORM set_event USING go_grid.
PERFORM set_sort USING pv_sql.
CALL METHOD go_grid->set_table_for_first_display
EXPORTING
is_layout = gs_layout
CHANGING
it_outtab = <gt_table>
it_fieldcatalog = lt_fieldcat[]
it_sort = gt_sort[]
EXCEPTIONS
invalid_parameter_combination = 1
program_error = 2
too_many_lines = 3
OTHERS = 4.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
PERFORM set_frontend_layout USING go_grid
gs_layout.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form REFRESH_ALV
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_REFRESH text
*----------------------------------------------------------------------*
FORM refresh_alv USING pv_refresh
po_grid TYPE REF TO cl_gui_alv_grid.
DATA: ls_stbl TYPE lvc_s_stbl.
ls_stbl-row = 'X'.
ls_stbl-col = 'X'.
CHECK pv_refresh IS NOT INITIAL.
po_grid->refresh_table_display(
EXPORTING is_stable = ls_stbl
i_soft_refresh = ' '
).
CLEAR pv_refresh.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_LAYOUT
*&---------------------------------------------------------------------*
FORM set_layout .
CLEAR : gs_layout.
gs_layout-sel_mode = 'D'.
gs_layout-zebra = 'X'.
gs_layout-col_opt = 'X'.
gs_layout-cwidth_opt = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_EVENT
*&---------------------------------------------------------------------*
FORM set_event USING po_grid TYPE REF TO cl_gui_alv_grid.
IF go_receiver IS INITIAL.
CREATE OBJECT go_receiver.
ENDIF.
SET HANDLER go_receiver->handle_after_command FOR po_grid.
CALL METHOD po_grid->register_edit_event
EXPORTING
i_event_id = cl_gui_alv_grid=>mc_evt_modified
EXCEPTIONS
error = 1
OTHERS = 2.
CALL METHOD po_grid->register_edit_event
EXPORTING
i_event_id = cl_gui_alv_grid=>mc_evt_enter
EXCEPTIONS
error = 1
OTHERS = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_SORT
*&---------------------------------------------------------------------*
FORM set_sort USING pv_sql.
DATA: lv_sql TYPE string.
DATA: lv_select TYPE string.
DATA: lv_order TYPE string.
DATA: lv_pattern TYPE string.
DATA: lv_field TYPE fieldname.
DATA: lv_ascend TYPE fieldname.
DATA: lv_desc TYPE c.
DATA: lo_regex TYPE REF TO cl_abap_regex.
DATA: lt_split TYPE stringtab WITH HEADER LINE.
DATA: lt_select TYPE stringtab WITH HEADER LINE.
DATA: lt_alias TYPE gty_t_alias WITH HEADER LINE.
CLEAR: gt_sort[].
CHECK pv_sql IS NOT INITIAL.
lv_sql = pv_sql.
TRANSLATE lv_sql TO UPPER CASE.
*--- Remove Comments
REPLACE ALL OCCURRENCES OF REGEX '\/\*[^\*]+\*\/' IN lv_sql WITH ' '.
*--- Remove carriage return & line feed
REPLACE ALL OCCURRENCES OF REGEX '\r\n' IN lv_sql WITH ' '.
*--- Replace CASE statement to alias
lv_pattern = 'case\b(?!END).+?\bEND (?:AS)* *(\b(?!FROM).+?\b)'.
PERFORM get_regex USING lv_pattern
CHANGING lo_regex.
REPLACE ALL OCCURRENCES OF REGEX lo_regex IN lv_sql WITH '$1'.
*--- SELECT & ORDER BY clause
lv_pattern = '^\s*SELECT (?:TOP [0-9]+)*\s*(?:DISTINCT)*\s*((?!FROM).+?)FROM.+ORDER BY ((?!ORDER BY).+)'.
PERFORM get_regex USING lv_pattern
CHANGING lo_regex.
CHECK lo_regex IS NOT INITIAL.
DATA: lv_offset TYPE i.
DATA: lv_length TYPE i.
DATA: lv_length1 TYPE i.
FIND REGEX lo_regex IN lv_sql
SUBMATCHES lv_select lv_order.
CHECK sy-subrc IS INITIAL.
IF lv_select CS '*'.
PERFORM get_select_sub_clause CHANGING lv_select
lv_sql
lo_regex.
ENDIF.
**--- Remove table alias
* lv_pattern = '[^\.]+\.([^\,]+) *(,*)'.
* PERFORM get_regex USING lv_pattern
* CHANGING lo_regex.
* REPLACE ALL OCCURRENCES OF REGEX lo_regex IN lv_select WITH '$1$2'.
* REPLACE ALL OCCURRENCES OF REGEX lo_regex IN lv_order WITH '$1$2'.
CONDENSE lv_select.
SPLIT lv_select AT ',' INTO TABLE lt_select.
LOOP AT lt_select.
lv_pattern = '([^\s]+\(* *[^\s]* * \)*)\s*(?:AS)*\s*(.+)*'.
PERFORM get_regex USING lv_pattern
CHANGING lo_regex.
FIND REGEX lo_regex IN lt_select
SUBMATCHES lt_alias-fieldname lt_alias-alias.
IF sy-subrc IS NOT INITIAL.
ENDIF.
CONDENSE: lt_alias-fieldname, lt_alias-alias.
APPEND lt_alias.
ENDLOOP.
SORT lt_alias BY fieldname.
CONDENSE lv_order.
SPLIT lv_order AT ',' INTO TABLE lt_split.
CHECK lt_split[] IS NOT INITIAL.
LOOP AT lt_split.
CLEAR: lv_desc.
CONDENSE: lt_split.
*--- ASC, DESC Check.
lv_pattern = '([^\s]+\(* *[^\s]* *\)*)\s*([^\s]*)'.
PERFORM get_regex USING lv_pattern
CHANGING lo_regex.
FIND REGEX lo_regex IN lt_split
SUBMATCHES lv_field lv_ascend.
READ TABLE lt_alias WITH KEY fieldname = lv_field
BINARY SEARCH.
CHECK sy-subrc IS INITIAL.
IF lv_ascend CP 'DESC*'.
lv_desc = 'X'.
ENDIF.
IF lt_alias-alias IS NOT INITIAL.
$_set_sort : lt_alias-alias lv_desc.
ELSE.
*--- Remove table alias
lv_pattern = '[^\.]+\.([^\,]+) *(,*)'.
PERFORM get_regex USING lv_pattern
CHANGING lo_regex.
REPLACE ALL OCCURRENCES OF REGEX lo_regex IN lv_field WITH '$1$2'.
$_set_sort : lv_field lv_desc.
ENDIF.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_REGEX
*&---------------------------------------------------------------------*
FORM get_regex USING pv_pattern
CHANGING po_regex TYPE REF TO cl_abap_regex.
CLEAR po_regex.
po_regex = cl_abap_regex=>create_pcre( pattern = pv_pattern
ignore_case = 'X'
extended = ' '
).
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_SELECT_SUB_CLAUSE
*&---------------------------------------------------------------------*
FORM get_select_sub_clause
CHANGING pv_select
pv_sql
po_regex TYPE REF TO cl_abap_regex.
DATA: lv_offset TYPE i.
DATA: lv_length TYPE i.
DATA: lv_sql TYPE string.
DATA: lv_select TYPE string.
DATA: lv_pattern TYPE string.
DATA: lv_alias TYPE string.
FIND pv_select IN pv_sql
MATCH OFFSET lv_offset
MATCH LENGTH lv_length.
IF sy-subrc IS INITIAL.
lv_length = lv_length + lv_offset + strlen( 'FROM' ).
FIND 'SELECT' IN pv_sql+lv_length(*)
MATCH OFFSET lv_offset.
IF sy-subrc IS INITIAL.
lv_length = lv_length + lv_offset.
lv_sql = pv_sql+lv_length(*).
lv_pattern = '^\s*SELECT ((?!FROM).+?)FROM'.
PERFORM get_regex USING lv_pattern
CHANGING po_regex.
FIND REGEX po_regex IN lv_sql
SUBMATCHES lv_select.
IF sy-subrc IS INITIAL.
IF lv_select CS '*'.
PERFORM get_select_sub_clause CHANGING lv_select
lv_sql
po_regex.
ENDIF.
FIND REGEX '([^\.]+)\.\*' IN pv_select
SUBMATCHES lv_alias.
IF sy-subrc IS INITIAL.
lv_pattern = '([^\.]+)(\.[^\,]+ *,*)'.
PERFORM get_regex USING lv_pattern
CHANGING po_regex.
CONCATENATE lv_alias '$2'
INTO lv_alias.
REPLACE ALL OCCURRENCES OF REGEX po_regex IN lv_select WITH lv_alias.
lv_pattern = '([^\s,]+)\.([^\s,]+)\s*(?:AS)\s+([^,\s]+)+\s+'.
PERFORM get_regex USING lv_pattern
CHANGING po_regex.
REPLACE ALL OCCURRENCES OF REGEX po_regex IN lv_select
WITH '$1.$3'.
REPLACE REGEX '\s*[^\.]+\.\*' IN pv_select WITH lv_select.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form HANDLE_AFTER_COMMAND
*&---------------------------------------------------------------------*
FORM handle_after_command
USING po_sender TYPE REF TO cl_gui_alv_grid
pv_ucomm
pv_saved
pv_not_processed.
DATA : lv_set TYPE c.
FIELD-SYMBOLS : <ls_table> TYPE any,
<lv_value> TYPE any.
IF pv_ucomm CP '&*'.
lv_set = 'X'.
ELSE.
ENDIF.
CHECK lv_set IS NOT INITIAL.
PERFORM set_frontend_layout USING po_sender
gs_layout.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_FRONTEND_LAYOUT
*&---------------------------------------------------------------------*
FORM set_frontend_layout
USING po_sender TYPE REF TO cl_gui_alv_grid
ps_layout TYPE lvc_s_layo.
po_sender->set_frontend_layout( ps_layout ).
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_SQL_INSTANCE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_sql_instance .
CHECK go_dbsys IS INITIAL.
TRY.
go_dbsys = cl_db6_sys=>get_sys_ref(
EXPORTING system_id = sy-sysid
).
CATCH cx_db6_sys .
ENDTRY.
go_hdb_sql ?= cl_hdb_sql_executor=>get_instance( go_dbsys ).
ENDFORM.
*&---------------------------------------------------------------------*
*& Form EXPLAIN_SQL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_SQL text
*----------------------------------------------------------------------*
FORM explain_sql USING pv_sql.
CALL FUNCTION 'DB_EXPLAIN_PLAN'
EXPORTING
statement = pv_sql
* no_display = pv_no_display
EXCEPTIONS
explain_failed = 1
OTHERS = 2.
IF sy-subrc IS NOT INITIAL.
PERFORM set_line_selected.
EXIT.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_REFRESH
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_GV_REFRESH text
*----------------------------------------------------------------------*
FORM set_refresh CHANGING pv_refresh.
pv_refresh = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_STATEMENT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_SQL text
*----------------------------------------------------------------------*
FORM check_statement USING pv_sql.
DATA : lo_exept TYPE REF TO cx_sql_exception.
DATA : lv_text TYPE string.
DATA : lv_plan_id TYPE c LENGTH 256.
DATA : lv_subrc TYPE sy-subrc.
PERFORM before_explain_plan USING ' '
CHANGING lv_plan_id.
PERFORM db_explain_plan USING pv_sql
lv_plan_id
CHANGING sy-subrc.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_SQL_RESULT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_SQL text
*----------------------------------------------------------------------*
FORM get_sql_result USING pv_sql pv_logging.
DATA: lo_struct TYPE REF TO cl_abap_structdescr.
DATA: lo_except TYPE REF TO cx_static_check.
DATA: lo_sql_ex TYPE REF TO cx_sql_exception.
DATA: lv_text TYPE string.
IF pv_sql IS INITIAL.
MESSAGE ID 'S1' TYPE 'E' NUMBER 899
WITH 'Statement is empty.'.
ENDIF.
*--- if CL_DB6_TREE_NAVIGATOR is not bound log will not work.
TRY.
go_hdb_sql->exec_query_dyn(
EXPORTING im_statement = pv_sql
im_system = go_dbsys
im_logging = pv_logging
IMPORTING ex_structdescr = lo_struct
ex_result_ref = go_ref
).
CATCH cx_sql_exception INTO lo_except.
lv_text = lo_except->if_message~get_longtext( ).
IF lv_text IS INITIAL.
lo_sql_ex ?= lo_except.
lv_text = lo_sql_ex->sql_message.
ENDIF.
CATCH cx_dba_adbc INTO lo_except.
lv_text = lo_except->if_message~get_longtext( ).
CATCH cx_dba_root INTO lo_except.
lv_text = lo_except->if_message~get_longtext( ).
ENDTRY.
IF lo_except IS NOT INITIAL.
MESSAGE lv_text TYPE 'E'.
ENDIF.
IF <gt_table> IS ASSIGNED.
UNASSIGN <gt_table>.
ENDIF.
CLEAR : gt_comps[].
IF lo_struct IS BOUND.
gt_comps[] = lo_struct->get_components( ).
ENDIF.
CHECK go_ref IS NOT INITIAL.
ASSIGN go_ref->* TO <gt_table>.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_FIELD_INFO
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SQL text
*----------------------------------------------------------------------*
FORM get_field_info USING pv_sql.
CONSTANTS : lc_select TYPE char30 VALUE 'SELECT',
lc_from TYPE char30 VALUE 'FROM',
lc_where TYPE char30 VALUE 'WHERE',
lc_group TYPE char30 VALUE 'GROUP BY',
lc_order TYPE char30 VALUE 'ORDER BY'.
CONSTANTS : lc_prefix TYPE char30 VALUE 'FROM +[^\)]+'.
DATA : lt_split TYPE stringtab WITH HEADER LINE.
DATA : lt_match TYPE match_result_tab WITH HEADER LINE.
DATA : lt_join TYPE match_result_tab WITH HEADER LINE.
DATA : lt_submatch TYPE submatch_result_tab WITH HEADER LINE.
DATA : lv_offset_fr TYPE i,
lv_offset_to TYPE i,
lv_previous TYPE i.
DATA : lv_offset TYPE i,
lv_length TYPE i.
DATA : lv_level LIKE gt_dictionary-level.
DATA : lv_count TYPE i.
DATA : lv_matches TYPE i.
DATA : lv_tabix TYPE sy-tabix.
DATA : lv_pattern TYPE string.
DATA : lv_replace TYPE string.
DATA : lv_sql TYPE string.
DATA : lv_temp TYPE string.
DATA : lt_comps LIKE TABLE OF gt_comps WITH HEADER LINE.
CLEAR : gt_dictionary, gt_dictionary[].
lt_comps[] = gt_comps[].
SORT lt_comps BY name.
FIND ALL OCCURRENCES OF lc_from IN pv_sql IGNORING CASE
RESULTS lt_match[].
*--- Get Field list
LOOP AT lt_match.
lv_level = sy-tabix.
IF sy-tabix EQ 1.
lv_offset_fr = 0.
lv_offset_to = lt_match-offset.
lv_previous = lt_match-offset.
ELSE.
FIND REGEX 'SELECT +' IN SECTION OFFSET lv_previous
OF pv_sql IGNORING CASE
MATCH OFFSET lv_offset_fr
MATCH LENGTH lv_length.
lv_offset_to = lt_match-offset - lv_offset_fr.
lv_previous = lt_match-offset.
ENDIF.
lv_temp = pv_sql+lv_offset_fr(lv_offset_to).
PERFORM remove_scalar_func CHANGING lv_temp.
CONCATENATE '.*' lc_select INTO lv_pattern.
REPLACE REGEX lv_pattern IN lv_temp
WITH lc_select
IGNORING CASE.
SPLIT lv_temp AT ','
INTO TABLE lt_split[].
LOOP AT lt_split. "#EC CI_NESTED
CLEAR : lv_offset, lv_length, lv_pattern.
*--- Remove SELECT .. TOP ..
CONCATENATE lc_select ' (top [0-9]+)* *'
INTO lv_pattern.
FIND REGEX lv_pattern IN lt_split IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length.
IF sy-subrc IS INITIAL.
lv_offset = lv_offset + lv_length.
SHIFT lt_split BY lv_length PLACES LEFT.
REPLACE REGEX lc_select IN lt_split WITH ' ' IGNORING CASE.
ENDIF.
CONDENSE lt_split.
PERFORM remove_brace CHANGING lt_split.
*--- Get Table Alias
CLEAR : lv_offset, lv_length.
FIND REGEX ' *([^\(]+)\.' IN lt_split IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length
SUBMATCHES gt_dictionary-alias.
IF sy-subrc IS INITIAL.
CONDENSE gt_dictionary-alias.
* SHIFT lt_split BY lv_offset PLACES LEFT.
SHIFT lt_split BY lv_length PLACES LEFT.
lv_offset = lv_offset + lv_length.
ELSE.
CLEAR lv_offset.
ENDIF.
*--- Get fieldname
FIND REGEX '\.*([^\.\s]+) +(?:AS)* *([^\s]+)'
IN lt_split
IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length
SUBMATCHES gt_dictionary-field
gt_dictionary-column.
IF sy-subrc IS NOT INITIAL.
FIND REGEX '\.*([^\.\s]+)' IN lt_split IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length
SUBMATCHES gt_dictionary-field.
IF sy-subrc IS NOT INITIAL.
gt_dictionary-field = lt_split.
ENDIF.
gt_dictionary-column = gt_dictionary-field.
ENDIF.
CONDENSE gt_dictionary-column.
CONDENSE gt_dictionary-field.
*--- Get Table
TRANSLATE gt_dictionary-table TO UPPER CASE.
TRANSLATE gt_dictionary-field TO UPPER CASE.
TRANSLATE gt_dictionary-column TO UPPER CASE.
* READ TABLE lt_comps WITH KEY name = gt_dictionary-column
* TRANSPORTING NO FIELDS
* BINARY SEARCH.
* CHECK sy-subrc IS INITIAL.
gt_dictionary-level = lv_level.
APPEND gt_dictionary.
CLEAR gt_dictionary.
ENDLOOP.
ENDLOOP.
SORT gt_dictionary BY level alias.
*--- Get tables
SORT lt_match BY offset DESCENDING.
DESCRIBE TABLE lt_match LINES lv_count.
lv_count = lv_count + 1.
lv_sql = pv_sql.
LOOP AT lt_match.
lv_level = lv_count - sy-tabix.
lv_offset_fr = lt_match-offset.
*--- Remove nested subQuery
IF sy-tabix > 1.
lv_pattern = '\( *SELECT +.+ +FROM +[^\)]+ *\)'.
REPLACE REGEX lv_pattern
IN SECTION OFFSET lv_offset_fr
OF lv_sql
WITH gc_enclose
IGNORING CASE.
ENDIF.
CLEAR : lv_offset, lv_length, lv_pattern, lv_temp.
lv_temp = lv_sql+lv_offset_fr(*).
FIND REGEX lc_prefix
IN lv_temp
IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length.
IF sy-subrc IS INITIAL.
lv_temp = lv_temp(lv_length).
ENDIF.
CLEAR lv_offset.
FIND REGEX lc_where
IN lv_temp
IGNORING CASE
MATCH OFFSET lv_offset.
IF sy-subrc IS NOT INITIAL.
FIND REGEX lc_group
IN lv_temp
IGNORING CASE
MATCH OFFSET lv_offset.
ENDIF.
IF sy-subrc IS NOT INITIAL.
FIND REGEX lc_order
IN lv_temp
IGNORING CASE
MATCH OFFSET lv_offset.
ENDIF.
IF lv_offset IS NOT INITIAL.
lv_temp = lv_temp(lv_offset).
ENDIF.
*--- Remove FROM literal
SHIFT lv_temp BY 4 PLACES LEFT.
PERFORM remove_scalar_func CHANGING lv_temp.
lv_pattern = '(INNER)* *(LEFT OUTER)* *JOIN' .
FIND REGEX lv_pattern IN lv_temp
IGNORING CASE.
IF sy-subrc IS INITIAL.
*--- Remove JOIN EXPRESSION
CONCATENATE lv_pattern '(.+).+ON.+(AND.+)*'
INTO lv_pattern.
FIND ALL OCCURRENCES OF REGEX lv_pattern IN lv_temp
IGNORING CASE
RESULTS lt_join[].
IF sy-subrc IS INITIAL.
SORT lt_join BY offset DESCENDING.
LOOP AT lt_join.
CLEAR: lt_submatch, lv_replace, lv_matches.
lt_submatch[] = lt_join-submatches.
FIND ALL OCCURRENCES OF REGEX '(\([^\)]*\))' IN lv_pattern
MATCH COUNT lv_matches.
lv_matches = lv_matches - 1.
READ TABLE lt_submatch INDEX lv_matches.
CONCATENATE ','
lv_temp+lt_submatch-offset(lt_submatch-length)
INTO lv_replace.
REPLACE REGEX lv_pattern IN SECTION OFFSET lt_join-offset
OF lv_temp
WITH lv_replace
IGNORING CASE.
ENDLOOP.
ENDIF.
ENDIF.
SPLIT lv_temp AT ','
INTO TABLE lt_split[].
LOOP AT lt_split. "#EC CI_NESTED
CLEAR : gs_table.
CONDENSE lt_split.
FIND REGEX '([^\s]+) +(?:AS)* *([^\s]+)'
IN lt_split
IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length
SUBMATCHES gs_table-table
gs_table-alias.
IF sy-subrc IS NOT INITIAL.
FIND REGEX '([^\s]+)' IN lt_split IGNORING CASE
MATCH OFFSET lv_offset
MATCH LENGTH lv_length
SUBMATCHES gs_table-table.
ENDIF.
CLEAR gt_dictionary.
READ TABLE gt_dictionary WITH KEY level = lv_level
alias = gs_table-alias
TRANSPORTING NO FIELDS
BINARY SEARCH.
IF sy-subrc IS INITIAL.
LOOP AT gt_dictionary FROM sy-tabix.
IF gt_dictionary-level NE lv_level
OR gt_dictionary-alias NE gs_table-alias.
EXIT.
ENDIF.
gt_dictionary-table = gs_table-table.
TRANSLATE gt_dictionary-table TO UPPER CASE.
MODIFY gt_dictionary.
ENDLOOP.
ENDIF.
ENDLOOP.
ENDLOOP.
SORT gt_dictionary BY level column.
PERFORM convert_asterisk.
LOOP AT gt_dictionary.
CASE gt_dictionary-table.
WHEN gc_enclose OR space.
PERFORM get_enclosed_table_name
USING gt_dictionary-level
gt_dictionary-field
CHANGING gt_dictionary-table.
ENDCASE.
MODIFY gt_dictionary.
ENDLOOP.
PERFORM check_dd03l.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form REMOVE_BRACE
*&---------------------------------------------------------------------*
FORM remove_brace CHANGING pv_split.
DATA : lv_match TYPE string.
DATA : lv_alias TYPE string.
FIND REGEX '\( *(.+) *(?:,.+)* *\) *(?:AS)* *(.+)'
IN pv_split
IGNORING CASE
SUBMATCHES lv_match
lv_alias.
IF sy-subrc IS INITIAL.
CONCATENATE lv_match lv_alias
INTO pv_split.
FIND REGEX '\(' IN pv_split IGNORING CASE.
IF sy-subrc IS INITIAL.
PERFORM remove_brace CHANGING pv_split.
ENDIF.
ENDIF.
FIND REGEX 'case +.* *when.+then(.+) +end +(?:AS)* (.+)'
IN pv_split
IGNORING CASE
SUBMATCHES lv_match
lv_alias.
IF sy-subrc IS INITIAL.
CONCATENATE lv_match 'AS' lv_alias
INTO pv_split
SEPARATED BY space.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form REMOVE_SCALAR_FUNC
*&---------------------------------------------------------------------*
FORM remove_scalar_func CHANGING pv_split.
DATA : lv_pattern TYPE string.
CONCATENATE '(,*) *'
'[^,\(]+\(([^,\)\(]+)'
'(,[^\)\(]*)*\) *as'
INTO lv_pattern.
REPLACE ALL OCCURRENCES OF REGEX lv_pattern IN pv_split
WITH '$1 $2 as'
IGNORING CASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_DML_TABLE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_SQL text
* <--P_TABLE text
*----------------------------------------------------------------------*
FORM get_dml_table USING pv_sql
CHANGING pv_table
pv_command.
DATA : lv_ddl TYPE c.
DATA : lv_sql TYPE string.
DATA : lv_pattern TYPE char30.
* pv_command = pv_sql(6).
lv_pattern = `^\s*(.{6})`.
FIND REGEX lv_pattern IN pv_sql
IGNORING CASE
SUBMATCHES pv_command.
TRANSLATE pv_command TO UPPER CASE.
*--- Check SQL type
CLEAR: lv_pattern.
CASE pv_command.
WHEN 'DELETE'.
CONCATENATE pv_command '\s+FROM\s+([^\s]+)\s*'
INTO lv_pattern.
WHEN 'INSERT'.
CONCATENATE pv_command '\s+INTO\s+([^\s]+)\s+'
INTO lv_pattern.
WHEN 'UPDATE'.
CONCATENATE pv_command '\s+([^\s]+)\s+'
INTO lv_pattern.
WHEN OTHERS.
lv_ddl = 'X'.
ENDCASE.
CHECK lv_pattern IS NOT INITIAL.
lv_sql = pv_sql.
TRANSLATE lv_sql TO UPPER CASE.
FIND REGEX lv_pattern IN pv_sql
IGNORING CASE
SUBMATCHES pv_table.
IF sy-subrc IS NOT INITIAL.
MESSAGE e177(tw) WITH 'TableName not found'.
ENDIF.
TRANSLATE pv_table TO UPPER CASE.
*--- Check DDL
IF lv_ddl IS NOT INITIAL.
PERFORM check_auth USING gc_ddl.
IF sy-subrc <> 0.
MESSAGE e744(mc) WITH pv_table.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_ENCLOSED_TABLE_NAME
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_LEVEL text
* -->P_FIELDNAME text
*----------------------------------------------------------------------*
FORM get_enclosed_table_name USING pv_level
pv_field
CHANGING pv_table.
DATA : lv_level LIKE gt_dictionary-level.
DATA : ls_dictionary LIKE gt_dictionary.
lv_level = pv_level + 1.
READ TABLE gt_dictionary WITH KEY level = lv_level
column = pv_field
INTO ls_dictionary
BINARY SEARCH.
IF sy-subrc IS INITIAL.
CASE ls_dictionary-table.
WHEN gc_enclose OR space.
PERFORM get_enclosed_table_name USING lv_level
pv_field
CHANGING pv_table.
WHEN OTHERS.
pv_table = ls_dictionary-table.
ENDCASE.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CONVERT_ASTERISK
*&---------------------------------------------------------------------*
FORM convert_asterisk .
DATA : lt_dictionary LIKE TABLE OF gt_dictionary.
DATA : lv_level LIKE gt_dictionary-level.
DATA : ls_dictionary LIKE gt_dictionary.
LOOP AT gt_dictionary.
CHECK gt_dictionary-field EQ '*'.
CLEAR : lv_level.
lv_level = gt_dictionary-level + 1.
READ TABLE gt_dictionary WITH KEY level = lv_level
TRANSPORTING NO FIELDS
BINARY SEARCH.
LOOP AT gt_dictionary FROM sy-tabix
INTO ls_dictionary.
IF ls_dictionary-level NE lv_level.
EXIT.
ENDIF.
IF gt_dictionary-table IS NOT INITIAL.
CHECK gt_dictionary-table EQ ls_dictionary-table.
ENDIF.
ls_dictionary-level = gt_dictionary-level.
ls_dictionary-field = ls_dictionary-column.
APPEND ls_dictionary TO lt_dictionary.
ENDLOOP.
ENDLOOP.
CHECK lt_dictionary[] IS NOT INITIAL.
APPEND LINES OF lt_dictionary TO gt_dictionary.
DELETE gt_dictionary WHERE field = '*'.
SORT gt_dictionary BY level column.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CHECK_DD03L
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM check_dd03l .
DATA : BEGIN OF lt_dd03l OCCURS 0,
tabname LIKE dd03l-tabname,
fieldname LIKE dd03l-fieldname,
END OF lt_dd03l.
CHECK gt_dictionary[] IS NOT INITIAL.
DELETE gt_dictionary WHERE level NE 1.
SELECT tabname fieldname
INTO TABLE lt_dd03l
FROM dd03l
FOR ALL ENTRIES IN gt_dictionary
WHERE tabname EQ gt_dictionary-table
AND fieldname EQ gt_dictionary-field.
SORT lt_dd03l BY tabname fieldname.
LOOP AT gt_dictionary.
READ TABLE lt_dd03l WITH KEY tabname = gt_dictionary-table
fieldname = gt_dictionary-field
BINARY SEARCH.
CHECK sy-subrc IS NOT INITIAL.
DELETE gt_dictionary.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_LINE_SELECTED
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM set_line_selected .
DATA : lv_line TYPE i.
DATA : lv_offset TYPE i.
DATA : lv_pos TYPE i.
CHECK gs_selected IS NOT INITIAL.
lv_line = gs_selected-line.
lv_offset = gs_selected-col.
IF gs_selected-obj IS NOT INITIAL.
lv_pos = lv_offset + strlen( gs_selected-obj ).
ELSE.
lv_pos = lv_offset + gs_selected-pos.
ENDIF.
CALL METHOD go_editor->set_selection_pos
EXPORTING
from_line = lv_line
from_pos = lv_offset
to_line = lv_line
to_pos = lv_pos.
EXIT.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form BEFORE_EXPLAIN_PLAN
*&---------------------------------------------------------------------*
FORM before_explain_plan USING con_name TYPE dbcon_name
CHANGING pv_plan_id.
DATA: lv_error TYPE string,
lo_sqlerr TYPE REF TO cx_sql_exception,
lo_stmt TYPE REF TO cl_sql_statement,
lo_ref TYPE REF TO data,
lo_result TYPE REF TO cl_sql_result_set,
lv_sql TYPE string,
lv_cnt TYPE i,
lv_pid TYPE c LENGTH 8.
* get the workprocess id
CALL FUNCTION 'TH_GET_OWN_WP_NO'
IMPORTING
wp_pid = lv_pid.
* concatenate plan_id from workprocess id and hostname
CONCATENATE sy-host lv_pid INTO pv_plan_id.
CLEAR: go_con_ref, gv_sql_code.
TRY.
go_con_ref = cl_db6_con=>get_connection( con_name ).
CONCATENATE `select count(*) as cnt `
` from sys.EXPLAIN_PLAN_TABLE `
` where statement_name = '` pv_plan_id `' `
INTO lv_sql. "#EC NOTEXT
lo_stmt = go_con_ref->create_statement( ).
lo_result = lo_stmt->execute_query( lv_sql ).
GET REFERENCE OF lv_cnt INTO lo_ref.
lo_result->set_param( lo_ref ).
lo_result->next( ).
lo_result->close( ).
IF lv_cnt <> 0.
CONCATENATE `delete from sys.EXPLAIN_PLAN_TABLE `
` where statement_name = '` pv_plan_id `' `
INTO lv_sql. "#EC NOTEXT
lo_result = lo_stmt->execute_query( lv_sql ).
ENDIF.
CATCH cx_sql_exception INTO lo_sqlerr.
IF lo_sqlerr->connection_closed <> 'X'.
TRY.
IF go_con_ref IS BOUND.
go_con_ref->close( ).
ENDIF.
CATCH cx_sql_exception INTO lo_sqlerr.
WRITE lo_sqlerr->sql_code
TO gv_sql_code LEFT-JUSTIFIED.
CONCATENATE gc_err_prefix gv_sql_code
lo_sqlerr->sql_message
INTO lv_error
SEPARATED BY space.
MESSAGE lv_error TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.
ENDIF.
MESSAGE i600(sada) WITH con_name.
ENDTRY.
ENDFORM. "BEFORE_EXPLAIN_PLAN
*&---------------------------------------------------------------------*
*& Form DB_EXPLAIN_PLAN
*&---------------------------------------------------------------------*
FORM db_explain_plan USING pv_sql
pv_plan_id
CHANGING pv_subrc.
DATA: lo_stmt TYPE REF TO cl_sql_statement,
lo_sqlerr TYPE REF TO cx_sql_exception,
lv_sql TYPE string,
lv_error TYPE string.
DATA: lv_pattern TYPE string.
DATA: lv_text TYPE string.
TRY.
CONCATENATE
` explain plan set statement_name = ` "#EC NOTEXT
` '` pv_plan_id `' `
` for `
INTO lv_sql.
CONCATENATE lv_sql
cl_abap_char_utilities=>cr_lf
pv_sql
INTO lv_sql.
lo_stmt = go_con_ref->create_statement( ).
lo_stmt->execute_ddl( lv_sql ).
go_con_ref->commit( ).
CATCH cx_sql_exception INTO lo_sqlerr.
IF lo_sqlerr->connection_closed <> 'X'.
TRY.
IF go_con_ref IS NOT INITIAL.
go_con_ref->close( ).
ENDIF.
CATCH cx_sql_exception INTO lo_sqlerr.
WRITE lo_sqlerr->sql_code
TO gv_sql_code LEFT-JUSTIFIED.
CONCATENATE gc_err_prefix
gv_sql_code
lo_sqlerr->sql_message
INTO lv_error
SEPARATED BY space.
MESSAGE lv_error TYPE 'S' DISPLAY LIKE 'E'.
pv_subrc = lo_sqlerr->internal_error.
ENDTRY.
ENDIF.
WRITE lo_sqlerr->sql_code
TO gv_sql_code LEFT-JUSTIFIED.
CONCATENATE gc_err_prefix
gv_sql_code
lo_sqlerr->sql_message
INTO lv_error
SEPARATED BY space.
*--- Find Error line
PERFORM find_error_offset USING lv_error.
*--- Line no. adjust.
IF gs_selected-line IS NOT INITIAL.
CONDENSE gs_selected-line.
lv_pattern = 'line [0-9]+'.
CONCATENATE 'line' gs_selected-line
INTO lv_text
SEPARATED BY space.
REPLACE REGEX lv_pattern IN lv_error WITH lv_text.
ENDIF.
MESSAGE lv_error TYPE 'S' DISPLAY LIKE 'E'.
pv_subrc = lo_sqlerr->internal_error.
ENDTRY.
ENDFORM. " db_explain_plan
*&---------------------------------------------------------------------*
*& Form check_auth
*&---------------------------------------------------------------------*
FORM check_auth USING pv_auth.
AUTHORITY-CHECK OBJECT 'ZSQL_AUTH'
ID 'QUERY' FIELD pv_auth.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form find_error_offset
*&---------------------------------------------------------------------*
FORM find_error_offset USING pv_error.
DATA: lv_pattern TYPE string.
FIND REGEX `invalid table name` IN pv_error.
IF sy-subrc IS INITIAL.
lv_pattern = `table/view ([^ ]+) in schema .+:`.
PERFORM find_regex_pattern USING lv_pattern
pv_error.
ENDIF.
IF gs_selected IS INITIAL.
FIND REGEX `invalid column name` IN pv_error.
IF sy-subrc IS INITIAL.
lv_pattern = `:( *[^ ]+ *):`.
PERFORM find_regex_pattern USING lv_pattern
pv_error.
ENDIF.
ENDIF.
IF gs_selected IS INITIAL.
FIND REGEX `sql syntax error` IN pv_error.
IF sy-subrc IS INITIAL.
lv_pattern = `syntax near "([^ ]+)":`.
PERFORM find_regex_pattern USING lv_pattern
pv_error.
ENDIF.
ENDIF.
IF gs_selected IS INITIAL.
PERFORM find_regex_pattern USING lv_pattern
pv_error.
ENDIF.
CHECK gs_selected IS NOT INITIAL.
gs_selected-line = gs_selected-line - 1.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form find_regex_pattern
*&---------------------------------------------------------------------*
FORM find_regex_pattern USING pv_pattern
pv_error.
DATA: lv_pattern TYPE string.
DATA: lv_offset TYPE string.
lv_offset = `line ([0-9]+) col ([0-9]+) \(at pos ([0-9]+)\)`.
IF pv_pattern IS NOT INITIAL.
CONCATENATE pv_pattern lv_offset
INTO lv_pattern
SEPARATED BY space.
FIND REGEX lv_pattern IN pv_error
SUBMATCHES gs_selected-obj
gs_selected-line
gs_selected-col
gs_selected-pos.
ELSE.
lv_pattern = lv_offset.
FIND REGEX lv_pattern IN pv_error
SUBMATCHES gs_selected-line
gs_selected-col
gs_selected-pos.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& SCREEN
*&---------------------------------------------------------------------*
100
GC_EDITOR ACtrl 1 1 255 255 200
GV_OK_CODE OK 0 0 20 20 1
PROCESS BEFORE OUTPUT.
MODULE status_0100.
MODULE init_editor.
PROCESS AFTER INPUT.
MODULE exit_0100 AT EXIT-COMMAND.
MODULE user_command_0100.
200
GC_CONTAINER ACtrl 1 1 255 255 200
GV_OK_CODE OK 0 0 20 20 1 OK
PROCESS BEFORE OUTPUT.
MODULE status_0200.
MODULE init_alv.
PROCESS AFTER INPUT.
MODULE exit_0100 AT EXIT-COMMAND.
MODULE user_command_0200.
*&---------------------------------------------------------------------*
*& GUI Status buttons
*&---------------------------------------------------------------------*
S100
EXEC
text: 실행
icon: ICON_EXECUTE_OBJECT
EXPL
text: 실행계획
icon: ICON_AVAILABILITY_CHECK
BACK EXIT CANC
S200
REFRESH
text: Refresh
icon: ICON_REFRESH
BACK EXIT CANC
*&---------------------------------------------------------------------*
*& Title bar
*&---------------------------------------------------------------------*
T100 SQL Editor for HANA
T200 ( &1 ) rows selected.
'SAP > ABAP' 카테고리의 다른 글
| Hana Error: cannot use duplicate index name (0) | 2022.08.03 |
|---|---|
| ENQUEUE_READ (0) | 2022.06.24 |
| 마스터 데이터 BAPI (0) | 2022.05.25 |
| Material Characteristics Basic 2 / MRP 3 (0) | 2016.03.09 |
| CTS Table length check Function. (0) | 2015.11.25 |