Run SQL queries in SAP
When implementing information solutions based on SAP ERP, as a rule, three systems are deployed:1. Development system.
2. Testing system.
3. The system of productive operation.
In the process of developing programs, it is often necessary to promptly test SQL queries in a productive or test system, since the development system usually contains minimal data and is not always enough. Let's look at the existing options for this, evaluate their shortcomings and ultimately develop our own tool.
I managed to count the 5 available options:
')
1. Transaction SE16 / SE16N
With this transaction, you can only select from one table. Not suitable for queries with multiple tables.
2. Transaction ST04 (Additional Functions -> SQL Command Editor)
This tool allows you to perform SQL queries of any complexity, but has 2 drawbacks:
- firstly, it perceives only Native SQL queries (DBMS syntax), which imposes some inconveniences, since when developing ABAP programs, Open SQL queries are used for universality, somewhat different in syntax, but this could have been survived if not Secondly";
- secondly, it works only if Oracle is used as a DBMS.
3. Transaction SQVI
You cannot write SQL queries directly in a transaction, but you can use the constructor to build fairly complex samples from several tables with JOINs. Does not know how to work with subqueries, and besides, the designer has to perform too many mouse manipulations, so it is not suitable for testing queries.
4. Write a simple program with the test query and transfer it to the test system
The process of transferring the modified code to the test (productive) system requires some routine manipulations and takes an average of 5-7 minutes, so this option is also not suitable, since there is not enough patience to do all this after each query edit.
5. Direct access to the DBMS
In most cases, it is not possible for developers to get such access on projects, so this option is not suitable.
Conclusion
It turns out that there is no convenient universal tool that would allow to quickly test SQL queries of any complexity in SAP. Having come to this conclusion, I decided to develop such a tool.
Getting to the development
First, in the transaction SE80, we create the ZSQL program, the MAIN100 GUI status with the Run button, and Screen 0100.
In large scale, the program's algorithm looks like this:
Getting SQL SELECT query
To obtain the SQL query, we will use a text editor, which we will create on the screen using the CL_GUI_TEXTEDIT class. To do this, add to the Screen 0100 an empty container named MYEDIT, into which we will display the editor.
Code snippet creating a text editor on the screen
data: g_editor type ref to cl_gui_textedit, g_editor_container type ref to cl_gui_custom_container. if g_editor is initial. create object g_editor_container exporting container_name = `MYEDIT` exceptions cntl_error = 1 cntl_system_error = 2 create_error = 3 lifetime_error = 4 lifetime_dynpro_dynpro_link = 5. create object g_editor exporting parent = g_editor_container wordwrap_mode = cl_gui_textedit=>wordwrap_at_fixed_position wordwrap_to_linebreak_mode = cl_gui_textedit=>true exceptions others = 1. if sy-subrc <> 0. leave program. endif. endif. SQL Parsing
From the entered SQL query, we need to get a list of selectable fields and tables in order to dynamically generate an ALV-Grid structure based on this list to produce the result.
Code snippet parsing the query
types: ty_simple_tab type standard table of ty_simple_struc. types: ty_t_code type standard table of rssource-line. data lt_sql_query type ty_simple_tab. lt_fields type ty_simple_tab, lt_tables type ty_simple_tab, l_use_cnt(1) type c. " " loop at lt_sql_query assigning <fs_sql_query>. " - replace all occurrences of con_tab in <fs_sql_query>-line with space. concatenate ` ` <fs_sql_query>-line ` ` into <fs_sql_query>-line. " " refresh lt_parsed_sql_line. split <fs_sql_query>-line at space into table lt_parsed_sql_line. delete lt_parsed_sql_line where line = ''. loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>. translate <fs_parsed_sql_line>-line to upper case. if <fs_parsed_sql_line>-line = 'SELECT'. continue. endif. " * - , " if <fs_parsed_sql_line>-line = '*'. l_field_names_obtained = 'X'. continue. endif. " FROM JOIN - , . " " if <fs_parsed_sql_line>-line = 'FROM' or <fs_parsed_sql_line>-line = 'JOIN'. l_field_names_obtained = 'X'. l_is_tabname = 'X'. continue. endif. " " if l_field_names_obtained is initial. " COUNT()" find 'COUNT(' in <fs_parsed_sql_line>-line ignoring case. if sy-subrc = 0. l_use_cnt = 'X'. continue. endif. " ~" search <fs_parsed_sql_line>-line for '~'. if sy-subrc = 0. add 1 to sy-fdpos. endif. append <fs_parsed_sql_line>-line+sy-fdpos to lt_fields. endif. " " if l_is_tabname = 'X'. append <fs_parsed_sql_line>-line to lt_tables. clear l_is_tabname. endif. endloop. endloop. Execute SQL query
To execute our query, we use the generate subroutine pool statement, which allows us to dynamically generate temporary ABAP programs based on the source code passed as a parameter, which we will prepare from the entered SQL query.
Code snippet generating an ABAP program
types: ty_t_code type standard table of rssource-line. data: code type ty_t_code, prog(8) type c, msg(120) type c, lt_parsed_sql_line type ty_simple_tab, l_sub_order(1) type c. field-symbols: <fs_sql_query> type ty_simple_struc, <fs_parsed_sql_line> type ty_simple_struc. append `program z_sql.` to code. append `form get_data using fs_data type standard table.` to code. append `try.` to code. loop at lt_sql_query assigning <fs_sql_query>. clear: lt_parsed_sql_line. split <fs_sql_query>-line at space into table lt_parsed_sql_line. delete lt_parsed_sql_line where line = ''. loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>. concatenate ` ` <fs_parsed_sql_line>-line ` ` into <fs_parsed_sql_line>-line. translate <fs_parsed_sql_line>-line to upper case. " into… 1 , " if <fs_parsed_sql_line>-line = ' FROM ' and l_sub_order is initial. append `into corresponding fields of table fs_data` to code. l_sub_order = 'X'. endif. append <fs_parsed_sql_line>-line to code. endloop. endloop. append `.` to code. append `rollback work.` to code. append `catch cx_root.` to code. append `rollback work.` to code. append `message ``- , `` type ``i``.` to code. append `endtry.` to code. append `endform.` to code. generate subroutine pool code name prog message msg. Displaying the result on the screen
Since the composition of the fields and their type are unknown to us in advance, to obtain the result and display it on the screen, we need to dynamically generate the internal table and the ALV-Grid structure based on the fields selected in the query. For this we will use the create_dynamic_table method of the cl_alv_table_create class.
Code snippet generating the ALV-Grid structure
data: ref_table_descr type ref to cl_abap_structdescr, lt_tab_struct type abap_compdescr_tab, ls_fieldcatalog type slis_fieldcat_alv. field-symbols: <fs_tab_struct> type abap_compdescr, <fs_tables> type ty_simple_struc, <fs_fields> type ty_simple_struc. loop at lt_tables assigning <fs_tables>. refresh lt_tab_struct. " " ref_table_descr ?= cl_abap_typedescr=>describe_by_name( <fs_tables>-line ). lt_tab_struct[] = ref_table_descr->components[]. loop at lt_tab_struct assigning <fs_tab_struct>. " SQL- - " if lines( lt_fields ) > 0. read table lt_fields transporting no fields with key line = <fs_tab_struct>-name. if sy-subrc <> 0. continue. endif. endif. " , " read table lt_fieldcatalog transporting no fields with key fieldname = <fs_tab_struct>-name. if sy-subrc = 0. continue. endif. clear ls_fieldcatalog. ls_fieldcatalog-fieldname = <fs_tab_struct>-name. ls_fieldcatalog-ref_tabname = <fs_tables>-line. append ls_fieldcatalog to lt_fieldcatalog. endloop. endloop. " COUNT() – CNT INT" if l_use_cnt = 'X'. clear ls_fieldcatalog. ls_fieldcatalog-fieldname = 'CNT'. ls_fieldcatalog-seltext_l = '-'. ls_fieldcatalog-seltext_m = '-'. ls_fieldcatalog-seltext_s = '-'. ls_fieldcatalog-datatype = 'INT4'. if p_tech_names = 'X'. ls_fieldcatalog-seltext_l = 'CNT'. ls_fieldcatalog-seltext_m = 'CNT'. ls_fieldcatalog-seltext_s = 'CNT'. ls_fieldcatalog-reptext_ddic = 'CNT'. endif. append ls_fieldcatalog to lt_fieldcatalog. endif. The dynamic code snippet
data: dyn_table type ref to data, dyn_line type ref to data, lt_lvc_fieldcatalog type lvc_t_fcat, ls_lvc_fieldcatalog type lvc_s_fcat. field-symbols: <fs_fieldcatalog> type slis_fieldcat_alv. " " loop at lt_fieldcatalog assigning <fs_fieldcatalog>. clear ls_lvc_fieldcatalog. move-corresponding <fs_fieldcatalog> to ls_lvc_fieldcatalog. ls_lvc_fieldcatalog-ref_table = <fs_fieldcatalog>-ref_tabname. append ls_lvc_fieldcatalog to lt_lvc_fieldcatalog. endloop. " " call method cl_alv_table_create=>create_dynamic_table exporting it_fieldcatalog = lt_lvc_fieldcatalog importing ep_table = dyn_table. assign dyn_table->* to <fs_data>. create data dyn_line like line of <fs_data>. assign dyn_line->* to <fs_wa_data>. Full listing of source code of the ZSQL program:
To uncover
type-pools: slis. types: begin of ty_simple_struc, line(255) type c, end of ty_simple_struc. types: ty_simple_tab type standard table of ty_simple_struc. types: ty_t_code type standard table of rssource-line. data: g_editor type ref to cl_gui_textedit, g_editor_container type ref to cl_gui_custom_container, g_ok_code like sy-ucomm, p_tech_names(1) type c. field-symbols: <fs_data> type standard table, <fs_wa_data> type any. call screen 100. module pbo output. set pf-status `MAIN100`. " SQL-" if g_editor is initial. create object g_editor_container exporting container_name = `MYEDIT` exceptions cntl_error = 1 cntl_system_error = 2 create_error = 3 lifetime_error = 4 lifetime_dynpro_dynpro_link = 5. create object g_editor exporting parent = g_editor_container wordwrap_mode = cl_gui_textedit=>wordwrap_at_fixed_position wordwrap_to_linebreak_mode = cl_gui_textedit=>true exceptions others = 1. if sy-subrc <> 0. leave program. endif. endif. endmodule. module pai input. case sy-ucomm. when `EXIT`. leave program. when `EXEC`. " «» perform exec. endcase. endmodule. form exec. " " data lt_sql_query type ty_simple_tab. clear lt_sql_query. call method g_editor->get_text_as_r3table importing table = lt_sql_query exceptions others = 1. delete lt_sql_query where line = ''. " " data: lt_fields type ty_simple_tab, lt_tables type ty_simple_tab, l_use_cnt(1) type c. clear: lt_fields, lt_tables, l_use_cnt. perform parse_sql_query using lt_sql_query changing lt_fields lt_tables l_use_cnt. " ABAP- SQL-" data: code type ty_t_code, prog(8) type c, msg(120) type c. clear: code, prog, msg. perform create_get_function using lt_sql_query changing code. generate subroutine pool code name prog message msg. if sy-subrc <> 0. message msg type 'I'. return. endif. " ALV-Grid " data: lt_fieldcatalog type slis_t_fieldcat_alv. refresh: lt_fieldcatalog. perform get_fieldcat using lt_tables lt_fields p_tech_names l_use_cnt changing lt_fieldcatalog. " , , <fs_data>, " " perform create_itab_dynamically using lt_fieldcatalog. " SQL-, " perform get_data in program (prog) using <fs_data>. " " perform show_alv using lt_fieldcatalog. endform. " " form parse_sql_query using lt_sql_query type ty_simple_tab changing lt_fields type ty_simple_tab lt_tables type ty_simple_tab l_use_cnt. data: l_field_names_obtained(1) type c, l_is_tabname(1) type c, lt_parsed_sql_line type ty_simple_tab. clear: l_field_names_obtained, l_is_tabname. field-symbols: <fs_sql_query> type ty_simple_struc, <fs_parsed_sql_line> type ty_simple_struc. constants: con_tab type c value cl_abap_char_utilities=>horizontal_tab. " " loop at lt_sql_query assigning <fs_sql_query>. " - replace all occurrences of con_tab in <fs_sql_query>-line with space. concatenate ` ` <fs_sql_query>-line ` ` into <fs_sql_query>-line. " " refresh lt_parsed_sql_line. split <fs_sql_query>-line at space into table lt_parsed_sql_line. delete lt_parsed_sql_line where line = ''. loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>. translate <fs_parsed_sql_line>-line to upper case. if <fs_parsed_sql_line>-line = 'SELECT'. continue. endif. " * - , " if <fs_parsed_sql_line>-line = '*'. l_field_names_obtained = 'X'. continue. endif. " FROM JOIN - , . " " if <fs_parsed_sql_line>-line = 'FROM' or <fs_parsed_sql_line>-line = 'JOIN'. l_field_names_obtained = 'X'. l_is_tabname = 'X'. continue. endif. " " if l_field_names_obtained is initial. " COUNT()" find 'COUNT(' in <fs_parsed_sql_line>-line ignoring case. if sy-subrc = 0. l_use_cnt = 'X'. continue. endif. " ~" search <fs_parsed_sql_line>-line for '~'. if sy-subrc = 0. add 1 to sy-fdpos. endif. append <fs_parsed_sql_line>-line+sy-fdpos to lt_fields. endif. " " if l_is_tabname = 'X'. append <fs_parsed_sql_line>-line to lt_tables. clear l_is_tabname. endif. endloop. endloop. endform. " ABAP- " form create_get_function using lt_sql_query type ty_simple_tab changing code type ty_t_code. data: lt_parsed_sql_line type ty_simple_tab, l_sub_order(1) type c. clear l_sub_order. field-symbols: <fs_sql_query> type ty_simple_struc, <fs_parsed_sql_line> type ty_simple_struc. append `program z_sql.` to code. append `form get_data using fs_data type standard table.` to code. append `try.` to code. loop at lt_sql_query assigning <fs_sql_query>. clear: lt_parsed_sql_line. split <fs_sql_query>-line at space into table lt_parsed_sql_line. delete lt_parsed_sql_line where line = ''. loop at lt_parsed_sql_line assigning <fs_parsed_sql_line>. concatenate ` ` <fs_parsed_sql_line>-line ` ` into <fs_parsed_sql_line>-line. translate <fs_parsed_sql_line>-line to upper case. " into… 1 , " if <fs_parsed_sql_line>-line = ' FROM ' and l_sub_order is initial. append `into corresponding fields of table fs_data` to code. l_sub_order = 'X'. endif. append <fs_parsed_sql_line>-line to code. endloop. endloop. append `.` to code. append `rollback work.` to code. append `catch cx_root.` to code. append `rollback work.` to code. append `message ``- , `` type ``i``.` to code. append `endtry.` to code. append `endform.` to code. endform. " ALV-" form get_fieldcat using lt_tables type ty_simple_tab lt_fields type ty_simple_tab p_tech_names l_use_cnt changing lt_fieldcatalog type slis_t_fieldcat_alv. data: ref_table_descr type ref to cl_abap_structdescr, lt_tab_struct type abap_compdescr_tab, ls_fieldcatalog type slis_fieldcat_alv. field-symbols: <fs_tab_struct> type abap_compdescr, <fs_tables> type ty_simple_struc, <fs_fields> type ty_simple_struc. loop at lt_tables assigning <fs_tables>. refresh lt_tab_struct. " " ref_table_descr ?= cl_abap_typedescr=>describe_by_name( <fs_tables>-line ). lt_tab_struct[] = ref_table_descr->components[]. loop at lt_tab_struct assigning <fs_tab_struct>. " SQL- - " if lines( lt_fields ) > 0. read table lt_fields transporting no fields with key line = <fs_tab_struct>-name. if sy-subrc <> 0. continue. endif. endif. " , " read table lt_fieldcatalog transporting no fields with key fieldname = <fs_tab_struct>-name. if sy-subrc = 0. continue. endif. clear ls_fieldcatalog. ls_fieldcatalog-fieldname = <fs_tab_struct>-name. ls_fieldcatalog-ref_tabname = <fs_tables>-line. append ls_fieldcatalog to lt_fieldcatalog. endloop. endloop. " COUNT() – CNT INT" if l_use_cnt = 'X'. clear ls_fieldcatalog. ls_fieldcatalog-fieldname = 'CNT'. ls_fieldcatalog-seltext_l = '-'. ls_fieldcatalog-seltext_m = '-'. ls_fieldcatalog-seltext_s = '-'. ls_fieldcatalog-datatype = 'INT4'. if p_tech_names = 'X'. ls_fieldcatalog-seltext_l = 'CNT'. ls_fieldcatalog-seltext_m = 'CNT'. ls_fieldcatalog-seltext_s = 'CNT'. ls_fieldcatalog-reptext_ddic = 'CNT'. endif. append ls_fieldcatalog to lt_fieldcatalog. endif. endform. " " form create_itab_dynamically using lt_fieldcatalog type slis_t_fieldcat_alv. data: dyn_table type ref to data, dyn_line type ref to data, lt_lvc_fieldcatalog type lvc_t_fcat, ls_lvc_fieldcatalog type lvc_s_fcat. field-symbols: <fs_fieldcatalog> type slis_fieldcat_alv. " " loop at lt_fieldcatalog assigning <fs_fieldcatalog>. clear ls_lvc_fieldcatalog. move-corresponding <fs_fieldcatalog> to ls_lvc_fieldcatalog. ls_lvc_fieldcatalog-ref_table = <fs_fieldcatalog>-ref_tabname. append ls_lvc_fieldcatalog to lt_lvc_fieldcatalog. endloop. " " call method cl_alv_table_create=>create_dynamic_table exporting it_fieldcatalog = lt_lvc_fieldcatalog importing ep_table = dyn_table. assign dyn_table->* to <fs_data>. create data dyn_line like line of <fs_data>. assign dyn_line->* to <fs_wa_data>. endform. " ALV-Grid " form show_alv using lt_fieldcatalog type slis_t_fieldcat_alv. data: ls_event type slis_alv_event, lt_event type slis_t_event, ls_layout type slis_layout_alv, l_repid like sy-repid. ls_layout-colwidth_optimize = 'X'. l_repid = sy-repid. call function 'REUSE_ALV_GRID_DISPLAY' exporting i_callback_program = l_repid is_layout = ls_layout it_fieldcat = lt_fieldcatalog i_save = 'X' tables t_outtab = <fs_data> exceptions program_error = 1 others = 2. if sy-subrc <> 0. leave program. endif. endform. The developed program allows you to perform Open SQL SELECT queries of any complexity. It is only necessary to observe one rule when writing a query: if the COUNT () construct is used, then “AS cnt” must be added after it in order for the ALV-Grid to be generated correctly.
The program, in theory, can be slightly refined and used not only for testing requests, but also for generating custom reports.
In the article, I did not address security issues. An incoming request is not checked for correctness in any way, after it you can write any ABAP code and it will be executed. To exclude this possibility, it suffices to add simple checks.
Full source code : https://github.com/RusZ/SAP-SQL-Executor
Source: https://habr.com/ru/post/251665/
All Articles